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!

[JFKJr]

You try to assign Class1 object to eventsXL.myOLEo bj variable declared as MSForms.Combobo x.

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_Chan ge" 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.CommandB utton object as well as Click event handler.


Class: ButtonCollectio n

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.CommandB utton object as argument, creates new object of ButtonWrapper class, passes the Access.CommandB utton 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 ButtonCollectio n 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 ButtonCollectio n 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 ButtonCollectio n 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.CommandB utton object as well as Click event handler.


Class: ButtonCollectio n

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.CommandB utton object as argument, creates new object of ButtonWrapper class, passes the Access.CommandB utton 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 ButtonCollectio n 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 ButtonCollectio n 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 ButtonCollectio n 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.Combobo x has been created and populated create new object of "Class1" class, store reference to the MSForms.Combobo x 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.Combobo x has been created and populated create new object of "Class1" class, store reference to the MSForms.Combobo x 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?