Solved: Userform Pop-up when opening excel

[Skopweb]

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]

Show the form on workbook enter and get the value. Make sure that form gets hidden on close not unloaded.

[vba]

Private Sub Workbook_Open()

With Userform1

.Show
Worksheets("Sheet1").Range("A2").Value = .cmbList.Value
End With
End Sub
[/vba]

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]

Thanks XLD
i tried using for the combobox and it works
--------------------------------
[VBA]Private Sub Workbook_Open()

With UserForm1

.Show
Worksheets("Sheet1").Range("A2").Value = .ComboBox1.Value
End With
End Sub[/VBA]

--------------------------------
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]

Where are those values, in the code or a worksheet?

[Skopweb]

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]

With only 4 items, and assuming they don't change I would put then in the code

[VBA]

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

[/VBA]

[Skopweb]

thanks it works.....
but once the item is selected.. can the form close automatically

[mdmackillop]

Yes.
Just add Unload code to the Click event of the Combobox.

[Skopweb]

thanks this works the way i wanted..
regards
skopweb