PDA

View Full Version : Open then Close Workbook with userforms



Endorphin
11-22-2010, 03:42 PM
Hi All,

I have 2 workbooks that I wish to switch between (there may be more than 2 in the future), both workbooks automatically open userforms upon start up.
This works great individually.
The problem i have is, when I try to go from one workbook to another via the userforms
I either get,
1. The new book opens and the original saves/closes but the userform in the new book does not start
or
2. The new book opens with its userform and the old one does not save/close until the userform of the new book is closed.

This depends on where I put the save section of the code.

Is there a way to do what im trying?

Thanks in advance.

Neil

Private Sub Workbook_Open()
With Application
'disable the ESC key
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call UnhideSheets

.ScreenUpdating = True
're-enable ESC key
.EnableCancelKey = xlInterrupt
End With

'maximize the size of the window under the application.
Application.WindowState = xlMaximized

'maximize the size of MainMenu Userform
With Diagnostic
.Width = Application.UsableWidth + 9
.Height = Application.UsableHeight * 1.2
End With
Load Diagnostic
Diagnostic.Show
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name = "MSSCC Logs 2.0.xls" Then
Workbooks("MSSCC Logs 2.0.xls").Close savechanges:=True
End If
Next
End Sub

Jan Karel Pieterse
11-22-2010, 11:48 PM
I expect if you swap some lines and make it look like this you should be OK:
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name = "MSSCC Logs 2.0.xls" Then
Workbooks("MSSCC Logs 2.0.xls").Close savechanges:=True
End If
Next
Load Diagnostic
Diagnostic.Show