How do I get the information from my combobox

G

Guest

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

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Supposing your combobox is combobox1, and the sheet you want the answer in is Sheet1, you could use:

worksheets("sheet1").range("a1").value = combobox1
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
My Combo button is on a form. I am not certain about the procedure linking the button to the cell. Please explain.
Thanks
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top