Change event of OleObject (Combo box) in activesheet of excel 2000 using VBA?

Question

--------------------------------------------------------------------------------

1. How to add change event of Oleboject (Combobox) in activesheet of excel 2000? I am having 3 to 4 combox (oleoject) in sheet and like to add change event for each combobox.

2. How to retrieve selected value from combobox (Olebobject) & below code does not work. throwing error as script out of range.

Dim myCmdObj As OLEObject, N%, MySheet$
MySheet = "Sheet" & ActiveSheet.Index
Set myCmdObj = ActiveSheet.OLEObjects. _
Add(ClassType:="Forms.CommandButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=52.5, Top:=50, _
Width:=202.5, Height:=26.25)
myCmdObj.Name = "MyButton"
myCmdObj.Object.Caption = "Button 1"
With ThisWorkbook.VBProject.VBComponents(MySheet).Codemodule
N = .CountOfLines
.InsertLines N + 1, "Private Sub Button_Click()"
.InsertLines N + 2, "End Sub"
End With

creation of combobox at runtime very much required. Kindly suggest me a solution at runtime.

Answer 1

Why are you doing this?
You have two options about ComboBox in Sheet.
Once you bring it from Forms Toolbar, which is I think what you are talking about here.
the other one is from ControlToolbox. Once you draw a combobox from ControlToolbox toolbar you can treate it like a VB Combobox in a form, means it does have a Change event to enable you to write code when the selected value is changed.

Again the Combobox that comes from Forms toolbar, you can right click and assign it with a cell, this will enable you to know what item did the user chose.

Again, You are drawing the combo box in run time, do you realy want to be drawn at run time? just asking..

Read my profile, I am XLMan