Show User Form -VBA

[kilyg]

Hello!,

How do I make a User Form show up when the user opens up a spreadsheet? At present when I click the Run button above the user form within VBA the Form opens up in the Excel spreadsheet. I want the form to open up in the Excel spreadsheet automatically on opening, however.

Thanks,
kilyg

 

[Tyler_Dirton]

In your worksheet if you include

Sub AutoOpen()
UserForm3.Show

Where 'UserForm3' is the name of your form.

 

[patrickmuldoon99]

In Excel, press Alt + F11

This brings you into the VBA Window

Goto View --> Project Explorer. A pane will appear on the left. Locate where it says ThisWorkbook, and double click.

In the resulting window on the right, paste the following code, substituting userform1 for the name of your form.

Private Sub Workbook_Open()
   userform1.show
End Sub

Code in the Workbook_Open event is executed when the workbook is opened. This will make your userform appear when the book is opened.

Hope this helps

Patrick

 

[kilyg]

thats brilliant thanks very much!

One more question, how do I set up the page so that when the user has finished filling in this form, and clicks x on the top right of the user form, he is not returned to VBA editor but rather can see the information he has inputted on the excel screen

thanks,
kilyg

 

[patrickmuldoon99]

He shouldn't be redirected to the VBA editor, if the form is launched from Excels workbook open procedure, when the userform is closed, focus should go to whatever worksheet is active (I'm guessing Sheet 1")

You would be redirected to teh VBA editor if you are designing the form and then click the 'play' button.

Is that what you're doing?

 

[kilyg]

Yes Patrick I was doing what you said you was I doing. Thanks for all your help on the previous questions, I have another issue. I am designing this User Form for a variety of users. At the moment the last users entries are left on the spreadsheet, and re-opening the form and entering data on the user forms appends to the previous data on the spreadsheet. How do I make it so that the Excel spreadsheet is empty each time for the user?

Thanks for all the help,
kilyg

 

[KThatcher]

Hi all, just tagging onto the bottom of this thread since the simple explanation of how to do what I am trying to do is here.

WHY is this not working for me??

Private Sub Workbook_Open()
Application.Calculation = xlManual

Form1.Show

End Sub

My form is called Form1 but I get an error "run time error '424' object required" when I open my sheet. wth? when I click debug it takes me to the form1.show command.

 

[Norie]

KThatcher

I think you really need to create a new thread.

The reason being is because either:

1 You've got the form name wrong.

2 There's some problem in the Initiliaze/Activate event of the form.

Oh, and this thread is almost 2 years old.