how to add items to the comboBox using VBA
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7

Thread: how to add items to the comboBox using VBA

  1. #1
    Join Date
    Apr 2010
    Posts
    7

    how to add items to the comboBox using VBA

    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.

  2. #2
    Join Date
    Jul 2000
    Location
    Milano, Italy
    Posts
    7,726

    Re: how to add items to the comboBox using VBA

    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:
    Code:
    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:
    Code:
    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
    ...at present time, using mainly Net 4.0, Vs 2010



    Special thanks to Lothar "the Great" Haensler, Chris Eastwood , dr_Michael, ClearCode, Iouri and
    all the other wonderful people who made and make Codeguru a great place.
    Come back soon, you Gurus.

  3. #3
    Join Date
    Apr 2010
    Posts
    7

    Re: how to add items to the comboBox using VBA

    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.

  4. #4
    Join Date
    Apr 2010
    Posts
    7

    Re: how to add items to the comboBox using VBA

    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?

  5. #5
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,958

    Re: how to add items to the comboBox using VBA

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  6. #6
    Join Date
    Apr 2010
    Posts
    7

    Exclamation Re: how to add items to the comboBox using VBA

    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?

  7. #7
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,958

    Re: how to add items to the comboBox using VBA

    Again, look in the macro editor. It tells all
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center