PDA

View Full Version : Solved: Userform Pop-up when opening excel



Skopweb
08-27-2009, 06:46 AM
Hello
Can u help to create a script that will popup a user form asking to select an option from the list. the option selected should then reflect in one of the cell in sheet2 and the form should close automatically..
Regards
Skopweb

Bob Phillips
08-27-2009, 07:21 AM
Show the form on workbook enter and get the value. Make sure that form gets hidden on close not unloaded.



Private Sub Workbook_Open()

With Userform1

.Show
Worksheets("Sheet1").Range("A2").Value = .cmbList.Value
End With
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

Skopweb
08-28-2009, 05:51 AM
Thanks XLD
i tried using for the combobox and it works
--------------------------------
Private Sub Workbook_Open()

With UserForm1

.Show
Worksheets("Sheet1").Range("A2").Value = .ComboBox1.Value
End With
End Sub

--------------------------------
however can u tell me how can i enter the list of values to be selected from the combobox and once its selected the the form should close.
the combo box should not take any other values than the one in the list


Regards
Skopweb

Bob Phillips
08-28-2009, 07:49 AM
Where are those values, in the code or a worksheet?

Skopweb
08-30-2009, 07:59 AM
hello xld
i just have 4 options to select but i have no preference if it should be in the code or in the list....
can u let me know both of it so i can select the appropirate one...
regards
skopweb

mdmackillop
08-30-2009, 09:02 AM
With only 4 items, and assuming they don't change I would put then in the code


Private Sub UserForm_Initialize()
ComboBox1.List = Array("Item1", "Item2", "Item3", "Item4")
End Sub

Skopweb
08-30-2009, 11:46 PM
thanks it works.....
but once the item is selected.. can the form close automatically

mdmackillop
08-31-2009, 12:43 AM
Yes.
Just add Unload code to the Click event of the Combobox.

Skopweb
08-31-2009, 01:59 AM
thanks this works the way i wanted..
regards
skopweb