Maui_Jim
09-24-2008, 01:40 PM
I have a very simple macro attached to a Worksheet Change event that allows the user to click on a cell and report if it?s value is equal to one. If the result is true, the Message Box displays the ActiveCell Sheet Name.
Sub CellSheet()
Dim sh As Worksheet
Dim CurCell As String
Dim CurSheet As String
CurCell = ActiveCell.Address
CurSheet = ActiveSheet.Name
If Range(CurCell).Value = 1 Then
MsgBox (CurSheet)
Else
MsgBox "Value is not 1"
End If
End Sub
I have been unsuccessful in my attempts to modify this code to search the same Cell (ActiveCell clicked by User) in all of the WorkSheets in the WorkBook. If the value of the cell is equal to 1, I would like the MessageBox to display all the Sheet Names where the value of 1 is found.
The solution has been illusive to a VBA amateur such as myself; I would appreciate any advice that could point me down the correct path.
Thanks for your time,
Jim
Sub CellSheet()
Dim sh As Worksheet
Dim CurCell As String
Dim CurSheet As String
CurCell = ActiveCell.Address
CurSheet = ActiveSheet.Name
If Range(CurCell).Value = 1 Then
MsgBox (CurSheet)
Else
MsgBox "Value is not 1"
End If
End Sub
I have been unsuccessful in my attempts to modify this code to search the same Cell (ActiveCell clicked by User) in all of the WorkSheets in the WorkBook. If the value of the cell is equal to 1, I would like the MessageBox to display all the Sheet Names where the value of 1 is found.
The solution has been illusive to a VBA amateur such as myself; I would appreciate any advice that could point me down the correct path.
Thanks for your time,
Jim