Getting VB List Box Selections to show in an excel spreadsheet cel

[MBA Needs Help with Excel VBE]

Hello,

I am building a VBA code in Excel 2003 and am having trouble with what I
thought was a relatively basic task. I have created a list box with values
that can be selected. I am trying to program the code so that when a user
selects an item from the drop down item, the value appears in a spreadsheet
cell. The list box is called "ClientList." It is located in a spreadsheet
called "Control Page" I am trying to get the selected value from the List
Box to appear in cell A2 of another worksheet in the book called
"Performance." I have tried all of the following codes, but when I run the
macro, cell A2 does not show anything. Please help!!

Worksheets("Performance").Range("A2").Value = ClientList.Value
Worksheets("Performance").Range("A2").Value = ClientList.Text
ws.Cells(2, 1).Value = Me.ClientList.Value

None of these work. The only thing that happens is with the third.
However, here, the only response is a number corresponding to the list item
that appears in the same sheet at the List Box (i.e. Control Page)

 

[NickHK]

Use a combo box from the Control Toolbox rather than from Forms. That way
you get an ActiveX control instead.
Then set the LinkedCell property of the combo box from the properties dialog
(right-click the control and select "Properties") to "Performance!A2",
without the quotes.
Set the ListFillRange to the values to fill the combo box.

NickHK

"MBA Needs Help with Excel VBE" <MBA Needs Help with Excel
(e-mail address removed)> wrote in message
news:[email protected]...

 

[MBA Needs Help with Excel VBE]

Nick,
Thank you for your speedy message. I can tell right off that the control
toolbox is much better. I have one follow up question. In my original code,
I had it set up so that they pressed a "Get Report" button and that opened
the form and allowed you to select an item from the list and then to click on
a button (with a macro behind it) that produced the report. Is there a way
to set up something similar to this but with using ActiveX

 

[NickHK]

These ActiveX controls have events; different controls, different events,
although there is some commonality amongst them.
The easiest way to start is to right-click the control and select "View
Code". You will see you have a routine stub something like:

Private Sub CommandButton1_Click()

End Sub

and above the code pane, you will see that at the top left, the combo box
shows "CommandButton1" and the top-right shows "Click".
Select the various items in the top-right combo box to see the various
events that the control in the left combo box exposes. You will get the stub
created for you for each event you select.

NickHK

"MBA Needs Help with Excel VBE"