Thread: how to get value of a combobox list in a worksheet from code in a module

hi there,

i have a combobox in a worksheet, and the associated list has 3 columns

I need the value of the third column (col index = 2) in a code I have in one of the modules.

following this thread (http://www.pcreview.co.uk/forums/thread-989754.php) I have tried the myworksheet.oleobjects("mycombobox").object.list(.... ) but it does not seem to work

any hints on how to do that? (which should be quite easy I guess... )

thanks!

Bernat

Hi,

If I understand you correctly you are wanting to see the 3rd item in the combobox when the form is opened.

Make sure that you have the ColumnCount property set to 3, then in the ColumnWidth property enter

0,0,20

if the last 20 is not wide enough just change it. You may also need to use semi colons instead of commas depending on your local settings.

HTH

Hi,

OK, how about

MsgBox UserForm1.ComboBox1.Column(2)

where the (2) is the third column since the index is zero based.

HTH

Hello ,

Since you did not say which toolbar control you are using, I am providing examples for both types. These examples assume that the code is in a standard VBA module, like Module1. These are only basic examples. There is no object validation or error handling in these macros..

Apparently you have used the ListFillRange property to fill these controls. The ListFillRange property property is available to the Control Toolbox ComboBox control only when it is on a worksheet. The properties for Control Toolbox controls may change depending on where they are placed: VBA UserForm or Worksheet.

Control Toolbox ComboBox Code
This will return the Column 3 value to the variable "X".

Sub GetComboBoxColumn3()

 'For Control Toolbox ComboBox Control
 
  Dim CB As OLEObject
  Dim Wks As Worksheet
  Dim X As Variant
  
    Set Wks = Worksheets("Sheet1")
    Set CB = Wks.OLEObjects("ComboBox1")
    
    X = CB.Object.List(CB.ListIndex, 2)
  
End Sub

Forms Toolbar DropDown Code
This will return the Column 3 value to the variable "X".

Sub GetDropDownColumn3()

 'For the Forms Drop Down Control
 'Assign this macro to the Drop Down
 
  Dim DD As Excel.DropDown
  Dim Rng As Range
  Dim Wks As Worksheet
  Dim X As Variant
  
    Set Wks = Worksheets("Sheet1")
    Set DD = Wks.DropDowns(Application.Caller)
    
    Set Rng = Evaluate(DD.ListFillRange)
    
    X = Rng.Cells(DD.ListIndex, 3)
    
End Sub

If you have any questions, please ask.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save