Run this Worksheet_Calculate when opening workbook without running my Macro

Question

Right now my process is working correctly, with the help of this community, however, I need this Worksheet_Calculate to NOT execute the Macro (MacroRuns for example) when the workbook opens, but I still need it to function the same way it is currently, after the workbook is opened.

Thank you so much for your help in advance!

The Code I Am Using:

in ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    TargetStart
End Sub

in the target sheet's code window

Option Explicit

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
    TargetCalc Me
Application.EnableEvents = True
End Sub

in Module 1

Option Explicit

Public TargetValue As Variant
Private Const cTarget As String = "C3"

Sub TargetCalc(ws as Worksheet)
    If ws.Range(cTarget) <> TargetValue Then
'this is where I would like the code to say something like, "if workbook just opened, exit -- otherwise continue. If this is even possible.
       Call MacroRuns
        TargetValue = ws.Range(cTarget).Value
    End If
End Sub

Sub TargetStart()
    TargetValue = Sheet1.Range(cTarget).Value
End Sub


Sub MacroRuns()

    Call UpdateMsgBox

End Sub

Possible duplicate of [VBA Worksheet change event bypass?](https://stackoverflow.com/questions/15761530/vba-worksheet-change-event-bypass). Even though it's a different event, it sounds like you possibly need the same solution. — BigBen, Jan 18 '19 at 18:48
Thanks @BigBen I've read that but it doesn't seem to pertain to my question of how to exit a worksheet_calculate after my macro has been run 1 time. If somehow it does answer my quiestion, I don't understand how to implement that into my code listed above. — soundship, Jan 18 '19 at 18:52
Within `Private Sub Worksheet_Calculate()`, add `Application.EnableEvents = False` before `TargetCalc Me`, and `Application.EnableEvents = True` after `TargetCalc.Me`. — BigBen, Jan 18 '19 at 18:54
Thank you so much man, that worked! The only other thing that's related to this is to somehow "NOT run the macro when I open the workbook, but run normally after the workbook is open." Is this a simple answer, or should I post a separate question. I have looked for this type of question on this site but haven't seen anything. Thanks for your help.. — soundship, Jan 18 '19 at 19:00

score 0 · Answer 1 · answered Jan 18 '19 at 19:15

0

I think that this can solve your problem:

In ThisWorkbook:

Private Sub Workbook_Open()
    Worksheets("NameHere").Range("A1") = True
End Sub

In the target sheet's code window:

Private Sub Worksheet_Calculate()
    If Worksheets("NameHere").Range("A1") Then MacroRuns
    Worksheets("NameHere").Range("A1") = False
End Sub

answered Jan 18 '19 at 19:15

Michel Excel

25
5

I tried to clarify my question since BigBen pointed out that my original question was a duplicate. He showed me how to keep it from looping, now I just need to know how to keep it from running my macro when I open the workbook. – soundship Jan 18 '19 at 19:23
I do not understand what you need. Using my code, MacroRuns will run only once when the worksheet is calculated. – Michel Excel Jan 18 '19 at 19:58

score 0 · Answer 2 · answered Jan 19 '19 at 09:52

Please try this arrangement.

Public StartUp As Boolean

Private Sub Workbook_Open()
    StartUp = True
    TargetStart
End Sub

Sub TargetCalc(ws As Worksheet)
    If ws.Range(cTarget) <> TargetValue Then
        If Not StartUp Then MacroRuns
        StartUp = False
        TargetValue = ws.Range(cTarget).Value
    End If
End Sub

or, perhaps you prefer it to be like this.

Sub TargetCalc(ws As Worksheet)
    If ws.Range(cTarget) <> TargetValue Then
        If Not StartUp Then
            MacroRuns
            TargetValue = ws.Range(cTarget).Value
        End If
        StartUp = False
    End If
End Sub

Run this Worksheet_Calculate when opening workbook without running my Macro

The Code I Am Using:

2 Answers2