CODEWITHSUNDEEP
vbaexcel
gus zogolovitch
gus zogolovitch

Reputation: 1

stopping an excel VBA macro from closing

I've written some very basic VBA code to do a simple task: remind me to fill in my timesheet (which is an Excel s/sheet). it's worked for me for many years while I went to the office, turned on the computer and had Excel in my 'startup' folder. However, now my life has changed a bit and I use my laptop and don't tend to shut it down and therefore the macro doesn't get fired up.

Here's how it opens:

Private Sub Workbook_Open()
    Application.OnTime TimeValue("17:00:00"), "box"
    If TimeValue("17:00:00") < Time Then
       Run ("box")
    End If
    ActiveWindow.WindowState = xlMinimized
    Run ("morning")
End Sub

here's 'box':

Sub box()

    response = MsgBox("Have you filled in your timesheet?", vbYesNo, "timesheet")

    If response = vbYes Then
        ActiveWindow.WindowState = xlMinimized
    End If
    If response = vbNo Then
        Workbooks("timesheet2015 - GZ.xlsm").Activate
        ActiveWindow.WindowState = xlMaximized
    End If
End Sub

And here's 'morning'

Sub morning()
    response = MsgBox("Did you fill in your timesheet yesterday?", vbYesNo, "timesheet")

    If response = vbYes Then End
    If response = vbNo Then
        Workbooks("timesheet2015 - GZ.xlsm").Activate
        ActiveWindow.WindowState = xlMaximized
    End If
End Sub

I've been scratching my head working out if there's a way to stop the macro from ending once it's run once. I don't want to have some huge endless loop or it will slow the laptop down. I'm sure there's a simple way to do it so would love your help.

thanks!

Upvotes: 0

Views: 1122

Answers (1)

David Zemens
David Zemens

Reputation: 53663

Put the timesheet Workbook in your Excel's XLStart folder. This will open the workbook every time you open Excel. The use the workbook's _Open and _Close event handlers to prompt you to fill out the timesheet?

Or, put these procedures in another workbook or Add-in which loads with Excel (probably also in the XLStart or AddIns folder) that way you don't have to keep this file open perpetually. You can still use OnTime with the latter method.

Upvotes: 0

Related Questions