CODEWITHSUNDEEP
vbaexcel
Vincent
Vincent

Reputation: 41

Getting a value from ComboBox

I have created a combo-box via VBA code.

Sub CreateFormControl()

ActiveSheet.DropDowns.Add(0, 0, 100, 15).Name = "ComboBox1"
ActiveSheet.Shapes("ComboBox1").ControlFormat.RemoveAllItems

Dim i As Integer
With ActiveSheet.Shapes("ComboBox1").ControlFormat
    For i = 1 To 25
        .AddItem i
    Next i
End With
ActiveSheet.Shapes.Range(Array("ComboBox1")).Select
Selection.OnAction = "ComboBox1_Change"
Range("B2").Select

End Sub

The problem here is, when I select an item in the ComboBox, it gives me a

Run-time error 424. Object required

It doesn't show the value selected. I also tried to change my declaration, Sub CreateFormControl() to Public Sub CreateFormControl(), but it's still not working.

Sub ComboBox1_Change()

MsgBox (ComboBox1.Value) 'The error is here

End Sub

Upvotes: 1

Views: 10306

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

Try the code below, try to replace ActiveSheet with a qualifed Worksheet, like Worksheets("YoutSheetName").

Sub ComboBox1_Change()

Dim ws As Worksheet
Dim MyDropDown As DropDown

' try not to use ActiveSheet, replace "Sheet1" with your sheet's name
Set ws = Worksheets("Sheet1") ' ActiveSheet
Set MyDropDown = ws.Shapes("ComboBox1").OLEFormat.Object ' <-- set my Object with "ComboBo1" drop-down

MsgBox MyDropDown.List(MyDropDown.ListIndex) '<-- display the value of the selected item 

End Sub

Below is a "cleaner" way to add a new DropDown to a Worksheet without using ActiveSheet, Select and Selection (just use fully qualified objects).

Sub CreateFormControl Code

Option Explicit

Sub CreateFormControl()

Dim MyDropDown As DropDown
Dim i As Long

' set the drop-down object to the new created drop-down (replace "Sheet1" with your sheet's name)
Set MyDropDown = Worksheets("Sheet1").DropDowns.Add(0, 0, 100, 15)

' modify the drop-down properties
With MyDropDown
    .Name = "ComboBox1"

    .RemoveAllItems
    For i = 1 To 25
        .AddItem i
    Next i

    .OnAction = "ComboBox1_Change"
End With

End Sub

Upvotes: 2

Related Questions