How to run Macro on close??

[MaximumGravity]

I have created a simple macro to sort my worksheet. Now I want this to run wither at the close of the workbook, or the opening of it (I don't care which). Also, is there an automated way to run this macro (i.e. time interval, or at the end of a new line, etc)

Thanks for the help.

Russell

 

[Mark O'Brien]

To answer the first part:

1. In the VBE (Open Excel ALT+F11), double click on "ThisWorkbook".
2. In the left drop down, choose Workbook.
3. Now put the name of your macro after the line:
<pre>
Private Sub Workbook_Open()</pre>

This will now run your macro whenever you open Excel.

OR:

To run the macro on the Close Event:

Do 1 and 2 from above:

3. On the right dropdown menu choose "BeforeClose"
4. Put your macro name after the line:
<pre>
Private Sub Workbook_BeforeClose(Cancel As Boolean)</pre>

HTH

 

[Phil Riley]

To answer the second part, you can make the macro run when the selected cell is changed, in a very similar way to the reply above. Again in the VBE, click on Sheet1 (or whichever sheet it is in) and from the drop down select "Worksheet". One of the events in the right hand drop down is "SelectionChange". If you put your code in there it will run every time you select a different cell. This is better than "Change" for what you want because "Change" will run every time you press a key

 

[Al Chara]

On 2002-04-18 07:56, Phil Riley wrote:
One of the events in the right hand drop down is "SelectionChange". If you put your code in there it will run every time you select a different cell. This is better than "Change" for what you want because "Change" will run every time you press a key

This is not entirely true. The CHANGE event is triggered everytime a cell is changed.

So if you are editting a cell, you can press as many keys as you want, but the event will not be triggered unless the cell's value actually changes.

You can enter twenty different keys into a cell, but if you hit ESC then the current operation is cancelled and the CHANGE event will not be triggered.

 

[MaximumGravity]

Thanks guys, that is what I was looking for.

Russell