How do I get the information from my combobox

How do I get it to appear in a cell? Just supposing I want it go into cell reference a1?

[Audiojoe]

Supposing your combobox is combobox1, and the sheet you want the answer in is Sheet1, you could use:

worksheets("sheet1").range("a1").value = combobox1

[Yogi]

Where would this formula be written?

[voodoo]

Here one option. It will take whatever item you select in the combobox and place it in cell A1.

This assumes your combobox is placed on your worksheet and users the combo change event. Place this in the code module behind your sheet.

Private Sub comboBox1_Change()
Sheet1.Range("A1").Value = ComboBox1.Text
End Sub

[Ekim]

If your Combo box is from the Toolbox menu, put a cell reference in the "LinkedCell" field. If the Combo Box is from the Form menu, put a cell reference in the "Cell Link" field. No macro is necessary. If you are not clear about this, post back.

Regards,

Mike

[Yogi]

My Combo button is on a form. I am not certain about the procedure linking the button to the cell. Please explain.
Thanks

[Ekim]

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:

Tiger

Panther

Lion

Leopard

Puma

Cougar

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:

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

In a standard module, put:

Sub MyForm()

UserForm1.Show



End Sub

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:

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

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 ]

[Mudface]

Comboboxes on a userform do have a 'linked cell' property, the ControlSource property. Right-click the combobox in design mode, find the ControlSource entry and type in the cell reference where you'd like the value to appear. As long as you have another control on the form, tabbing out of the combobox will enter it's value in the cell.

[Ekim]

Mudface,

You are correct. When I wrote my reply last night (my time), I must have had my head up my .....

Using the data from my previous post, here's a simple answer to the OP's question.

1. Right click your combo box to bring up the Property window.
2. In RowSource field, put "BigCats" (without the apostrophes).
3. In the ControlSource field, put Sheet2!C5 (no "=" sign).

When are you going to get out of freezing cold, rainy, miserable Hull? Nice and warm in my hometown.

Regards,

Mike

[Mudface]

On 2002-12-20 22:10, Ekim wrote:
Mudface,

When are you going to get out of freezing cold, rainy, miserable Hull? Nice and warm in my hometown.

.