[mso] Re: Change Events in Excel

Geoff ,
   
  small correction in  the way cells defined!
   
  Private Sub Worksheet_Change(ByVal target As Range)
n = 27
If target.Address = Range("22:22"). Then
' If target.Address = "$G$n" Then 
Range("22:22").Select
Selection.EntireRow.Insert
Range("23:23").Select
Selection.Copy
Range("22:22").Select
Selection.PasteSpecial
Application.CutCopyMode = False
Range("$C$22").Select
n=n+1
End If
If target.Address = Range(Cells(7,n) ) Then 
Range(Cells(7,n+1)).Select
Selection.EntireRow.Insert
Range(Cells(7,n+2)).Select
Selection.Copy
Range(Cells(7,n+1)).Select
Selection.PasteSpecial
Application.CutCopyMode = False
Range("C26").Select
End If
End Sub



joby john <john_joby@xxxxxxxxx> wrote:
  Geoff,

Try something in the line of following,

Private Sub Worksheet_Change(ByVal target As Range)
n = 27
If target.Address = Range("22:22"). Then
' If target.Address = "$G$n" Then 
Range("22:22").Select
Selection.EntireRow.Insert
Range("23:23").Select
Selection.Copy
Range("22:22").Select
Selection.PasteSpecial
Application.CutCopyMode = False
Range("$C$22").Select
n=n+1
End If
If target.Address = Range(7,n) Then

Range(7,n+1).Select
Selection.EntireRow.Insert
Range(7,n+2).Select
Selection.Copy
Range(7,n+1).Select
Selection.PasteSpecial
Application.CutCopyMode = False
Range("C28").Select

End If
End Sub


Geoff Culbertson wrote:
John,

Thanks very much for your input, which is allowing me to make some progress
with this project.

I now have the following code as suggested by you

Private Sub Worksheet_Change(ByVal target As Range)
n = 21
If target.Address = "$G$21" Then
' If target.Address = "$G$n" Then 
Range("22:22").Select
Selection.EntireRow.Insert
Range("23:23").Select
Selection.Copy
Range("22:22").Select
Selection.PasteSpecial
Application.CutCopyMode = False
Range("$C$22").Select

End If

If target.Address = "$G$27" Then

Range("28:28").Select
Selection.EntireRow.Insert
Range("29:29").Select
Selection.Copy
Range("28:28").Select
Selection.PasteSpecial
Application.CutCopyMode = False
Range("C28").Select

End If
End Sub

If I input data into G21 and press Tab a new line is inserted properly
formatted, but as I have inserted a new line the target.Address for the
second section is no longer correct. If I haven't added a new line in after
G21 then the code works for G27.

I have tried putting a variable n=21 at the top and then changing all the
addresses to $G$n and ("n+1:n+1") etc. but this doesn't work ;(

Again, thanks for your help

Regards

Geoff Culbertson
Petersfield, Hampshire, UK


-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
Of joby john
Sent: 30 October 2007 12:46
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Change Events in Excel

Hi Geoff ,

If my understanding is right a simple solution will be to insert more " if
statement" in the code!
Identfy the target Cells( Cell which triger the Macro ) and add as many if
statements(If target.Address="$(Triger Cell)$............ end if)

something like following,


Private Sub Worksheet_Change(ByVal target As Range)
If target.Address = "$G$21" Then

Range("22:22").Select
Selection.EntireRow.Insert
Range("23:23").Select
Selection.Copy
Range("22:22").Select
Selection.PasteSpecial
Application.CutCopyMode = False
Range("C22").Select

End If

If target.Address = "$A$1" Then

Range("2:2").Select
Selection.EntireRow.Insert
Range("3:3").Select
Selection.Copy
Range("2:2").Select
Selection.PasteSpecial
Application.CutCopyMode = False
Range("C22").Select

End If
End Sub


Hope this will help you bit.




joby john wrote:
Hi,

What I understand is that u need to use same procedure morethan one area of
the worksheet ?



how many rows u need to creat in dif areas of worksheet?



Regards

John
Geoff Culbertson wrote:
Hi John
I have amended your code slightly to make it copy the cell formatting I
require, so it now looks like this. (I think the reason I had to change the
code was because each row has groups of merged cells.)

Private Sub Worksheet_Change(ByVal target As Range)

If target.Address = "$G$21" Then

Range("22:22").Select
Selection.EntireRow.Insert
Range("23:23").Select
Selection.Copy
Range("22:22").Select
Selection.PasteSpecial
Application.CutCopyMode = False
Range("C22").Select

End If
End Sub

I tried copying the code into a new Private Sub Worksheet_Change1(ByVal
target as Range), for a different area of the worksheet, but it wouldn't
work at all?! I thought the Worksheet_Change was just a name.

The code works fine for the first area I applied it to, but you can only add
one line. I tried adding some code along the lines of :-

N=0
For n=n+1


Next n

To allow the addition of any number of lines.

Hope you can understand what I mean and see a way of achieving the result
I'm after.

Many thanks again.

Regards

Geoff Culbertson
Petersfield, England, UK


-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
Of joby john
Sent: 28 October 2007 22:17
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Change Events in Excel

Geoff,

Please copy and paste following code and try
I have assumed that the title "Name "and "Position" is in A1 and B2
ur input are in A2 and B2


Private Sub Worksheet_Change(ByVal target As Range)


If target.Address = "$B$2" Then

Range("b3").Select
Selection.EntireRow.Insert
Range("A2:B2").Select
Selection.Copy
Range("a3").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=
_
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("b1").Select
End If
End Sub

Regards

John


Geoff Culbertson wrote:
John et al,

I have two columns with headers of Name & Position on a form and two rows
below this.
I believe it is possible to attach a change event macro to the first cell
below the Name header so that when an editor adds data in that cell it
automatically adds another row below the second row which is formatted in
the same way. It doesn't matter if there is always a blank row at the bottom
of this table.

I have seen similar examples where there is a button to add another row, but
think it is possible to automate this.

Does this make more sense?

Regards

Geoff Culbertson
Petersfield, UK

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
Of joby john
Sent: 27 October 2007 06:14
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Change Events in Excel

Geoff

Can I have more details on this please, may be detail example as to what u
need

John
Geoff Culbertson wrote:
I believe that it is possible for a macro to be started when data is
inserted in a cell in Excel.


I have a protected worksheet that when the user enters data into a cell, I
would like a macro to automatically copy and insert a row below the row that
data has been entered in. I think that a change events macro could do this,
but have not found any code which I can adapt to do this. Can anyone help
please?



e.g. C1 = Name and D1 = Position

C2 is blank and D2 is blank

C3 is blank and D3 is blank

When data is entered in C2, I would like row 3 to copied and pasted as row 4
and then if data is inserted in C3, row 4 is copied and pasted as row 5, and
so on.



-- 

Geoff Culbertson

Petersfield, UK





*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the
dropdown menu at the top. This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow you
to join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the
dropdown menu at the top. This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow you
to join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************


-- 
No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.15.11/1094 - Release Date: 26/10/2007
08:50


*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the
dropdown menu at the top. This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow you
to join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the
dropdown menu at the top. This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow you
to join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************


-- 
No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.15.11/1094 - Release Date: 26/10/2007
08:50

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the
dropdown menu at the top. This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow you
to join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the
dropdown menu at the top. This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow you
to join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the
dropdown menu at the top. This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow you
to join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************


-- 
No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.15.12/1098 - Release Date: 29/10/2007
09:28


*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top. This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
This group will not allow for posting of emails, but will allow you to join and 
share problem files, templates, etc.: 
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top. This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
This group will not allow for posting of emails, but will allow you to join and 
share problem files, templates, etc.: 
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************


 __________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
 This group will not allow for posting of emails, but will allow you to join 
and share problem files, templates, etc.:  
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

Other related posts: