How can I get the value of a Combo box on an Excel worksheet?

[karpftj]

I need to be able to use VB code to extract the value of combo boxes and
Listboxes that were added to a worksheet in Excel.

The values of those combo and list boxes were not linked to cells.

I need to extract data from several hundred workbooks, so I really need an
automated solution.

Any assistance would be greatly appreciated.


Thanks.

Tom Karpf

 

[Bob Phillips]

Combobox1.Value if it's a userform or worksheet control.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[karpftj]

That doesn't seem to work. Let me clarify what I'm doing.

1) I'm using Excel 2000.
2) Create a new worksheet.
3) Right-click on the tool bars and click Forms.
4) Click on the combo box icon on the new toolbar
5) Create the new combo box on the form. It's called 'Drop Down 1'.
6) Click on Tools, Macro, Visual Basic editor
7) Create a routine that looks something like this:

Sub getvalue()
Dim wb As Workbook
Dim ws As Worksheet
Dim ctrlComboBox as ????????????

Set wb = currentworkbook
Set ws = wb.Worksheets("Sheet1")

debug.print ws.combobox1.value 'doesn't work
End Sub

8) But I can't figure out what to do after the 'Set ws = ' line.

I know that I can find the existing combo box by iterating through the
SHAPES collection, but I can't get the value of the combobox.

Thanks.

Tom Karpf

 

[Dave Peterson]

Option Explicit
Sub testme01()
Dim myDD As DropDown
Set myDD = ActiveSheet.DropDowns("drop down 1")
If .ListIndex > 0 Then
MsgBox .List(.ListIndex)
End If
End Sub

And if you're doing something each time the users changes a dropdown:

Sub testme01B()
Dim myDD As DropDown
Set myDD = ActiveSheet.DropDowns(application.caller)
With myDD
If .ListIndex > 0 Then
MsgBox .List(.ListIndex)
End If
End With
End Sub

And assign that macro to each of the dropdowns.

With a listbox from the Forms toolbar (single value returned)

Option Explicit
Sub testme01()
Dim myLB As ListBox
Set myLB = ActiveSheet.ListBoxes("list box 2")
With myLB
MsgBox .List(.ListIndex)
End With
End Sub

If you can return lots from the listbox:
Option Explicit
Sub testme01()
Dim myLB As ListBox
Dim iCtr As Long
Set myLB = ActiveSheet.ListBoxes("list box 2")
With myLB
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
MsgBox .List(iCtr)
End If
Next iCtr
End With
End Sub

(and you can use Application.caller here, too and assign the macro to all the
listboxes.)

 

[Bob Phillips]

When you create a forms combobox, it becomes part of the shapes collection .
AFAIK there is no way to get a shape's value, there is certainly no Value
property. The only way I know of with forms comboboxes is to link it tow a
worksheet cell, and then get the value from there.

Either that or use ActiveX Control Toolbox controls, which is what I use.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[karpftj]

That doesn't work in Excel 2000. It's blowing up on Set MyDD = ...
Perhaps I'm missing a reference?

I currently have the following:
Visual Basic for Applications
Microsoft Excel 9.0 Object Library
OLE Automation
Microsoft Office 9.0 Object Library

Tom Karpf

 

[Dave Peterson]

I should have checked that listbox to see if anything was selected, too (single
selection returned version):

Option Explicit
Sub testme01()
Dim myLB As ListBox
Set myLB = ActiveSheet.ListBoxes("list box 2")
With myLB
If .ListIndex > 0 Then
MsgBox .List(.ListIndex)
End If
End With
End Sub

 

[Dave Peterson]

What's the name of the dropdown on the activesheet that you want to use?

If it's not on the activesheet or if it's not named "drop down 1", then you'd
get an error.

And if you're using the

set mydd = activesheet.dropdowns(application.caller)

Then you have to run it by clicking on the dropdown--not by debugging (F5/F8's
in the code window).

 

[karpftj]

Thanks a lot. I'm all set now.

Tom Karpf

What's the name of the dropdown on the activesheet that you want to use?

If it's not on the activesheet or if it's not named "drop down 1", then you'd
get an error.

And if you're using the

set mydd = activesheet.dropdowns(application.caller)

Then you have to run it by clicking on the dropdown--not by debugging (F5/F8's
in the code window).

 

[Juan Pablo González]

I need to be able to use VB code to extract the value of combo boxes
and Listboxes that were added to a worksheet in Excel.

The values of those combo and list boxes were not linked to cells.

I need to extract data from several hundred workbooks, so I really
need an automated solution.

Any assistance would be greatly appreciated.


Thanks.

Tom Karpf

Just in case, using the Shapes() collection:

Sub GetSelection()
Dim Sh As Shape
Set Sh = Sheets("Sheet1").Shapes("Drop Down 1")

'Get the ListIndex
Debug.Print Sh.ControlFormat.ListIndex

'The linked range
Debug.Print Sh.ControlFormat.ListFillRange

'The value
With Sh.ControlFormat
Debug.Print Range(.ListFillRange)(.ListIndex)
End With
End Sub