Macro is being driven on different worksheets

[Dot]

Hey There!

I have a msgbox that repeats in a given time.
But when I open other different worksheets, the code is run on them too.

How do I run only on the worksheet of the code?



Thanks.

[Dot]

Sorry,

Folows the Vba:

Module:

Sub shwMsg()
    MsgBox "hello msgbox", vbYesNo
    Application.OnTime Now + TimeValue("00:00:10"), "shwMsg"
End Sub

workbook:

Private Sub workbook_open()
    shwMsg
End Sub

[Aussiebear]

Try declaring the name the actual sheet on which you want the sub to run

[SamT]

ThisWorkbook

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "My Sheet" Then shwMsg
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "My Sheet" Then
'Cancel Application OnTime
end if
End Sub

[Dot]

I tried to insert this code, but nothing happened. Including the msgbox didn´t open.
I tried to also insert the code in the worksheet 1 and not in the workbook, but not the msgbox also didn´t open.




Thank You

[Paul_Hossler]

Try this version if there's only a single WS that you want the msgbox on

In the worksheet code module

Option Explicit
Private Sub Worksheet_Activate()
    ShowMessage
End Sub

Private Sub Worksheet_Deactivate()
    StopMessage
End Sub

In a Standard Module

Option Explicit

Public dtNextMessage As Date
 
Sub ShowMessage()
    Application.StatusBar = "Show Msgbox Started"
    
    MsgBox "hello msgbox", vbYesNo
    
    dtNextMessage = Now + TimeValue("00:00:05")
    Application.OnTime dtNextMessage, "ShowMessage", , True
    DoEvents
End Sub

Sub StopMessage()
    Application.StatusBar = "Show Msgbox Stopped"
    On Error Resume Next
    Application.OnTime dtNextMessage, "ShowMessage", , False
    On Error GoTo 0
    DoEvents
End Sub

[Dot]

This code still allows the execution in other worksheets.
Is possible run the macro only on the sheet1?

[Paul_Hossler]

I believe that it already does that

Capture.JPG

Did you put the event code on the code page of the worksheet where you want the popup?

[Dot]

This code solved the problem.
But, the msgbox appears in other files from other worksheets.

sheets.JPG

[Paul_Hossler]

Did you remove the Workbook_SheetActivate and Workbook_SheetDeactivate macros from ThisWorkbook?


[QUOTE][But, the msgbox appears in other files from other worksheets./QUOTE]

What do you mean by 'other files'?

The attached file seems to work for me

If you still have the issue, can you attached a small workbook that shows the problem?

[Dot]

For example.
With booth worksheets open at the same time, the code of the worksheet with the msgbox appears in second worksheet open or in any other worksheets open.
Try to open the two worksheets and run the msgbox. Look in another worksheet open, the code will appear too.
two worksheets.jpg

[Dot]

Guys,
Solved with the code:

"Sheet1":

Sub ShowMessage()
If ActiveWorkbook.Name <> ThisWorkbook.Name Then StopMessage: Exit Sub

"This Workbook":

Private Sub Workbook_Activate()
If ActiveSheet.Name = "Sheet1" Then ShowMessage
End Sub

Thanks