Run a macro automatically but not in ThisWorkbook

Question

I have a code to set a timer of 5 minutes on my userform. It works when I press the command button.

How do I make it run automatically at the start ? I tried in ThisWorkbook but it didn't worked.

here's the code :

In a module:

Public Const AllowedTime As Double = 1

In the Userform:

Private Sub CommandButton1_Click()

Dim userClickedPause As Boolean ' Gets set to True by the Pause button

Dim stopTime As Date

    userClickedPause = False
    ' If AllowedTime is the number of minutes with a decimal part:
    stopTime = DateAdd("s", Int(AllowedTime * 600), Now) ' add seconds to current time

    ' If AllowedTime is the number of seconds:
    'stopTime = DateAdd("s", AllowedTime, Now) ' add seconds to current time
    Do
        With UserForm1.TextBox1
            .Value = Format(stopTime - Now, "Nn:Ss")
        End With
        DoEvents
        If userClickedPause = True Then
            Exit Do
        End If
    Loop Until Now >= stopTime


End Sub
Private Sub CommandButton2_Click()
    userClickedPause = True
End Sub

there's none. @braX I also wont need the pause function of this code. I just wanna see 10 minutes go by. — Chadi N
– Chadi N, Commented Dec 8, 2019 at 20:29
@braX I dont know what and what part of the code to place in ThisWorkbook to have it run automatically. For now, I have to click on a command button to run 10 minutes to 0. I would like to have it run once I open the workbook. This is what I dont know how. I pasted the code CommandButton1_Click() in ThisWorkbook but nothings happening. — Chadi N
– Chadi N, Commented Dec 8, 2019 at 20:32
Did you put it in the right event? or did you just paste it in randomly with no specific name? learn.microsoft.com/en-us/office/vba/api/excel.workbook.open — braX
– braX, Commented Dec 8, 2019 at 20:33

Michael Wycisk · Accepted Answer · 2019-12-08 21:07:38Z

1

I would recommend you to re-structure your code. Since you want to call your StartTimer sub from both a UserForm and the Workbook_Open event, put it into a module.

Then you simply call the sub from your Commandbutton1_Click event and the Workbook_Open event.

'Put this code inside ThisWorkbook
Private Sub Workbook_Open()
    'This sub into ThisWorkbook
    Application.Run "SetTimer"
End Sub

'This sub goes into a module
Private Sub SetTimer()
    'Here goes your code that sets the timer
    '(move it from your UserForm)
    '.......................................
    '.......................................
End Sub

'The code inside your UserForm
Private Sub CommandButton1_Click()
    Application.Run "SetTimer"
End Sub

answered Dec 8, 2019 at 21:07

Michael Wycisk

1,67815 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Michael Wycisk Over a year ago

@Codingnoob Check if "Events" are enabled. You can type ?Application.EnableEvents into the "Immediate Window" inside the VBE. If it returns FALSE, you can type Application.EnableEvents=True to enable events.

Michael Wycisk Over a year ago

Well, if you have your code inside ThisWorkbook in the Workbook_Open() Sub and Events enables, the only further thing that comes into my mind would be security settings that prevent the macro from running when the workbook opens. But I assume you checked those?

Chadi N Over a year ago

Have you tried it? It doenst work at all. Also you ommited to tell me where do I insert Public Const AllowedTime As Double = 1. I copied it inside another module, it still only works when I click the command button. It doesn't run automatically.

Michael Wycisk Over a year ago

The Public declaration can be in any module (but not in the UserForm, Sheet or ThisWorkbook). And no, I have not tried it, I showed you the general approach - which works if done right.

Michael Wycisk Over a year ago

Good to hear. Not sure if that can be fixed anyway. Probably the same problem like Excel blanking out while a heavy macro is running.

|

Collectives™ on Stack Overflow

Run a macro automatically but not in ThisWorkbook

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related