Show User Form -VBA

kilyg

New Member
Joined
Jan 11, 2006
Messages
36
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Tyler_Dirton

Board Regular
Joined
Oct 24, 2006
Messages
50
In your worksheet if you include

Sub AutoOpen()
UserForm3.Show

Where 'UserForm3' is the name of your form.
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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.

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

New Member
Joined
Jan 11, 2006
Messages
36
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

Active Member
Joined
Jun 27, 2006
Messages
345

ADVERTISEMENT

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

New Member
Joined
Jan 11, 2006
Messages
36
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

Board Regular
Joined
May 25, 2006
Messages
135
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,059
Messages
5,545,759
Members
410,704
Latest member
Cobber2008
Top