listi.net

Auto_open

Auto_open is a Subroutine that will be executed automatically when the spreadsheet is opened.


Sub Auto_Open()
...

End Sub

This can be in any Module.

You can do the same in the ThisWorkBook Workbook_Open().

One thing I put in this routine is IsLatestFile function which checks if I'm working on the latest version of the code.

This is related to Versioning as I don't want to work on an old version of the code.

The next process is to set up any function keys to use. There are 2 ways to set these up.

Application.Onkey "key","sub_to_run"

Macro dialog > Options... > Shortcut key: setting

I prefer the Application.onkey because the Macro dialog setting is hard to find.

ex.
Application.OnKey "^+1","gototodo"
Application.OnKey "^+2","gotoUpcoming"

The ^ = CTRL key
The + = Shift key
The +^ together = CTRL Shift 1

The second parameter is the Sub to run


Sub gototodo()
sheets("Todo").Select
end sub

Another process I do is to open a specific webpage. I then check the page if a value has been set. If that value is set I have to update another system with the correct info or my name will appear on a DQ (Data Quality) Report.

You can also do any other code you want to run each time the spreadsheet is opened like:

Check the monitor size to see if automation will work

Check some event or query you're waiting for

Just be aware that you may need to implement some sort of "New Day" logic if you don't shut down the spreadsheet and you want to run some code each day, ex. like backing up the code.

To stop this code from running you use to be able to hold down the Shift key, but it doesn't always work so you might have to have code that looks for a file you create and if the file exists the code goes into debug mode via the Stop command.