Reference last activeworkbook

**randell.graybill** · 01-02-2010, 10:25 PM

Hi all I have a userform that presents the user with 4 choices and once they click on one choice a macro runs that filters and formats the data and when the user clicks on the button start over it will reopen the file they were just using prior to the changes occurring. The reason for this is the same spreadsheet is printed up to 4 times with different information (remember the part where the data was filtered?). So basically the user clicks on option 1 the macro runs formating the page and then the user clicks start over (different userform then the first one) and the spreadsheet closes and reopens before the changes.

The way I have excel reopening the file now is by having the user input the file name into a text box and clicking OK this passes the file name to the open code in VBA. How can I tell the code the name of the sheet after the sheet is no longer active (because a userform was made active)? Basically the end goal is for the user to click over and excel will just reopen the file without any user input.

Any questions to clear this up please ask.

**Leith Ross** · 01-02-2010, 10:36 PM

Hello Randell,

Is this user form a VBA UserForm or simply a worksheet with some controls? Is the sheet always the ActiveSheet in the workbook?

**randell.graybill** · 01-06-2010, 12:36 AM

Sorry been away from the computer over the weekend. And yes the userform in questino is a VBA userform and during the duration of the macro it will be the only workbook referenced. But might not be the only one open.

**randell.graybill** · 01-07-2010, 10:04 PM

I was able to solve this by using using a variable to extract the file name and storing that data in a user form and then setting that as a new variable in the new module. Maybe there is a better way but this method at least works.

Basically the way I have it setup is one macro defines the name of the spreadsheet being used and opens a user form. And that user form allows the user to choose from a choice of 5 macros which one to invoke. After the macro completes the user is asked if they wish to Save, Close, or Start Over. When Start Over is clicked the macro retrieves the name of the workbook the macro was originally ran from and the user logged on in order to reopen the file that was just used without the changes so that the user name chose from another one of the 5 macros before closing or saving.