Access VBA to handle excel ComboBox events

[JFKJr]

Hello everyone,

I have added a ComboBox in excel spreadsheet using the following Access VBA code.

Public eXL As New eventsXL
 
Function Create_ComboBox()
With eXL
If .XL Is Nothing Then Set .XL = New Excel.Application
.XL.Visible = True
.XL.Interactive = True
Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
Set .WS = .WB.Worksheets("Example")
.WS.Activate
 
.XL.CommandBars("Control Toolbox").Visible = False
Set OLEObj = .WS.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
    DisplayAsIcon:=False, Left:=162, Top:=32.25, Width:=110.25, Height:= _
    33.75).Select
 
Set myOLEObj = OLEObj.Object
With myOLEObj
.AddItem "A"
.AddItem "B"
.AddItem "C"
.AddItem "D"
End With
End With
End Function

And, now I would like to display 'MsgBox' whenever a user selects a value from the drop down list of the above created combo box. But some how I am unable to do this!

I created "eventsXL" file and defined the following ComboBox event in order to handle the events.

Public WithEvents XL As Excel.Application
Public WithEvents WB As Excel.Workbook
Public WithEvents WS As Excel.Worksheet
Public WithEvents ComboBox1 As ComboBox
 
Private Sub ComboBox1_click()
MsgBox "Hello"
End Sub

Any suggestions/ideas will be greatly appreciated. Thanks in advance!

[FishVal]

Hi there.

Two points.

• Where do you instantiate Combobox1?
• When declaring variable of class Combobox its a good idea to use explicit library reference.

Kind regards,
Fish

[JFKJr]

Hi there.

Two points.

• Where do you instantiate Combobox1?
• When declaring variable of class Combobox its a good idea to use explicit library reference.

Kind regards,
Fish

Hello FishVal, thanks for the reply. I changed the code a little bit as follows:

1. Created the following Access VBA module

Public eXL As New eventsXL
 
Function Create_ComboBox()
Dim OLEObj As OLEObject
Dim myOLEObj As MSForms.ComboBox
 
With eXL
If .XL Is Nothing Then Set .XL = New Excel.Application
.XL.Visible = True
.XL.Interactive = True
Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
Set .WS = .WB.Worksheets("SelectPolicies")
.WS.Activate
 
.XL.CommandBars("Control Toolbox").Visible = False
.WS.OLEObjects.Delete
Set OLEObj = .WS.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
    DisplayAsIcon:=False, Left:=266, Top:=14, Width:=110.25, Height:= _
    23.75)
 
Set myOLEObj = OLEObj.Object
With myOLEObj
.AddItem "A"
.AddItem "B"
.AddItem "C"
.AddItem "D"
End With
End With
End Function

2. Created "eventsXL" class module as follows:

Public WithEvents XL As Excel.Application
Public WithEvents WB As Excel.Workbook
Public WithEvents WS As Excel.Worksheet
Public WithEvents ComboBox1 As ComboBox
 
Private Sub ComboBox1_click()
MsgBox "Hello"
End Sub
 
Private Sub XL_WorkbookBeforeClose(ByVal WB1 As Excel.Workbook, Cancel As Boolean)
With XL.Workbooks
XL.Quit
Set eXL.XL = Nothing
Set eXL.WB = Nothing
Set eXL.WS = Nothing
Set eXL = Nothing
End With
Exit Sub
End Sub

And regarding your questions,

a) I created a combo box "ComboBox1" using line #17 (first step code), this is where I instantiated the ComboBox1.

b) I am not sure how to declare variable of class Combobox using explicit library reference.

Could you please let me know how to deal with the problem?

Thank you very much!

[FishVal]

I still don't see there an instruction like
Set eXL.Combobox1=myOLEobj
There are also multiple code lines in "eventsXL" class module which will not work as you are expecting.

Kind regards,
Fish

[JFKJr]

I still don't see there an instruction like
Set eXL.Combobox1=myOLEobj
There are also multiple code lines in "eventsXL" class module which will not work as you are expecting.

Kind regards,
Fish

Thanks for your wonderful suggestion. I changed the code to the following as per your suggestion. And now I am able call "Change" event whenever I click a value in the dropdown list of the dynamically created combo box.

But I ran into another problem. Hope you can help me on this!

This is what I am doing:

1. Created the following Access VBA module, which open an excel file and creates combo boxes in the following given range of cells (line #24)

Public eXL As New eventsXL
 
Function Create_ComboBox()
Dim OLEObj As OLEObject
Dim myRng As Range, myCell As Range
Dim LastUsedRow As Long
 
With eXL
If .XL Is Nothing Then Set .XL = New Excel.Application
.XL.Visible = True
.XL.Interactive = True
Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
Set .WS = .WB.Worksheets("Example")
.WS.Activate
 
If .XL.WorksheetFunction.CountA(.XL.Cells) > 0 Then
    LastUsedRow = .WS.Cells.Find(what:="*", after:=.WS.Cells(1, 1), lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
    'MsgBox " " & LastUsedRow
End If
 
.XL.CommandBars("Control Toolbox").Visible = False
.WS.OLEObjects.Delete
 
Set myRng = .WS.Range("E2:E" & LastUsedRow)
For Each myCell In myRng.Cells
With myCell
    .NumberFormat = ";;;" 'hide the value in the cell
    Set OLEObj = .Parent.OLEObjects.Add _
                (ClassType:="Forms.ComboBox.1", Link:=False, _
                DisplayAsIcon:=False, _
                Top:=.Top, _
                Left:=.Left, _
                Width:=.Width, _
                Height:=.Height)
End With
Set .myOLEObj = OLEObj.Object
With .myOLEObj
.AddItem "A"
.AddItem "B"
.AddItem "C"
.AddItem "D"
End With
Next myCell
End With
End Function

2. Created the following class module to define combo box events

Option Compare Database
 
Public WithEvents XL As Excel.Application
Public WithEvents WB As Excel.Workbook
Public WithEvents WS As Excel.Worksheet
Public WithEvents myOLEObj As MSForms.ComboBox
 
Private Sub myOLEObj_Change()
MsgBox "Hello"
End Sub
 
Private Sub XL_WorkbookBeforeClose(ByVal WB1 As Excel.Workbook, Cancel As Boolean)
With XL.Workbooks
XL.Quit
Set eXL.XL = Nothing
Set eXL.WB = Nothing
Set eXL.WS = Nothing
Set eXL = Nothing
End With
Exit Sub
End Sub

The above code is working fine only for the last created combo box, but if I click any of the combo boxes except the last one the code is not displaying "MsgBox" (i.e., not calling "Change" event).

Is there any way that I can assign combo box variable (myOLEObj) to an array variable and attach WithEvents to all created combo boxes in the spreadsheet.

Hope I did not confused you!

I really appreciate your help on this. Thanks!

[FishVal]

Access help says array variable could not be declared WithEvents.
Though you may create an additional class where you instantiate MSForms.Combobox variable and put event handling code, then you declare array or collection of this class in "eventXL" class and fill it with objects of this class. MSForms.Combobox events will be handled in these multiple instances of this class.

Regards,
Fish

[JFKJr]

Access help says array variable could not be declared WithEvents.
Though you may create an additional class where you instantiate MSForms.Combobox variable and put event handling code, then you declare array or collection of this class in "eventXL" class and fill it with objects of this class. MSForms.Combobox events will be handled in these multiple instances of this class.

Regards,
Fish

Hello FishVal, I did not exactly follow the idea you stated above.

Could you please give me an example on your above approach? Thank you very much!

[FishVal]

Hello FishVal, I did not exactly follow the idea you stated above.

Could you please give me an example on your above approach? Thank you very much!

Ok.

I'm going to attach simple mdb (useless as is :)) demonstrating a method of creating collection of objects reusing the same event handler.
In the example Form1 has 20 buttons added to collection on load. Each button is added within wrapping class "ButtonWrapper" to a collection within "ButtonsCollection" class which is being instantiated as global object variable in Form1 module.

Kind regards,
Fish

[JFKJr]

Ok.

I'm going to attach simple mdb (useless as is :)) demonstrating a method of creating collection of objects reusing the same event handler.
In the example Form1 has 20 buttons added to collection on load. Each button is added within wrapping class "ButtonWrapper" to a collection within "ButtonsCollection" class which is being instantiated as global object variable in Form1 module.

Kind regards,
Fish

Hello Fish, thank you very much for the example.

I am pulling my hair to solve this problem. I came up with the following code with the help of your example.

But I am clueless now. Please help me on how to proceed further.

1. Created the following Access VBA module to open an excel file, add combo boxes in the given following range of cells and add items to the combo boxes.
(I set the created combo box to "Abc" class defined in "eventsXL" class module to handle events (line #43))

Public eXL As New eventsXL
 
Function Create_ComboBox()
Dim OLEObj As OLEObject
Dim myRng As Range, myCell As Range
Dim LastUsedRow As Long
 
With eXL
If .XL Is Nothing Then Set .XL = New Excel.Application
.XL.Visible = True
.XL.Interactive = True
Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
Set .WS = .WB.Worksheets("Example")
.WS.Activate
 
If .XL.WorksheetFunction.CountA(.XL.Cells) > 0 Then
    LastUsedRow = .WS.Cells.Find(what:="*", after:=.WS.Cells(1, 1), lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
    'MsgBox " " & LastUsedRow
End If
 
.XL.CommandBars("Control Toolbox").Visible = False
.WS.OLEObjects.Delete
 
Set myRng = .WS.Range("E2:E" & LastUsedRow)
For Each myCell In myRng.Cells
With myCell
    .NumberFormat = ";;;" 'hide the value in the cell
    Set OLEObj = .Parent.OLEObjects.Add _
                (ClassType:="Forms.ComboBox.1", Link:=False, _
                DisplayAsIcon:=False, _
                Top:=.Top, _
                Left:=.Left, _
                Width:=.Width, _
                Height:=.Height)
End With
Set .myOLEObj = OLEObj.Object
With .myOLEObj
    .AddItem "A"
    .AddItem "B"
    .AddItem "C"
    .AddItem "D"
End With
Set .myOLEObj = .Abc
Next myCell
End With
End Function

2. Created the following "eventsXL" class module

Public WithEvents XL As Excel.Application
Public WithEvents WB As Excel.Workbook
Public WithEvents WS As Excel.Worksheet
Public myOLEObj As msforms.ComboBox
 
Public Property Get myComboBoxes() As msforms.ComboBox
    Set myComboBoxes = myOLEObj
End Property
 
Public Function Abc() As Class1
    Set Abc = New Class1
    Set Abc.myComboBox = myComboBoxes
End Function
 

3. Created the following "Class1" class module to handle combo box events

Public WithEvents myOLE As msforms.ComboBox
 
Public Property Get myComboBox() As msforms.ComboBox
Set myComboBox = myOLE
End Property
 
Public Property Set myComboBox(myCombo As msforms.ComboBox)
Set myOLE = myCombo
End Property
 
Private Sub myOLE_Change()
MsgBox "Hello"
End Sub

I am getting "Type Mismatch" error at line #8 in "Class1" class module.

Your help on this will be greatly appreciated. Thanks for your valuable time.

[FishVal]

You try to assign Class1 object to eventsXL.myOLEobj variable declared as MSForms.Combobox.

[JFKJr]

You try to assign Class1 object to eventsXL.myOLEobj variable declared as MSForms.Combobox.

So, do you have any idea on how to assign the objects appropriately to handle combo box events for all dynamically created combo boxes?

Thank you very much.

[FishVal]

Sure I have idea.
I've already posted it in post#8 together with working example.
Tell me what particularly was unclear and I'll try to explain it?

[JFKJr]

Sure I have idea.
I've already posted it in post#8 together with working example.
Tell me what particularly was unclear and I'll try to explain it?

OK, as per your suggestion I changed above line #43 to the following:

Call .Abc 

This time I am not getting any "Type Mismatch" error and I am able to create combo boxes in the above given range of cells (line #24).

But, I am unable to assign combo box "Change" event to any of the combo boxes, not even for the last combo box!

So, I added the following line after line #8 in "Class1" module to check whether "myOLE" object is referring to "myOLEObj"

myOLE.AddItem "E"

And now with the above line I am able to see item "E" in all the created combo boxes drawing to the conclusion that the "myOLE" object is referring to "myOLEObj". But, the code is unable to assign "myOLE_Change" event to all the created combo boxes.

Please kindly let me know where I am doing wrong.

Thank you very much for your help.

[FishVal]

The main and only thing you are doing wrong is that you are make some changes to code without following the whole logic of the proposed solution.

So I'll try to explain that simple code I've provided you in the example.

Class: ButtonWrapper.

Option Compare Database
 
Private WithEvents objButton As Access.CommandButton
 
Public Property Get Button() As Access.CommandButton
    Set Button = objButton
End Property
 
Public Property Set Button(ByRef objNewValue As Access.CommandButton)
    Set objButton = objNewValue
    objButton.OnClick = "[Event Procedure]"
End Property
 
Private Sub Class_Terminate()
    Set objButton = Nothing
End Sub
 
Private Sub objButton_Click()
    MsgBox objButton.Caption & " clicked"
End Sub
 

Each object of the class stores Access.CommandButton object as well as Click event handler.


Class: ButtonCollection

Option Compare Database
 
Private colButtons As New VBA.Collection
 
Private Sub Class_Terminate()
    Set colButtons = Nothing
End Sub
 
Public Property Get Buttons() As VBA.Collection
    Set Buttons = colButtons
End Property
 
Public Function Add(objItem As Variant) As Access9db.ButtonWrapper
 
    Set Add = New Access9db.ButtonWrapper
    Set Add.Button = objItem
    Me.Buttons.Add Add
 
End Function
 

The class exposes Add method which gets an Access.CommandButton object as argument, creates new object of ButtonWrapper class, passes the Access.CommandButton object to it and adds this object of ButtonWrapper class to private collection thus holding multiple objects of ButtonWrapper class where Click events are being handled.

Form: Form1

Option Compare Database
 
Private objButtonsCollection As Access9db.ButtonCollection
 
Private Sub Form_Load()
 
    Set objButtonsCollection = New Access9db.ButtonCollection
 
    For Each ctrl In Me.Controls
        If Left(ctrl.Name, 3) = "btn" Then objButtonsCollection.Add ctrl
    Next
 
End Sub
 

The form on load creats an object of ButtonCollection class and fill its collection invoking Add method with each control having name starting from "btn" (just all that 20 buttons) passed as argument.

Voila.
We have one ButtonCollection object which stores 20 ButtonWrapper objects each handling click event from the correspondent button.

The example is of academic interest as is because the same could be done much easier with Access controls using function call instead of event handler (not applicable in your particular case). But it shows how this general approach may be implemented.

Please let me know whether it makes more sense now.

Kind regards,
Fish

[ADezii]

Sure I have idea.
I've already posted it in post#8 together with working example.
Tell me what particularly was unclear and I'll try to explain it?

Hello FishVal, can you kindly explain to me the significance of the Access9db qualifier in both the Form Level Code, and Add Method of the ButtonCollection Class? OOP is not really my strong point, and you seem to have a firm grasp on it as indicated by this, and previous, Replies. Thanks for your time.

P.S. - I am not in any matter attempting to hijack this Thread, I only wish to be taught something new which would be difficult outside of this current context.

[JFKJr]

The main and only thing you are doing wrong is that you are make some changes to code without following the whole logic of the proposed solution.

So I'll try to explain that simple code I've provided you in the example.

Class: ButtonWrapper.

Option Compare Database
 
Private WithEvents objButton As Access.CommandButton
 
Public Property Get Button() As Access.CommandButton
    Set Button = objButton
End Property
 
Public Property Set Button(ByRef objNewValue As Access.CommandButton)
    Set objButton = objNewValue
    objButton.OnClick = "[Event Procedure]"
End Property
 
Private Sub Class_Terminate()
    Set objButton = Nothing
End Sub
 
Private Sub objButton_Click()
    MsgBox objButton.Caption & " clicked"
End Sub
 

Each object of the class stores Access.CommandButton object as well as Click event handler.


Class: ButtonCollection

Option Compare Database
 
Private colButtons As New VBA.Collection
 
Private Sub Class_Terminate()
    Set colButtons = Nothing
End Sub
 
Public Property Get Buttons() As VBA.Collection
    Set Buttons = colButtons
End Property
 
Public Function Add(objItem As Variant) As Access9db.ButtonWrapper
 
    Set Add = New Access9db.ButtonWrapper
    Set Add.Button = objItem
    Me.Buttons.Add Add
 
End Function
 

The class exposes Add method which gets an Access.CommandButton object as argument, creates new object of ButtonWrapper class, passes the Access.CommandButton object to it and adds this object of ButtonWrapper class to private collection thus holding multiple objects of ButtonWrapper class where Click events are being handled.

Form: Form1

Option Compare Database
 
Private objButtonsCollection As Access9db.ButtonCollection
 
Private Sub Form_Load()
 
    Set objButtonsCollection = New Access9db.ButtonCollection
 
    For Each ctrl In Me.Controls
        If Left(ctrl.Name, 3) = "btn" Then objButtonsCollection.Add ctrl
    Next
 
End Sub
 

The form on load creats an object of ButtonCollection class and fill its collection invoking Add method with each control having name starting from "btn" (just all that 20 buttons) passed as argument.

Voila.
We have one ButtonCollection object which stores 20 ButtonWrapper objects each handling click event from the correspondent button.

The example is of academic interest as is because the same could be done much easier with Access controls using function call instead of event handler (not applicable in your particular case). But it shows how this general approach may be implemented.

Please let me know whether it makes more sense now.

Kind regards,
Fish

Hello Fish, thank you very much for the detailed explanation of the example.

Forgive me if I use the code in the example as is. I am a total new beginner to Access VBA.

I understand the logic behind it but unable to use the logic for my situation. :((

It will be really helpfull if you could direct me on how to change the logic to suit my problem.

It will really make my day :)

Thanks!

[FishVal]

Hello FishVal, can you kindly explain to me the significance of the Access9db qualifier in both the Form Level Code, and Add Method of the ButtonCollection Class? OOP is not really my strong point, and you seem to have a firm grasp on it as indicated by this, and previous, Replies. Thanks for your time.

Hello, ADezii.

Access9db is library qualifier of current access project. I'm just somewhat superstitious as for implicit declarations and always use library qualifiers even when not needed. :)
Though code looks more wordy it helps to prevent many stupid situations I had in past especially taking into account my habbit to reuse popular names in code.

P.S. - I am not in any matter attempting to hijack this Thread, I only wish to be taught something new which would be difficult outside of this current context.

Not a problem. It is always pleasure to hear from you.

Regards,
Fish

[FishVal]

Hello Fish, thank you very much for the detailed explanation of the example.

Forgive me if I use the code in the example as is. I am a total new beginner to Access VBA.

I understand the logic behind it but unable to use the logic for my situation. :((

It will be really helpfull if you could direct me on how to change the logic to suit my problem.

It will really make my day :)

Thanks!

Ok.

Following the logic of the solution, you just need to declare VBA.Collection variable in "eventsXL" class and as soon as new MSForms.Combobox has been created and populated create new object of "Class1" class, store reference to the MSForms.Combobox in it and add it to the that VBA.Collection.

Does it make more sense now?

Regards,
Fish

[ADezii]

Hello, ADezii.

Access9db is library qualifier of current access project. I'm just somewhat superstitious as for implicit declarations and always use library qualifiers even when not needed. :)
Though code looks more wordy it helps to prevent many stupid situations I had in past especially taking into account my habbit to reuse popular names in code.



Not a problem. It is always pleasure to hear from you.

Regards,
Fish

Thanks FishVal, there it was staring me in the face all the time in the Object Browser under the Library Drop Down. I appreciate you taking the time to answer my question.

[JFKJr]

Ok.

Following the logic of the solution, you just need to declare VBA.Collection variable in "eventsXL" class and as soon as new MSForms.Combobox has been created and populated create new object of "Class1" class, store reference to the MSForms.Combobox in it and add it to the that VBA.Collection.

Does it make more sense now?

Regards,
Fish

Hello Fish, first of all thanks for all your patience with me.

I changed the code as per the above suggestion. But I am getting "Type Mismatch" error on line #13 in "eventsXL" class module

1. Access VBA module:

Public eXL As New eventsXL
 
Function Create_ComboBoxes()
Dim OLEObj As OLEObject
Dim myRng As Range, myCell As Range
 
With eXL
If .XL Is Nothing Then Set .XL = New Excel.Application
.XL.Visible = True
.XL.Interactive = True
Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
Set .WS = .WB.Worksheets("Example")
.WS.Activate
 
.XL.CommandBars("Control Toolbox").Visible = False
.WS.OLEObjects.Delete
 
Set myRng = .WS.Range("E2:E12")
For Each myCell In myRng.Cells
With myCell
    .NumberFormat = ";;;" 'hide the value in the cell
    Set OLEObj = .Parent.OLEObjects.Add _
                (ClassType:="Forms.ComboBox.1", Link:=False, _
                DisplayAsIcon:=False, _
                Top:=.Top, _
                Left:=.Left, _
                Width:=.Width, _
                Height:=.Height)
End With
Set .myOLEObj = OLEObj.Object
Next myCell
 
For Each ctrl In .WS.OLEObjects
.Abc ctrl
Next
 
End With
End Function

2. "eventsXL" class module:

Public WithEvents XL As Excel.Application
Public WithEvents WB As Excel.Workbook
Public WithEvents WS As Excel.Worksheet
Public myOLEObj As msforms.ComboBox
Private myObj As New VBA.Collection
 
Public Property Get myComboBoxes() As VBA.Collection
    Set myComboBoxes = myObj
End Property
 
Public Function Abc(mObj As Variant) As Class1
    Set Abc = New Class1
    Set Abc.myComboBox = mObj
    Me.myComboBoxes.Add Abc
End Function

3. "Class1" class module

Public WithEvents myOLE As msforms.ComboBox
 
Public Property Get myComboBox() As msforms.ComboBox
Set myComboBox = myOLE
End Property
 
Public Property Set myComboBox(ByRef myCombo As msforms.ComboBox)
Set myOLE = myCombo
myOLE.AddItem "A"
myOLE.AddItem "B"
myOLE.AddItem "C"
myOLE.AddItem "D"
End Property
 
Public Sub myOLE_Change()
MsgBox "Hello"
End Sub

Please kindly let me know what to do?

[FishVal]

With myCell
    .NumberFormat = ";;;" 'hide the value in the cell
    Set OLEObj = .Parent.OLEObjects.Add _
                (ClassType:="Forms.ComboBox.1", Link:=False, _
                DisplayAsIcon:=False, _
                Top:=.Top, _
                Left:=.Left, _
                Width:=.Width, _
                Height:=.Height)
End With
Set .myOLEObj = OLEObj.Object
Next myCell
 
For Each ctrl In .WS.OLEObjects
.Abc ctrl
Next
 

There is no need to iterate OLEObjects collection. Add control to eventsXL.myComboBoxes collection as soon as you've created it.

With myCell
    .NumberFormat = ";;;" 'hide the value in the cell
    Set OLEObj = .Parent.OLEObjects.Add _
                (ClassType:="Forms.ComboBox.1", Link:=False, _
                DisplayAsIcon:=False, _
                Top:=.Top, _
                Left:=.Left, _
                Width:=.Width, _
                Height:=.Height)
End With
.Abc OLEObj.Object
Next myCell
 

[JFKJr]

With myCell
    .NumberFormat = ";;;" 'hide the value in the cell
    Set OLEObj = .Parent.OLEObjects.Add _
                (ClassType:="Forms.ComboBox.1", Link:=False, _
                DisplayAsIcon:=False, _
                Top:=.Top, _
                Left:=.Left, _
                Width:=.Width, _
                Height:=.Height)
End With
Set .myOLEObj = OLEObj.Object
Next myCell
 
For Each ctrl In .WS.OLEObjects
.Abc ctrl
Next
 

There is no need to iterate OLEObjects collection. Add control to eventsXL.myComboBoxes collection as soon as you've created it.

With myCell
    .NumberFormat = ";;;" 'hide the value in the cell
    Set OLEObj = .Parent.OLEObjects.Add _
                (ClassType:="Forms.ComboBox.1", Link:=False, _
                DisplayAsIcon:=False, _
                Top:=.Top, _
                Left:=.Left, _
                Width:=.Width, _
                Height:=.Height)
End With
.Abc OLEObj.Object
Next myCell
 

With the above mentioned change the code works absolutely fine.

Thank you very much for your help :)

[FishVal]

You are quite welcome.

Best regards,
Fish

[JFKJr]

You are quite welcome.

Best regards,
Fish

Hello Fish, sorry to re-open the thread again!

I ran into some problem, hope I can get help from you.

Extending the above code, this is what I am trying to do

1. The following is the Access VBA module to open an excel spreadsheet, create combo boxes in the given range of cells, add items to all combo boxes.

I have added an item called "Add New Item", which allows the user to enter a new item. And, whenever a user chooses "Add New Item" from the drop down list of a combo box and enter an item, the code should be able to add the new item to all combo boxes removing the redundancy of entering the same item in all the combo boxes manually by the user.

The following code adds a new item only to a single combo box when ever a user selects an "Add New Item" from the drop down list.

Please kindly let me know how to add a new item to all combo boxes whenever a user enters a new item in one combo box.

Hope I did not confuse you!

Thank you very much.

1. Access VBA module

Public eXL As New eventsXL
 
Function Create_ComboBoxes()
Dim OLEObj As OLEObject
Dim myRng As Range, myCell As Range
 
With eXL
If .XL Is Nothing Then Set .XL = New Excel.Application
.XL.Visible = True
.XL.Interactive = True
Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
Set .WS = .WB.Worksheets("Example")
.WS.Activate
 
.XL.CommandBars("Control Toolbox").Visible = False
.WS.OLEObjects.Delete
 
Set myRng = .WS.Range("E2:E12")
For Each myCell In myRng.Cells
With myCell
    .NumberFormat = ";;;" 'hide the value in the cell
    Set OLEObj = .Parent.OLEObjects.Add _
                (ClassType:="Forms.ComboBox.1", Link:=False, _
                DisplayAsIcon:=False, _
                Top:=.Top, _
                Left:=.Left, _
                Width:=.Width, _
                Height:=.Height)
End With
.Abc OLEObj.Object
Next myCell
End With
End Function
 
Function NewItem() As String
Dim itemStr As String, j As Integer
With eXL
itemStr = .XL.InputBox(Prompt:="Please enter a new item in to the list.", _
                Title:="New Item", Type:=2)
If (itemStr = "False") Then
NewItem = "False"
Else
NewItem = itemStr
End If
End With
End Function

2. "eventsXL" class module

Public WithEvents XL As Excel.Application
Public WithEvents WB As Excel.Workbook
Public WithEvents WS As Excel.Worksheet
Private myObj As New VBA.Collection
 
Public Property Get myComboBoxes() As VBA.Collection
    Set myComboBoxes = myObj
End Property
 
Public Function Abc(Obj As Variant) As Class1
    Set Abc = New Class1
    Set Abc.myComboBox = Obj
    Me.myComboBoxes.Add Abc
End Function

3. "Class1" class module to handle combo box events

Public WithEvents myOLE As MSForms.ComboBox
 
Public Property Get myComboBox() As MSForms.ComboBox
Set myComboBox = myOLE
End Property
 
Public Property Set myComboBox(ByRef myCombo As MSForms.ComboBox)
Set myOLE = myCombo
myOLE.AddItem "A"
myOLE.AddItem "B"
myOLE.AddItem "Add New Item"
End Property
 
Public Sub myOLE_Change()
Dim Item As String
 
Select Case myOLE.Text
Case "Add New Item"
Item = NewItem
 
If (Item <> "False") And (Len(Item) <> 0) Then
myOLE.AddItem Item
End If
 
Case Else
End Select
End Sub

[FishVal]

You already have a Collection of Class1 objects in class eventsXL.
The most obvious way to perform same operation with the Comboboxes stored in the Collection members is to iterate the Collection invoking Combobox.AddItem method.

The following code goes in "eventsXL" class module.

Public Sub AddNewItemToComboboxes()
 
    Dim Item As String
 
    Item = NewItem
    If (Item <> "False") And (Len(Item) <> 0) Then
    For Each obj In Me.myComboBoxes
        obj.myComboBox.AddItem Item
    Next
 
End Sub
 

BTW it is better to move NewItem() function to "eventsXL" module or even incorporate its code to AddNewItemToComboboxes() method.

The next question is how to run this method from Change event handler.
An appropriate methos is to make "eventsXL"/"Class1" relationship bidirectional.
"Class1" class stores reference to the parent class - "eventsXL" initialized on "Class1" object creation.

Class1 module.

....
Public objParent As eventsXL
 
Public Property Get Parent() As eventsXL
    Set Parent = objParent
End Property
 
Public Property Set Parent(objNewValue As eventsXL)
    Set objParent = objNewValue
End Property
.....
 
Public Sub myOLE_Change()
 
    Dim Item As String
 
    Select Case myOLE.Text
        Case "Add New Item"
            Me.Parent.AddNewItemToComboboxes
        Case Else
    End Select
 
End Sub
 

eventsXL module:

Public Function Abc(Obj As Variant) As Class1
    Set Abc = New Class1
    Set Abc.Parent = Me
    Set Abc.myComboBox = Obj
    Me.myComboBoxes.Add Abc
End Function
 

[JFKJr]

You already have a Collection of Class1 objects in class eventsXL.
The most obvious way to perform same operation with the Comboboxes stored in the Collection members is to iterate the Collection invoking Combobox.AddItem method.

The following code goes in "eventsXL" class module.

Public Sub AddNewItemToComboboxes()
 
    Dim Item As String
 
    Item = NewItem
    If (Item <> "False") And (Len(Item) <> 0) Then
    For Each obj In Me.myComboBoxes
        obj.myComboBox.AddItem Item
    Next
 
End Sub
 

BTW it is better to move NewItem() function to "eventsXL" module or even incorporate its code to AddNewItemToComboboxes() method.

The next question is how to run this method from Change event handler.
An appropriate methos is to make "eventsXL"/"Class1" relationship bidirectional.
"Class1" class stores reference to the parent class - "eventsXL" initialized on "Class1" object creation.

Class1 module.

....
Public objParent As eventsXL
 
Public Property Get Parent() As eventsXL
    Set Parent = objParent
End Property
 
Public Property Set Parent(objNewValue As eventsXL)
    Set objParent = objNewValue
End Property
.....
 
Public Sub myOLE_Change()
 
    Dim Item As String
 
    Select Case myOLE.Text
        Case "Add New Item"
            Me.Parent.AddNewItemToComboboxes
        Case Else
    End Select
 
End Sub
 

eventsXL module:

Public Function Abc(Obj As Variant) As Class1
    Set Abc = New Class1
    Set Abc.Parent = Me
    Set Abc.myComboBox = Obj
    Me.myComboBoxes.Add Abc
End Function
 

Excellent Fish, the code is working like a charm!

I just made the following minor changes to the code with your help and it is working absolutely fine.

1. Changed the "Change" event in "Class1" class module to the following:

Public Sub myOLE_Change()
Dim Item As String
 
Select Case myOLE.Text
Case "Add New Item"
Item = NewItem
 
If (Item <> "False") And (Len(Item) <> 0) Then
AddNewItemToComboboxes Item
End If
 
Case Else
End Select
End Sub

2. Added 'AddNewItemToComboboxes' function to the Access VBA module

Public Function AddNewItemToComboboxes(iVal As String)
For Each Obj In eXL.myComboBoxes
Obj.myComboBox.AddItem iVal
Next
End Function

Please feel free to correct me if anything is wrong with the above code.

You saved my whole day, Thanks a zillion times for all your help :)

[FishVal]

Oh, you are welcome.

As for code design there are two answers:

• As long as something is working there is no need to change it.
• On the other hand I think a better place for NewItem() function code is in "eventsXL" class or even within eventsXL.AddNewItemToComboboxes method together with XL.InputBox result checking. I could hardly imagine this code reused by other modules. So it looks logical to place it where it is really used to make code more readable, elegant and avoid unnecessary intermodule calls.

Regards,
Fish