Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$E$2" Then Exit Sub
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
sh.Range("G2") = "test worked"
Next
End Sub
'--------------------------------------------------------------------------------------------
Is there any way to call a sub to all worksheets. I've tried
replaceing the SH.RANGE line with CALL TEST and it did not apply it to
all sheets. I've tried using WITH statements as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$E$2" Then Exit Sub
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
with sh
call test
end with
Next
End Sub
This did not work either. Any suggestions?
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
with sh
call test(sh)
end with
Next sh
Sub Test(sh as worksheet)
msgbox sh.name
end sub
--
HTH...
Jim Thomlinson
Sub Test(sh As Worksheet)
MsgBox sh.Name
Range("G2") = "test worked"
End Sub
'__________________________
Sub Test(sh As Worksheet)
Range("G2") = "test worked"
End Sub
Yours:
Sub Test(sh As Worksheet)
Range("G2") = "test worked"
End Sub
Correct:
Sub test()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Range("G2") = "test worked"
Next
End Sub
--
dok112
------------------------------------------------------------------------
dok112's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10581
View this thread: http://www.excelforum.com/showthread.php?threadid=529351
Sub Test(sh As Worksheet)
sh.Range("G2") = "test worked"
End Sub
--
HTH...
Jim Thomlinson