Selecting values in sheet list box using vba

[Raj]

Hi,

A worksheet has several list boxes (values in drop down lists). Is
there a way to select values in these list boxes through vba code?


Regards,
Raj

 

[mikerickson]

Yes,
if the ListBox or ComboBox is from the Forms Menu

ActiveSheet.Shapes("controlName").ControlFormat.Value = desiredIndex

if an ActiveX control (untested)

ActiveSheet.Shapes("controlName").ControlFormat.Value = desiredValue

If its a Validation list, you can just put the value into the cell.

Note that the .ControlFormat.Value for a Forms control is the index of the list item, while in an ActiveX control, the .Value is the value of the list item

If the list is Able, Baker, Charlie and Baker is chosen,
if the control is a Forms menu control .ControlFormat.Value = 2
if the control is an ActiveX, .ControlFormat.Value = "Baker"

(also the .List of a Forms menu control is 1 based, ActiveX controls have 0 based .Lists)

 

[jamesdavey]

if they are in a drop down (as in data -> validation -> list) then in
code you just set the cell value...

Range("A1").Value = "x" - as long as "x" is a value in your list...

 

[Raj]

if they are in a drop down (as in data -> validation -> list) then in
code you just set the cell value...

Range("A1").Value = "x" - as long as "x" is a value in your list...

Hi,

The values are not in cells (using data validations). They are in list
box controls placed in the sheet.

Thanks and Regards,
Raj