PDA

View Full Version : [SOLVED] Macro is being driven on different worksheets



Dot
03-02-2017, 04:56 PM
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
03-02-2017, 05:06 PM
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
03-02-2017, 07:38 PM
Try declaring the name the actual sheet on which you want the sub to run

SamT
03-02-2017, 09:55 PM
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
03-03-2017, 08:06 AM
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
03-03-2017, 09:20 AM
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
03-03-2017, 07:11 PM
This code still allows the execution in other worksheets.
Is possible run the macro only on the sheet1?

Paul_Hossler
03-03-2017, 08:19 PM
I believe that it already does that

18537

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

Dot
03-04-2017, 06:50 AM
This code solved the problem.
But, the msgbox appears in other files from other worksheets.

18544

Paul_Hossler
03-04-2017, 06:59 AM
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
03-04-2017, 01:30 PM
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.
18547

Dot
03-07-2017, 04:00 PM
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