[mso] Re: Change Events in Excel

Correct.  Range needs to be given a string containing a real range.

The string you are giving it is "n+1:n+1" which is not a real range.

You need to use String operations to give it one.  E.g., your

  Range("n+1:n+1").Select

should be

  Range((n + 1) & ":" & (n + 1)).Select

Better for a while, though, would be to create the string for a range in a 
local variable, so you can see what it looks like before giving it to Range.

So - a piece of test code

Private Sub CommandButton1_Click()
  Dim N As Integer: N = 27
  Dim RowRange As String: RowRange = (N + 1) & ":" & (N + 1)
  Range(RowRange).Select
End Sub

If you put a breakpoint on this, you can see what RowRange is set to.  In 
this case "28:28".  You then give that known good string to the Range 
function.


Regards, Dave S

----- Original Message ----- 
From: "Geoff Culbertson" <glio@xxxxxxxxxxxx>
To: <mso@xxxxxxxxxxxxx>
Sent: Friday, November 02, 2007 2:32 AM
Subject: [mso] Re: Change Events in Excel


> Thanks David,
>
> That worked well, but VB does not like Range("n+1:n+1").Select
>
> Regards
>
> Geoff
>
> -----Original Message-----
> From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
> Of David Smart
> Sent: 31 October 2007 09:09
> To: mso@xxxxxxxxxxxxx
> Subject: [mso] Re: Change Events in Excel
>
> Not "$G$n" which is a string containing the letter n.
>
> Use "$G$"&n
>
> Which appends the value of n to the end of the other bit.
>
> Regards, Dave S
>
> ----- Original Message ----- 
> From: "Geoff Culbertson" <glio@xxxxxxxxxxxx>
> To: <mso@xxxxxxxxxxxxx>
> Sent: Wednesday, October 31, 2007 9:57 AM
> Subject: [mso] Re: Change Events in Excel
>
>
>> 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 <john_joby@xxxxxxxxx> 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
>> *************************************************************
>
> *************************************************************
> 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.14/1100 - Release Date: 
> 30/10/2007
> 18:26
>
> *************************************************************
> 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
> ************************************************************* 

*************************************************************
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: