In sheet3, I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Cells(1, 1)
If Target = Cells(47, 2) Then
MessageBoxMania
End If
End Sub
And in my main module I have:
Sub MessageBoxMania()
Dim Resp As Integer
Resp = MsgBox("message", 4, "Please confirm")
If Resp = 6 Then
'macro
Else
End If
End Sub
So if Cell(47,2)=1, it runs the macro perfectly, but ONLY if I am on sheet3. If I am on another sheet, and the value of Cell(47,2) changes to 1, the macro will not run. Eventually, I would like it to work like this:
While on some other sheet. Value on sheet3 changes. Save current position. Go to sheet3 and run macro. Return to previous position. Sounds complicated, but I almost have everything.
Thanks!
Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Cells(1, 1)
If Target = Cells(47, 2) Then
MessageBoxMania
End If
End Sub
And in my main module I have:
Sub MessageBoxMania()
Dim Resp As Integer
Resp = MsgBox("message", 4, "Please confirm")
If Resp = 6 Then
'macro
Else
End If
End Sub
So if Cell(47,2)=1, it runs the macro perfectly, but ONLY if I am on sheet3. If I am on another sheet, and the value of Cell(47,2) changes to 1, the macro will not run. Eventually, I would like it to work like this:
While on some other sheet. Value on sheet3 changes. Save current position. Go to sheet3 and run macro. Return to previous position. Sounds complicated, but I almost have everything.
Thanks!