Using Target.Row in other Sheets

  • Thread starter Thread starter zSplash
  • Start date Start date
Z

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
 
Why? There is probably an easier way to do what you want if we knew what it
is that you want.
 
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
 
Outstanding, Tom! Thanks so much.

st.

Tom Ogilvy said:
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
Click to expand...
 
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.
 
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.
 
Thank you, Tom.

st.

Tom Ogilvy said:
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
Click to expand...
 
BTW, Tom, you were right. I had "opted out of the error handler". That
fixed everything.

st.
 
Back
Top