How to run Macro on close??

MaximumGravity

New Member
Joined
Apr 16, 2002
Messages
29
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top