-
April 7th, 2010, 04:53 AM
#1
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.
-
April 7th, 2010, 10:19 AM
#2
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.
-
April 8th, 2010, 12:17 AM
#3
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.
-
April 8th, 2010, 12:20 AM
#4
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?
-
April 9th, 2010, 07:25 PM
#5
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
-
April 12th, 2010, 01:49 AM
#6
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?
-
April 12th, 2010, 03:18 AM
#7
Re: how to add items to the comboBox using VBA
Again, look in the macro editor. It tells all
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|