UserForm and Workbook_Open()

[Mr. Wizard]

I have created a user form that does everything I've told it to except pop up when the spreadsheet opens. The Workbook_Open seems to ignore the call to show the user form, and I am at a loss as to why.

What I would like to do is make all worksheets invisible, and only pop up the user interface (a user form). If I could make it so that it does not appear as an Excel spreadsheet, this would be good.

Can I get some suggestions?

 

[Nimrod]

Private Sub Workbook_Open()

Application.Visible = False

    Load UserForm1
    UserForm1.Show

Application.Visible = True

End Sub

 

[Norie]

Where have you placed the code for the Open event?

 

[Mr. Wizard]

I put the Workbook_Open in Module 5.

The macro that was listed in the previous reply works when I run the macro after Excel comes up, but doesn't start off automatically.

 

[Nimrod]

Mr. Wizard....

The Workbook_Open Event only works if it is placed in the "ThisWorkBook" module. :wink:


If you put a macro named Autpen() in a regular module, it will run whenever the workbook is manually opened (unless the user holds down the shift key while opening the file). If you put a macro named Workbook_Open() in the workbook code module that macro which will run upon opening the workbook. If both are present, both will run. The Workbook_Open macro will run first. You can access the workbook code module by displaying the Visual Basic project explorer, and double clicking on the ThisWorkbook object.

If code from another workbook opens a file containing Autpen() or Workbook_Open() macros, the Autpen macro() will not run, but the Workbook_Open() macro will run. To have the Autpen procedure run when the workbook is opened by Visual Basic code, the code opening the workbook can run the Autpen() procedure using this statement:

Explaination Source: Robert W. Flanagan: "Microsoft Excel Visual Basic Macros Made Easy "