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
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