PDA

View Full Version : Solved: Display Sheet Name If Cell Criteria Is Met



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

Bob Phillips
09-24-2008, 02:16 PM
Where's the worksheet_change event code that goes with it?

Bob Phillips
09-24-2008, 02:21 PM
Oops don't need it, I can work that code.



Sub CellSheet()
Dim sh As Worksheet
Dim CurCell As String
Dim msg As String

CurCell = ActiveCell.Address
For Each sh In ActiveWorkbook.Worksheets

If sh.Range(CurCell).Value = 1 Then
msg = msg & vbTab & sh.Name & vbNewLine
End If
Next sh
If Not msg = "" Then

MsgBox "Value found on these sheets:" & vbNewLine & vbNewLine & msg
End If
End Sub

Maui_Jim
09-24-2008, 04:52 PM
Bob,

As I have witnessed in so many of your posted solutions; concise, cerebral, and caring. Your resolution works perfectly. Moreover, I appreciate your time and willingness to share your knowledge with me.

Regards,
Jim