VBA 在表格中创建和使用下拉框(xlDropdow)

how to add items to the comboBox using VBA

I have created a comboBox1 on an excel sheet with the following statementSet 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.

650) this.width=650;” src=”http://forums.codeguru.com/image.php?s=6608aa8ee0982ecd07ac6e1189ce6c90&u=34090&dateline=1268516671″ alt=”Cimperiali's Avatar” title=”Cimperiali's Avatar” >

Cimperiali650) this.width=650;” class=”inlineimg onlinestatus” src=”http://img.68idc.cn/uploadfile/allimg/150816/0P20RB6-0.png” alt=”Cimperiali is offline” border=”0″ title=”Cimperiali is offline” >Old Uncle ModeratorPower Poster650) this.width=650;” class=”repimg” src=”http://img.68idc.cn/uploadfile/allimg/150816/0P20WF6-1.png” >650) this.width=650;” class=”repimg” src=”http://img.68idc.cn/uploadfile/allimg/150816/0P20WF6-1.png” >650) this.width=650;” class=”repimg” src=”http://img.68idc.cn/uploadfile/allimg/150816/0P20WF6-1.png” >650) this.width=650;” class=”repimg” src=”http://img.68idc.cn/uploadfile/allimg/150816/0P20WF6-1.png” >650) this.width=650;” class=”repimg” src=”http://img.68idc.cn/uploadfile/allimg/150816/0P20WF6-1.png” >650) this.width=650;” class=”repimg” src=”http://img.68idc.cn/uploadfile/allimg/150816/0P20VZ3-8.png” >650) this.width=650;” class=”repimg” src=”http://img.68idc.cn/uploadfile/allimg/150816/0P20VZ3-8.png” >650) this.width=650;” class=”repimg” src=”http://img.68idc.cn/uploadfile/allimg/150816/0P20VZ3-8.png” >650) this.width=650;” class=”repimg” src=”http://img.68idc.cn/uploadfile/allimg/150816/0P20VZ3-8.png” >

Join DateJul 2000LocationMilano, ItalyPosts7,724

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 followingin a worksheet:

Code:

Private Sub Worksheet_Activate()Dim shp As ShapeFor Each shp In Me.Shapes If shp.Name = "cboDynamic" Then shp.Delete Set shp = Nothing Exit For End If Set shp = NothingNextIf shp Is Nothing Then CreateCbo End IfEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)End SubPrivate 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

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 nextOn 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 = NothingWith 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 SubPublic Sub cboDynSecond_Click()    Dim shp As ShapeFor Each shp In ActiveSheet.Shapes    If shp.Name = "cboDynSecond" Then        Exit For    End If   Set shp = NothingNextIf 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 IfEnd Sub

一切伟大的行动和思想，都有一个微不足道的开始

相关文章：

你感兴趣的文章：

标签云：