Everyone assumed that you had a combo box in a worksheet not a combo box on a Form (big difference). Combo boxes in a worksheet have the linked cell procedure.
O.K. - let's start from scratch. To illustrate the procedure I will create some data. This is what I want to achieve:
1. Have a Combo Box and a Command button on a Form in Sheet 1 (I assume that since you already have a Combo Box on a Form, that you also know how to put a Command button on the same Form).
2. Populate the Combo Box from a list in a worksheet named "Data"
3. Get information from the Combo Box and put it in cell C5 on a worksheet named "Result".
Procedure:
1. Name a worksheet as "Data" and put the following list in A1:A6:<pre>
Tiger
Panther
Lion
Leopard
Puma
Cougar</pre>
2. Name the above list as "BigCats"
3. Name a worksheet as "Result"
4. On a Form (UserForm1), you will have a Combo Box (ComboBox1) and a Command button (CommandButton1). Single click CommandButton1, delete the words "CommandButton1" and enter the word "Cancel".
5. These are the macros that you will need:
Double click the Form, and put these macros in the module behind the Form:<pre>
Private Sub UserForm_Initialize()
With UserForm1.ComboBox1
.RowSource = Worksheets("Data").Range("BigCats").Address(external:=True)
'Set combo box to first entry
.ListIndex = 0
End With
End Sub
Private Sub ComboBox1_Change()
Worksheets("Result").Range("C5").Value = _
ComboBox1.Text
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub</pre>
In a standard module, put:<pre>
Sub MyForm()
UserForm1.Show
End Sub</pre>
6. Select Sheet1, go to the View menu, select Toolbars then Forms. Select the Button
icon and assign the button to the MyForm macro.
7. Click the button referred to in item 6, select an item from the Combo Box, hit the Cancel button on the Form, then look at cell C5 on the Results worksheet.
Notes:
Look at the first macro. You could populate the Combo Box by using the following macro:<pre>
Private Sub UserForm_Initialize()
With UserForm1.ComboBox1
.AddItem "Tiger"
.AddItem "Panther"
.AddItem "Lion"
.AddItem "Leopard"
.AddItem "Puma"
.AddItem "Cougar"
'Set combo box to first entry
.ListIndex = 0
End With
End Sub</pre>
Personally, I think that the first macro is the easier method.
HTH
Mike
This message was edited by Ekim on 2002-12-20 12:54