how to add items to the comboBox using VBA

[ghost56]

I have created a comboBox1 on an excel sheet with the following statement

Set MyCombo = Shapes.AddFormControl(xlDropDown, Cells(10, 1).Left, Cells(10, 1).Top, 50, Cells(10, 1).RowHeight)

My issues are:
1.Now how to add items to the comboBox1.
2. How to create a comboBox 2 adjacent to comboBox1 when an item is clicked in comboBox1.

when i used .AddItem methosd it throws an error stating that method or property does not exist for the object. Can anyone help me to resolve these issues.

[Cimperiali]

Not sure this is a dot net question. Seems as if you're doing it inside excel.
In case, a way to do it (but user must enable macros) is the following

in a worksheet:

Private Sub Worksheet_Activate()
Dim shp As Shape
For Each shp In Me.Shapes
    If shp.Name = "cboDynamic" Then
        shp.Delete
        Set shp = Nothing
        Exit For
    End If
   Set shp = Nothing
Next
If shp Is Nothing Then
    CreateCbo
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Private Sub CreateCbo()
  
    With Me.Shapes.AddFormControl(xlDropDown, Left:=Cells(10, 1).Left, Top:=Cells(10, 1).Top, Width:=50, Height:=Cells(10, 1).RowHeight)
        .ControlFormat.DropDownLines = 3
        .ControlFormat.AddItem "First", 1
        .ControlFormat.AddItem "Second", 2
        .ControlFormat.AddItem "Third", 3
        .Name = "cboDynamic"
        .OnAction = "cboDynamic_Change"
      
    End With
  
End Sub

in a module:

Public Sub cboDynamic_Change()
  
Dim selectIndex As Integer
  
  
'delete any existing dropdown box named "cboDynSecond"
'if none exists, do not throw error
 On Error Resume Next
 ActiveSheet.DropDowns("cboDynSecond").Delete
 'disable on error resume next
On Error GoTo 0
  
selectIndex = ActiveSheet.DropDowns("cboDynamic").ListIndex
  Dim dd As DropDown
  Set dd = ActiveSheet.DropDowns("cboDynamic")
  
  Dim lft As Integer
  lft = dd.Left + dd.Width + 10
  Dim tp As Integer
  tp = dd.Top
  Dim wdt As Integer
  wdt = dd.Width
  Dim hgt As Integer
  hgt = dd.Height
  Set dd = Nothing
With ActiveSheet.Shapes.AddFormControl(xlDropDown, Left:=lft, Top:=tp, Width:=wdt, Height:=hgt)
    .ControlFormat.DropDownLines = 7
    .Name = "cboDynSecond"
   
    Select Case selectIndex
  
        Case 1
            .ControlFormat.AddItem "A_01", 1
            .ControlFormat.AddItem "A_02", 2
            .ControlFormat.AddItem "A_03", 3
            .ControlFormat.AddItem "A_04", 4
        Case 2
            .ControlFormat.AddItem "B_01", 1
            .ControlFormat.AddItem "B_02", 2
            .ControlFormat.AddItem "B_03", 3
            .ControlFormat.AddItem "B_04", 4
        Case 3
            .ControlFormat.AddItem "C1", 1
            .ControlFormat.AddItem "C2", 2
            .ControlFormat.AddItem "C3", 3
            .ControlFormat.AddItem "C4", 4

  
    End Select
  .OnAction = "cboDynSecond_Click"
End With
  
End Sub

Public Sub cboDynSecond_Click()

    
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
    If shp.Name = "cboDynSecond" Then
        Exit For
    End If
   Set shp = Nothing
Next
If Not shp Is Nothing Then

    MsgBox ("you clicked on itme index" & shp.ControlFormat.ListIndex _
        & " (value is index: " & shp.ControlFormat.Value _
        & " -while text is " & shp.ControlFormat.List(shp.ControlFormat.ListIndex) _
        & ")")
End If



End Sub

[ghost56]

Thank u Very much. I also need to know how to write itemCLick event for the comboBox. When an item in comboBox is Clicked, a new comboBOx has to be created in its adjacent cell with its associated items. plz help me to resolve this issue also.

[ghost56]

one more, I need to set the comboBox property to autosize. the width of the comboBOx has to be changed regarding the user view. how to set the columnwidth property. i tried with
cell(10,1).ColumnWidth, but it is not working. Any suggestions?

[dglienna]

Record a macro, then see what Excel is doing. You need to do pretty much the same thing

[ghost56]

In the above given code you specified for xlDropDown .Name="cboDynamic"
while executing it is showing an runtime error saying permission denied and it breaks the execution flow.
I think we cant name a control.if not how do we know the default name of the control?

[dglienna]

Again, look in the macro editor. It tells all