For example, a combo box, placed over the cell "G23", will be named as G23.
Something like this.
Sub Macro1()
'
Dim rngOver As Range
Set rngOver = Range("G23")
With rngOver
With ActiveSheet.DropDowns.Add(.Left, .Top, .Width, .Height)
.Name = rngOver.Address(False, False, xlA1)
End With
End With
End Sub
Hi,
Thanks for your code. But this is not the exact thing I want. I have done this and after that I want to write the "change" event code for this combo. In all the case, the range will not be "G23". It varies in each and every run.
Your help is highly appreciated
Cheers
Try this
Dim MyRange As String
MyRange = ActiveCell.Address(0, 0) 'Cells(3, 3).Address(0, 0)
Set MyComboBox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
Left:=Range(MyRange).Left, Top:=Range(MyRange).Top, _
Width:=Range(MyRange).Width + 2, Height:=Range(MyRange).Height + 2).Object
ActiveSheet.Shapes("ComboBox1").Name = "cmb" & MyRange
If you make several controls, you need use the Class Module and a variable WithEvants for the evants Change
Hi,
I am making several controls through code. what code should I write in the class module? How can I refer the change events of those combo boxes I have created? Can any one explain.
cheers
Make a ClassModule and name it "clsCtrlArr". Write in it
Public WithEvents ctl As ComboBox
Private Sub ctl_Change()
Cancel = True
MsgBox ctl.Name
End Sub
Make a Module and write in it
Public cmbArea() As New clsCtrlArr
Sub InsertMyComboBox()
Dim MyRange As String
MyRange = ActiveCell.Address(0, 0) 'Cells(3, 3).Address(0, 0)
Set MyComboBox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
Left:=Range(MyRange).Left, Top:=Range(MyRange).Top, _
Width:=Range(MyRange).Width + 2, Height:=Range(MyRange).Height + 2).Object
ActiveSheet.Shapes("ComboBox1").Name = "cmb" & MyRange
Range(MyRange).Offset(2).Activate
Application.OnTime Now + TimeValue("00:00:1"), "AreaShape"
End Sub
Private Sub AreaShape()
Dim sh As Shape, i As Long
i = 0
For Each sh In ActiveSheet.Shapes
If Left(sh.Name, 3) = "cmb" Then
ReDim Preserve cmbArea(i)
Set cmbArea(i).ctl = sh.OLEFormat.Object.Object
i = i + 1
End If
Next
cmbFill
End Sub
Private Sub cmbFill()
For Each sp In cmbArea()
With sp.ctl
.Clear
.FontSize = 8
.AddItem "First"
.AddItem "Second"
.AddItem "Third"
End With
Next
End Sub
Hi,
That was fine. I was looking for this. But when I compile the above code, I am getting and error in the class module. The Compile error is "User-defined type not defined". Think the declartion "Public WithEvents ctl As ComboBox" need to be checked. Can we declare a variable object like this?
For your information, I am using Office XP.
Thanks again for the code.
Regards,
Sajagesh.
Then try the following: make one combobox on sheet "by hand" and run the code. Remove "hand" combobox afer that
Thanks a bunch!!!. It worked. I have added "MSForms." before the word Combobox
Cheers
