Restore Userform

[Clarissa]

Hi Everyone, Thanks in advance for any help! I have two issues,

1. I'm using a userform where the user will "view" a report in excel. When
the document is opened, the userform unloads. The user views the document
and then closes it. How can I then have the userform back on the screen
again?

2. Also, I have an Exit button which will exit the macro/workbook - exiting
the macro and closing the file. The error I get is: compile error - wrong
number of arguments or invalid property.

Here's that code:
Private Sub CommandButton3_Click()
Workbooks.Close savechanges:=False
End Sub

 

[Tom Ogilvy]

2. The close method for the Workbooks object takes no arguments

You could do

for each bk in workbooks
if bk.name <> thisworkbook.name then
bk.close SaveChanges:=False
end if
Next
Application.Quit

1. As for reshowing the userform, it is unclear what you mean by closing the
document. I the user closes the workbook that contains the form, then you
would have to reopen the workbook and issue the command to display the form.

 

[Clarissa]

Private Sub CommandButton26_Click()
Application.DisplayAlerts = False
Workbooks.Open ("W:\compensa\March 07.xls")
Windows("March 07.xls").Activate
Application.DisplayAlerts = True
Unload ViewReports
End Sub

When this button is clicked on the userform, the file will open. Once the
user is finished viewing the file, they'll close it.

Now, what's on screen, is the blank worksheet that contains the macros.
What I'd like to know is how to "re-load" the userform once the user closes
the file.

Thanks

 

[Tom Ogilvy]

You would need to detect the closing of the workbook and execute code to show
the form. You could instantiate Application Level events in the workbook
"with the code" and use that to detect the closing and reshow the userform.
Possibly using the window activate or deactivate event in conjunction with
the beforeclose event.

http://www.cpearson.com/excel/appevent.htm

 

[JLGWhiz]

It appears that your code to show the userform is in a Workbook_Open() event.
In the Visual Basic Editor>Project Window, double click on ThisWorkbook and
you should see the code that has either a UserForm1.Show or ViewReports.Show.
If so, then that is where the inital display of your UserForm originates.
To show the form again after it is unloaded in the CommandButton26_Click
event, you will need to either write as special macro to show it:

Sub ShwFrm()
ViewReports.Show
End Sub

Or put the ViewReports.Show command in another macro at the place where you
want it to show. Then you will need to include code to unload it again in
either case.