Show UserForm Opening Workbook

  • Hello,


    I have a userform that I created with VBA in Excel. How do I get the userform to load automatically when the workbook is opened? Also how do I make it the only visible item until the user makes a selection from the userform?


    Thank you.

  • Re: Shared Workbook Application


    Hi mileseve1


    Welcome to ozgrid.com


    Right click on the Excel icon, top left next to File and choose View Code. In here paste;

    Code
    Private Sub Workbook_Open()
        UserForm1.Show
    End Sub


    Quote

    Also how do I make it the only visible item until the user makes a selection from the userform?


    You could hide the Workbook and then use the Change Event of the Control they make their choice from to show it again. But I would honestly not go down this road if I were you. It is very likley to cause nothing but grief for both you and the user.


    Perhaps a Splash Screen?

  • Re: Show UserForm upon Opening Workbook


    mileseve1,


    As Dave has said - hiding the workbook would be a bad way to go.


    However there are plenty of options around using Splash Screens - where only certain sheets are hidden, not the entire application. Do a search on board for Splash Screens and see what comes up.


    Regards
    Weasel

  • Re: Show UserForm upon Opening Workbook


    Thank you.


    Is there any way to make the worksheets appear inactive when the userform is displayed? Also, I am looking for information on how to take data from several worksheets and have the data append a master worksheet. I have searched the website and have not found any postings.


    Thanks again

  • Re: Show UserForm upon Opening Workbook


    mileseve,


    Not sure what you mean by inactive. By default you are unable to use a worksheet while a userform is displayed - unless you are using a modeless userform.


    If you want your worksheets hidden while the userform is displayed (remembering that at least one must be displayed - this would typically be the splash screen) try using worksheet visible in the inialize/terminate events of the userform.


    eg. in the code section of the userform:


    Code
    Private Sub UserForm_Initialize()
    Sheets("sheet1").Visible = 0
    Sheets("sheet2").Visible = 0
    End Sub
    
    
    Private Sub UserForm_Terminate()
    Sheets("sheet1").Visible = 1
    Sheets("sheet2").Visible = 1
    End Sub


    Just be sure to use


    Code
    Unload userform1
    
    
    NOT
    
    
    userform1.hide


    to make the form go away.


    Regards
    Weasel

  • Re: Show UserForm upon Opening Workbook


    Thank you.


    Although, when I place this code in the userform code window, the code that starts the form when the application is opened now has a error. Is it possible to write a sub prodecure to handle these events?

  • Re: Show UserForm upon Opening Workbook


    Ok. Got it work however now its not showing any of my forms with either event and even when I remove code the forms are not showing!


    Help!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!