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.

"zSplash" <zNOSPAMSplash@ gci.net wrote in message
...
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

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","Sheet 5"))
sh.Activate
sh.Range(sAddr).Select
Next
me.activate

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

--
Regards,
Tom Ogilvy




"zSplash" <zNOSPAMSplash@ gci.net wrote in message
...
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

Outstanding, Tom! Thanks so much.

st.

"Tom Ogilvy" wrote in message
...

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","Sheet 5"))
sh.Activate
sh.Range(sAddr).Select
Next
me.activate

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

--
Regards,
Tom Ogilvy




"zSplash" <zNOSPAMSplash@ gci.net wrote in message
...
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

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.

"zSplash" <zNOSPAMSplash@ gci.net wrote in message
...
Outstanding, Tom! Thanks so much.

st.

"Tom Ogilvy" wrote in message
...

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","Sheet 5"))
sh.Activate
sh.Range(sAddr).Select
Next
me.activate

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

--
Regards,
Tom Ogilvy




"zSplash" <zNOSPAMSplash@ gci.net wrote in message
...
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

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","Sheet 5"))
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","Sheet 5"))
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



"zSplash" <zNOSPAMSplash@ gci.net wrote in message
...
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.

"zSplash" <zNOSPAMSplash@ gci.net wrote in message
...
Outstanding, Tom! Thanks so much.

st.

"Tom Ogilvy" wrote in message
...

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","Sheet 5"))
sh.Activate
sh.Range(sAddr).Select
Next
me.activate

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

--
Regards,
Tom Ogilvy




"zSplash" <zNOSPAMSplash@ gci.net wrote in message
...
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

Thank you, Tom.

st.

"Tom Ogilvy" wrote in message
...
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","Sheet 5"))
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","Sheet 5"))
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



"zSplash" <zNOSPAMSplash@ gci.net wrote in message
...
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.

"zSplash" <zNOSPAMSplash@ gci.net wrote in message
...
Outstanding, Tom! Thanks so much.

st.

"Tom Ogilvy" wrote in message
...

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","Sheet 5"))
sh.Activate
sh.Range(sAddr).Select
Next
me.activate

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

--
Regards,
Tom Ogilvy




"zSplash" <zNOSPAMSplash@ gci.net wrote in message
...
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

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

st.