Sajagesh


How to write code for dynamically (programatically) generated combo boxes
(excel-shape object) in excel
If the combo boxes are named using the cell reference on which these combo
boxes are placed and linked.

For example, a combo box, placed over the cell "G23", will be named as G23.



Re: How to write code for dynamically (programatically) generated combo boxes (excel-shape object) in excel?

Andy Pope


Hi,

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







Re: How to write code for dynamically (programatically) generated combo boxes (excel-shape object) in excel?

Sajagesh

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






Re: How to write code for dynamically (programatically) generated combo boxes (excel-shape object) in excel?

bi-lya

Try this

Code Snippet

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





Re: How to write code for dynamically (programatically) generated combo boxes (excel-shape object) in excel?

Sajagesh

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





Re: How to write code for dynamically (programatically) generated combo boxes (excel-shape object) in excel?

bi-lya

Make a ClassModule and name it "clsCtrlArr". Write in it

Code Snippet

Public WithEvents ctl As ComboBox

Private Sub ctl_Change()
Cancel = True
MsgBox ctl.Name
End Sub

Make a Module and write in it


Code Snippet

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





Re: How to write code for dynamically (programatically) generated combo boxes (excel-shape object) in excel?

Sajagesh

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.





Re: How to write code for dynamically (programatically) generated combo boxes (excel-shape object) in excel?

bi-lya

Then try the following: make one combobox on sheet "by hand" and run the code. Remove "hand" combobox afer that





Re: How to write code for dynamically (programatically) generated combo boxes (excel-shape object) in excel?

Sajagesh

Thanks a bunch!!!. It worked. I have added "MSForms." before the word Combobox

Cheers