Macros that only run once

[Tim Payne]

Hi,

Is it possible to have a macro that only runs the first time a spreadsheet
is opened by the user and then never again (say to perform some cleanup and
formatting)?

Tim.

 

[Chip]

Yes, right click on any sheet in your file, click on View Code, then
click on "This Workbook"...in the drop down menu where it probably says
"General", you want to choose "Workbook"..then you can use the right
drop down menu to choose the On Action criteria, like "Open"...this
will then give you a macro which will run only when the file is opened

 

[Ron de Bruin]

Hi Tim

With One macro named "TheMacro" in Module1
The simplest way is to delete the entire module with contains the procedure.

Sub TheMacro()
'
' your code
'
With ThisWorkbook.VBProject.VBComponents
.Remove .Item("Module1")
End With
End Sub

But this must be checked to do this
http://www.j-walk.com/ss/excel/tips/tip96.htm

 

[Tim Payne]

That's exactly what I was after! Using this I can have my spreadsheet run
some one-time initial formatting and layout macros, and then remove the
macro so that it's not run on subsequent opens of the spreadsheet. Thanks
for the advice!



Tim.

 

[Ron de Bruin]

Hi tim

If you want to run the code automatic when you open the workbook
the first time than name the macro

Sub Auto_open()
'code or macro name
End Sub

Note : If you use a Auto_open macro then this wil not run if you open the file with a macro