Using Target.Row in other Sheets

[zSplash]

I am trying to select cells in several sheets, if the user is in Sheet1 and
selects a cell in column A. I get a '1004' error ("Select method of Range
class failed") at Range("A" & Target.Row).Select. Can somebody help me?
Here is my code:

.... If Target.Cells.Count = 1 Then
1: ' if they select a cell in Col 1, select complementary cell in other
sheets
If Target.Column = 1 And Target.Value <> "" Then
Sheets(3).Select
Range("A" & Target.Row).Select
Sheets(4).Range("A" & Target.Row).Sheets(5).Select
Sheets(1).Select
EndIf
EndIf
....

TIA

 

[Don Guillett]

Why? There is probably an easier way to do what you want if we knew what it
is that you want.

 

[Tom Ogilvy]

You can only select on the activesheet.

On Error goto ErrHandler
Dim sh as Worksheet
Dim sAddr as String
if Target.Count > 1 then exit sub
If Target.Column = 1 And Target.Value <> "" Then
sAddr = target.Address
Application.ScreenUpdating = False
Application.EnableEvents = False
for each sh in worksheets(Array("sheet2","Sheet3","Sheet4","Sheet5"))
sh.Activate
sh.Range(sAddr).Select
Next
me.activate

End if
ErrHandler
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[zSplash]

Outstanding, Tom! Thanks so much.

st.

You can only select on the activesheet.

On Error goto ErrHandler
Dim sh as Worksheet
Dim sAddr as String
if Target.Count > 1 then exit sub
If Target.Column = 1 And Target.Value <> "" Then
sAddr = target.Address
Application.ScreenUpdating = False
Application.EnableEvents = False
for each sh in worksheets(Array("sheet2","Sheet3","Sheet4","Sheet5"))
sh.Activate
sh.Range(sAddr).Select
Next
me.activate

End if
ErrHandler
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[zSplash]

So, Tom, your code works wonderfully. Once. After that, none of the
worksheet_change event codes work. If I close the workbook, and re-open it,
they again work. What do you think?

st.

 

[Tom Ogilvy]

Sounds like events have been disabled, however, I re-enable events in the
error handler. Note that I do not jump out of the sub before getting to the
error handler. If you added code such as

Exit Sub '<== added code? -- no no
ErrHandler
Application.ScreenUpdating =True
Application.EnableEvents = True
End sub

as one would normally do with an error handler, then this is incorrect. I
intentionally fall through the error handler on every execution of the code
so events are always enabled:

On Error goto ErrHandler
Dim sh as Worksheet
Dim sAddr as String
if Target.Count > 1 then exit sub
If Target.Column = 1 And Target.Value <> "" Then
sAddr = target.Address
Application.ScreenUpdating = False
Application.EnableEvents = False
for each sh in worksheets(Array("sheet2","Sheet3","Sheet4","Sheet5"))
sh.Activate
sh.Range(sAddr).Select
Next
me.activate

End if
ErrHandler
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


Other than that, I can't say why events are not being enabled. However,
since I would see this as sheet level code, I don't think you really need to
disable events, so you could comment out that line:

On Error goto ErrHandler
Dim sh as Worksheet
Dim sAddr as String
if Target.Count > 1 then exit sub
If Target.Column = 1 And Target.Value <> "" Then
sAddr = target.Address
Application.ScreenUpdating = False
'Application.EnableEvents = False ' <= comment out
for each sh in worksheets(Array("sheet2","Sheet3","Sheet4","Sheet5"))
sh.Activate
sh.Range(sAddr).Select
Next
me.activate

End if
ErrHandler
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


If you have duplicated this code on multiple sheets, then you need to
disable events or you will get into a recursive situation.

 

[zSplash]

Thank you, Tom.

st.

Sounds like events have been disabled, however, I re-enable events in the
error handler. Note that I do not jump out of the sub before getting to the
error handler. If you added code such as

Exit Sub '<== added code? -- no no
ErrHandler
Application.ScreenUpdating =True
Application.EnableEvents = True
End sub

as one would normally do with an error handler, then this is incorrect. I
intentionally fall through the error handler on every execution of the code
so events are always enabled:

On Error goto ErrHandler
Dim sh as Worksheet
Dim sAddr as String
if Target.Count > 1 then exit sub
If Target.Column = 1 And Target.Value <> "" Then
sAddr = target.Address
Application.ScreenUpdating = False
Application.EnableEvents = False
for each sh in worksheets(Array("sheet2","Sheet3","Sheet4","Sheet5"))
sh.Activate
sh.Range(sAddr).Select
Next
me.activate

End if
ErrHandler
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


Other than that, I can't say why events are not being enabled. However,
since I would see this as sheet level code, I don't think you really need to
disable events, so you could comment out that line:

On Error goto ErrHandler
Dim sh as Worksheet
Dim sAddr as String
if Target.Count > 1 then exit sub
If Target.Column = 1 And Target.Value <> "" Then
sAddr = target.Address
Application.ScreenUpdating = False
'Application.EnableEvents = False ' <= comment out
for each sh in worksheets(Array("sheet2","Sheet3","Sheet4","Sheet5"))
sh.Activate
sh.Range(sAddr).Select
Next
me.activate

End if
ErrHandler
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


If you have duplicated this code on multiple sheets, then you need to
disable events or you will get into a recursive situation.

--
Regards,
Tom Ogilvy



cell

 

[zSplash]

BTW, Tom, you were right. I had "opted out of the error handler". That
fixed everything.

st.