Consulting

Results 1 to 12 of 12

Thread: Macro is being driven on different worksheets

  1. #1

    Macro is being driven on different worksheets

    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.

  2. #2
    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

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,862
    Location
    Try declaring the name the actual sheet on which you want the sub to run
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,272
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    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
    Last edited by Dot; 03-03-2017 at 01:05 PM.

  6. #6
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,337
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

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

  8. #8
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,337
    Location
    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?
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

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

    sheets.JPG

  10. #10
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,337
    Location
    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?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    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
    Attached Images Attached Images

  12. #12
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •