How to run Macro on close??
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: How to run Macro on close??

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:

    Private Sub Workbook_Open()


    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:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)


    HTH

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks guys, that is what I was looking for.

    Russell

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com