CODEWITHSUNDEEP
excelvba
Chadi N
Chadi N

Reputation: 441

Run a macro automatically but not in ThisWorkbook

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

Upvotes: 0

Views: 179

Answers (1)

Michael Wycisk
Michael Wycisk

Reputation: 1695

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

Upvotes: 1

Related Questions