Open then Close Workbook with userforms

Endorphin

Board Regular
Joined
Oct 27, 2006
Messages
62
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

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

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,234
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top