By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 994 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Access VBA to handle excel ComboBox events

100+
P: 126
Hello everyone,

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

Expand|Select|Wrap|Line Numbers
  1. Public eXL As New eventsXL
  2.  
  3. Function Create_ComboBox()
  4. With eXL
  5. If .XL Is Nothing Then Set .XL = New Excel.Application
  6. .XL.Visible = True
  7. .XL.Interactive = True
  8. Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
  9. Set .WS = .WB.Worksheets("Example")
  10. .WS.Activate
  11.  
  12. .XL.CommandBars("Control Toolbox").Visible = False
  13. Set OLEObj = .WS.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
  14.     DisplayAsIcon:=False, Left:=162, Top:=32.25, Width:=110.25, Height:= _
  15.     33.75).Select
  16.  
  17. Set myOLEObj = OLEObj.Object
  18. With myOLEObj
  19. .AddItem "A"
  20. .AddItem "B"
  21. .AddItem "C"
  22. .AddItem "D"
  23. End With
  24. End With
  25. 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.

Expand|Select|Wrap|Line Numbers
  1. Public WithEvents XL As Excel.Application
  2. Public WithEvents WB As Excel.Workbook
  3. Public WithEvents WS As Excel.Worksheet
  4. Public WithEvents ComboBox1 As ComboBox
  5.  
  6. Private Sub ComboBox1_click()
  7. MsgBox "Hello"
  8. End Sub
Any suggestions/ideas will be greatly appreciated. Thanks in advance!
Aug 15 '08 #1
Share this Question
Share on Google+
26 Replies


FishVal
Expert 2.5K+
P: 2,653
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
Aug 17 '08 #2

100+
P: 126
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
Expand|Select|Wrap|Line Numbers
  1. Public eXL As New eventsXL
  2.  
  3. Function Create_ComboBox()
  4. Dim OLEObj As OLEObject
  5. Dim myOLEObj As MSForms.ComboBox
  6.  
  7. With eXL
  8. If .XL Is Nothing Then Set .XL = New Excel.Application
  9. .XL.Visible = True
  10. .XL.Interactive = True
  11. Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
  12. Set .WS = .WB.Worksheets("SelectPolicies")
  13. .WS.Activate
  14.  
  15. .XL.CommandBars("Control Toolbox").Visible = False
  16. .WS.OLEObjects.Delete
  17. Set OLEObj = .WS.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
  18.     DisplayAsIcon:=False, Left:=266, Top:=14, Width:=110.25, Height:= _
  19.     23.75)
  20.  
  21. Set myOLEObj = OLEObj.Object
  22. With myOLEObj
  23. .AddItem "A"
  24. .AddItem "B"
  25. .AddItem "C"
  26. .AddItem "D"
  27. End With
  28. End With
  29. End Function
2. Created "eventsXL" class module as follows:
Expand|Select|Wrap|Line Numbers
  1. Public WithEvents XL As Excel.Application
  2. Public WithEvents WB As Excel.Workbook
  3. Public WithEvents WS As Excel.Worksheet
  4. Public WithEvents ComboBox1 As ComboBox
  5.  
  6. Private Sub ComboBox1_click()
  7. MsgBox "Hello"
  8. End Sub
  9.  
  10. Private Sub XL_WorkbookBeforeClose(ByVal WB1 As Excel.Workbook, Cancel As Boolean)
  11. With XL.Workbooks
  12. XL.Quit
  13. Set eXL.XL = Nothing
  14. Set eXL.WB = Nothing
  15. Set eXL.WS = Nothing
  16. Set eXL = Nothing
  17. End With
  18. Exit Sub
  19. 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!
Aug 18 '08 #3

FishVal
Expert 2.5K+
P: 2,653
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
Aug 18 '08 #4

100+
P: 126
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)
Expand|Select|Wrap|Line Numbers
  1. Public eXL As New eventsXL
  2.  
  3. Function Create_ComboBox()
  4. Dim OLEObj As OLEObject
  5. Dim myRng As Range, myCell As Range
  6. Dim LastUsedRow As Long
  7.  
  8. With eXL
  9. If .XL Is Nothing Then Set .XL = New Excel.Application
  10. .XL.Visible = True
  11. .XL.Interactive = True
  12. Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
  13. Set .WS = .WB.Worksheets("Example")
  14. .WS.Activate
  15.  
  16. If .XL.WorksheetFunction.CountA(.XL.Cells) > 0 Then
  17.     LastUsedRow = .WS.Cells.Find(what:="*", after:=.WS.Cells(1, 1), lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
  18.     'MsgBox " " & LastUsedRow
  19. End If
  20.  
  21. .XL.CommandBars("Control Toolbox").Visible = False
  22. .WS.OLEObjects.Delete
  23.  
  24. Set myRng = .WS.Range("E2:E" & LastUsedRow)
  25. For Each myCell In myRng.Cells
  26. With myCell
  27.     .NumberFormat = ";;;" 'hide the value in the cell
  28.     Set OLEObj = .Parent.OLEObjects.Add _
  29.                 (ClassType:="Forms.ComboBox.1", Link:=False, _
  30.                 DisplayAsIcon:=False, _
  31.                 Top:=.Top, _
  32.                 Left:=.Left, _
  33.                 Width:=.Width, _
  34.                 Height:=.Height)
  35. End With
  36. Set .myOLEObj = OLEObj.Object
  37. With .myOLEObj
  38. .AddItem "A"
  39. .AddItem "B"
  40. .AddItem "C"
  41. .AddItem "D"
  42. End With
  43. Next myCell
  44. End With
  45. End Function
2. Created the following class module to define combo box events
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public WithEvents XL As Excel.Application
  4. Public WithEvents WB As Excel.Workbook
  5. Public WithEvents WS As Excel.Worksheet
  6. Public WithEvents myOLEObj As MSForms.ComboBox
  7.  
  8. Private Sub myOLEObj_Change()
  9. MsgBox "Hello"
  10. End Sub
  11.  
  12. Private Sub XL_WorkbookBeforeClose(ByVal WB1 As Excel.Workbook, Cancel As Boolean)
  13. With XL.Workbooks
  14. XL.Quit
  15. Set eXL.XL = Nothing
  16. Set eXL.WB = Nothing
  17. Set eXL.WS = Nothing
  18. Set eXL = Nothing
  19. End With
  20. Exit Sub
  21. 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!
Aug 18 '08 #5

FishVal
Expert 2.5K+
P: 2,653
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
Aug 18 '08 #6

100+
P: 126
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!
Aug 18 '08 #7

FishVal
Expert 2.5K+
P: 2,653
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
Attached Files
File Type: zip ObjectGeneratingEventsCollection.zip (16.3 KB, 299 views)
Aug 18 '08 #8

100+
P: 126
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))

Expand|Select|Wrap|Line Numbers
  1. Public eXL As New eventsXL
  2.  
  3. Function Create_ComboBox()
  4. Dim OLEObj As OLEObject
  5. Dim myRng As Range, myCell As Range
  6. Dim LastUsedRow As Long
  7.  
  8. With eXL
  9. If .XL Is Nothing Then Set .XL = New Excel.Application
  10. .XL.Visible = True
  11. .XL.Interactive = True
  12. Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
  13. Set .WS = .WB.Worksheets("Example")
  14. .WS.Activate
  15.  
  16. If .XL.WorksheetFunction.CountA(.XL.Cells) > 0 Then
  17.     LastUsedRow = .WS.Cells.Find(what:="*", after:=.WS.Cells(1, 1), lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
  18.     'MsgBox " " & LastUsedRow
  19. End If
  20.  
  21. .XL.CommandBars("Control Toolbox").Visible = False
  22. .WS.OLEObjects.Delete
  23.  
  24. Set myRng = .WS.Range("E2:E" & LastUsedRow)
  25. For Each myCell In myRng.Cells
  26. With myCell
  27.     .NumberFormat = ";;;" 'hide the value in the cell
  28.     Set OLEObj = .Parent.OLEObjects.Add _
  29.                 (ClassType:="Forms.ComboBox.1", Link:=False, _
  30.                 DisplayAsIcon:=False, _
  31.                 Top:=.Top, _
  32.                 Left:=.Left, _
  33.                 Width:=.Width, _
  34.                 Height:=.Height)
  35. End With
  36. Set .myOLEObj = OLEObj.Object
  37. With .myOLEObj
  38.     .AddItem "A"
  39.     .AddItem "B"
  40.     .AddItem "C"
  41.     .AddItem "D"
  42. End With
  43. Set .myOLEObj = .Abc
  44. Next myCell
  45. End With
  46. End Function
2. Created the following "eventsXL" class module
Expand|Select|Wrap|Line Numbers
  1. Public WithEvents XL As Excel.Application
  2. Public WithEvents WB As Excel.Workbook
  3. Public WithEvents WS As Excel.Worksheet
  4. Public myOLEObj As msforms.ComboBox
  5.  
  6. Public Property Get myComboBoxes() As msforms.ComboBox
  7.     Set myComboBoxes = myOLEObj
  8. End Property
  9.  
  10. Public Function Abc() As Class1
  11.     Set Abc = New Class1
  12.     Set Abc.myComboBox = myComboBoxes
  13. End Function
  14.  
3. Created the following "Class1" class module to handle combo box events
Expand|Select|Wrap|Line Numbers
  1. Public WithEvents myOLE As msforms.ComboBox
  2.  
  3. Public Property Get myComboBox() As msforms.ComboBox
  4. Set myComboBox = myOLE
  5. End Property
  6.  
  7. Public Property Set myComboBox(myCombo As msforms.ComboBox)
  8. Set myOLE = myCombo
  9. End Property
  10.  
  11. Private Sub myOLE_Change()
  12. MsgBox "Hello"
  13. 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.
Aug 19 '08 #9

FishVal
Expert 2.5K+
P: 2,653
You try to assign Class1 object to eventsXL.myOLEobj variable declared as MSForms.Combobox.
Aug 19 '08 #10

100+
P: 126
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.
Aug 19 '08 #11

FishVal
Expert 2.5K+
P: 2,653
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?
Aug 19 '08 #12

100+
P: 126
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:
Expand|Select|Wrap|Line Numbers
  1. 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"
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 19 '08 #13

FishVal
Expert 2.5K+
P: 2,653
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.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private WithEvents objButton As Access.CommandButton
  4.  
  5. Public Property Get Button() As Access.CommandButton
  6.     Set Button = objButton
  7. End Property
  8.  
  9. Public Property Set Button(ByRef objNewValue As Access.CommandButton)
  10.     Set objButton = objNewValue
  11.     objButton.OnClick = "[Event Procedure]"
  12. End Property
  13.  
  14. Private Sub Class_Terminate()
  15.     Set objButton = Nothing
  16. End Sub
  17.  
  18. Private Sub objButton_Click()
  19.     MsgBox objButton.Caption & " clicked"
  20. End Sub
  21.  
Each object of the class stores Access.CommandButton object as well as Click event handler.


Class: ButtonCollection

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private colButtons As New VBA.Collection
  4.  
  5. Private Sub Class_Terminate()
  6.     Set colButtons = Nothing
  7. End Sub
  8.  
  9. Public Property Get Buttons() As VBA.Collection
  10.     Set Buttons = colButtons
  11. End Property
  12.  
  13. Public Function Add(objItem As Variant) As Access9db.ButtonWrapper
  14.  
  15.     Set Add = New Access9db.ButtonWrapper
  16.     Set Add.Button = objItem
  17.     Me.Buttons.Add Add
  18.  
  19. End Function
  20.  
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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private objButtonsCollection As Access9db.ButtonCollection
  4.  
  5. Private Sub Form_Load()
  6.  
  7.     Set objButtonsCollection = New Access9db.ButtonCollection
  8.  
  9.     For Each ctrl In Me.Controls
  10.         If Left(ctrl.Name, 3) = "btn" Then objButtonsCollection.Add ctrl
  11.     Next
  12.  
  13. End Sub
  14.  
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
Aug 19 '08 #14

ADezii
Expert 5K+
P: 8,597
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.
Aug 19 '08 #15

100+
P: 126
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.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private WithEvents objButton As Access.CommandButton
  4.  
  5. Public Property Get Button() As Access.CommandButton
  6.     Set Button = objButton
  7. End Property
  8.  
  9. Public Property Set Button(ByRef objNewValue As Access.CommandButton)
  10.     Set objButton = objNewValue
  11.     objButton.OnClick = "[Event Procedure]"
  12. End Property
  13.  
  14. Private Sub Class_Terminate()
  15.     Set objButton = Nothing
  16. End Sub
  17.  
  18. Private Sub objButton_Click()
  19.     MsgBox objButton.Caption & " clicked"
  20. End Sub
  21.  
Each object of the class stores Access.CommandButton object as well as Click event handler.


Class: ButtonCollection

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private colButtons As New VBA.Collection
  4.  
  5. Private Sub Class_Terminate()
  6.     Set colButtons = Nothing
  7. End Sub
  8.  
  9. Public Property Get Buttons() As VBA.Collection
  10.     Set Buttons = colButtons
  11. End Property
  12.  
  13. Public Function Add(objItem As Variant) As Access9db.ButtonWrapper
  14.  
  15.     Set Add = New Access9db.ButtonWrapper
  16.     Set Add.Button = objItem
  17.     Me.Buttons.Add Add
  18.  
  19. End Function
  20.  
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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private objButtonsCollection As Access9db.ButtonCollection
  4.  
  5. Private Sub Form_Load()
  6.  
  7.     Set objButtonsCollection = New Access9db.ButtonCollection
  8.  
  9.     For Each ctrl In Me.Controls
  10.         If Left(ctrl.Name, 3) = "btn" Then objButtonsCollection.Add ctrl
  11.     Next
  12.  
  13. End Sub
  14.  
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!
Aug 19 '08 #16

FishVal
Expert 2.5K+
P: 2,653
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
Aug 19 '08 #17

FishVal
Expert 2.5K+
P: 2,653
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
Aug 19 '08 #18

ADezii
Expert 5K+
P: 8,597
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.
Aug 19 '08 #19

100+
P: 126
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:
Expand|Select|Wrap|Line Numbers
  1. Public eXL As New eventsXL
  2.  
  3. Function Create_ComboBoxes()
  4. Dim OLEObj As OLEObject
  5. Dim myRng As Range, myCell As Range
  6.  
  7. With eXL
  8. If .XL Is Nothing Then Set .XL = New Excel.Application
  9. .XL.Visible = True
  10. .XL.Interactive = True
  11. Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
  12. Set .WS = .WB.Worksheets("Example")
  13. .WS.Activate
  14.  
  15. .XL.CommandBars("Control Toolbox").Visible = False
  16. .WS.OLEObjects.Delete
  17.  
  18. Set myRng = .WS.Range("E2:E12")
  19. For Each myCell In myRng.Cells
  20. With myCell
  21.     .NumberFormat = ";;;" 'hide the value in the cell
  22.     Set OLEObj = .Parent.OLEObjects.Add _
  23.                 (ClassType:="Forms.ComboBox.1", Link:=False, _
  24.                 DisplayAsIcon:=False, _
  25.                 Top:=.Top, _
  26.                 Left:=.Left, _
  27.                 Width:=.Width, _
  28.                 Height:=.Height)
  29. End With
  30. Set .myOLEObj = OLEObj.Object
  31. Next myCell
  32.  
  33. For Each ctrl In .WS.OLEObjects
  34. .Abc ctrl
  35. Next
  36.  
  37. End With
  38. End Function
2. "eventsXL" class module:
Expand|Select|Wrap|Line Numbers
  1. Public WithEvents XL As Excel.Application
  2. Public WithEvents WB As Excel.Workbook
  3. Public WithEvents WS As Excel.Worksheet
  4. Public myOLEObj As msforms.ComboBox
  5. Private myObj As New VBA.Collection
  6.  
  7. Public Property Get myComboBoxes() As VBA.Collection
  8.     Set myComboBoxes = myObj
  9. End Property
  10.  
  11. Public Function Abc(mObj As Variant) As Class1
  12.     Set Abc = New Class1
  13.     Set Abc.myComboBox = mObj
  14.     Me.myComboBoxes.Add Abc
  15. End Function
3. "Class1" class module
Expand|Select|Wrap|Line Numbers
  1. Public WithEvents myOLE As msforms.ComboBox
  2.  
  3. Public Property Get myComboBox() As msforms.ComboBox
  4. Set myComboBox = myOLE
  5. End Property
  6.  
  7. Public Property Set myComboBox(ByRef myCombo As msforms.ComboBox)
  8. Set myOLE = myCombo
  9. myOLE.AddItem "A"
  10. myOLE.AddItem "B"
  11. myOLE.AddItem "C"
  12. myOLE.AddItem "D"
  13. End Property
  14.  
  15. Public Sub myOLE_Change()
  16. MsgBox "Hello"
  17. End Sub
Please kindly let me know what to do?
Aug 19 '08 #20

FishVal
Expert 2.5K+
P: 2,653
Expand|Select|Wrap|Line Numbers
  1. With myCell
  2.     .NumberFormat = ";;;" 'hide the value in the cell
  3.     Set OLEObj = .Parent.OLEObjects.Add _
  4.                 (ClassType:="Forms.ComboBox.1", Link:=False, _
  5.                 DisplayAsIcon:=False, _
  6.                 Top:=.Top, _
  7.                 Left:=.Left, _
  8.                 Width:=.Width, _
  9.                 Height:=.Height)
  10. End With
  11. Set .myOLEObj = OLEObj.Object
  12. Next myCell
  13.  
  14. For Each ctrl In .WS.OLEObjects
  15. .Abc ctrl
  16. Next
  17.  
There is no need to iterate OLEObjects collection. Add control to eventsXL.myComboBoxes collection as soon as you've created it.

Expand|Select|Wrap|Line Numbers
  1. With myCell
  2.     .NumberFormat = ";;;" 'hide the value in the cell
  3.     Set OLEObj = .Parent.OLEObjects.Add _
  4.                 (ClassType:="Forms.ComboBox.1", Link:=False, _
  5.                 DisplayAsIcon:=False, _
  6.                 Top:=.Top, _
  7.                 Left:=.Left, _
  8.                 Width:=.Width, _
  9.                 Height:=.Height)
  10. End With
  11. .Abc OLEObj.Object
  12. Next myCell
  13.  
Aug 20 '08 #21

100+
P: 126
Expand|Select|Wrap|Line Numbers
  1. With myCell
  2.     .NumberFormat = ";;;" 'hide the value in the cell
  3.     Set OLEObj = .Parent.OLEObjects.Add _
  4.                 (ClassType:="Forms.ComboBox.1", Link:=False, _
  5.                 DisplayAsIcon:=False, _
  6.                 Top:=.Top, _
  7.                 Left:=.Left, _
  8.                 Width:=.Width, _
  9.                 Height:=.Height)
  10. End With
  11. Set .myOLEObj = OLEObj.Object
  12. Next myCell
  13.  
  14. For Each ctrl In .WS.OLEObjects
  15. .Abc ctrl
  16. Next
  17.  
There is no need to iterate OLEObjects collection. Add control to eventsXL.myComboBoxes collection as soon as you've created it.

Expand|Select|Wrap|Line Numbers
  1. With myCell
  2.     .NumberFormat = ";;;" 'hide the value in the cell
  3.     Set OLEObj = .Parent.OLEObjects.Add _
  4.                 (ClassType:="Forms.ComboBox.1", Link:=False, _
  5.                 DisplayAsIcon:=False, _
  6.                 Top:=.Top, _
  7.                 Left:=.Left, _
  8.                 Width:=.Width, _
  9.                 Height:=.Height)
  10. End With
  11. .Abc OLEObj.Object
  12. Next myCell
  13.  
With the above mentioned change the code works absolutely fine.

Thank you very much for your help :)
Aug 20 '08 #22

FishVal
Expert 2.5K+
P: 2,653
You are quite welcome.

Best regards,
Fish
Aug 20 '08 #23

100+
P: 126
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

Expand|Select|Wrap|Line Numbers
  1. Public eXL As New eventsXL
  2.  
  3. Function Create_ComboBoxes()
  4. Dim OLEObj As OLEObject
  5. Dim myRng As Range, myCell As Range
  6.  
  7. With eXL
  8. If .XL Is Nothing Then Set .XL = New Excel.Application
  9. .XL.Visible = True
  10. .XL.Interactive = True
  11. Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
  12. Set .WS = .WB.Worksheets("Example")
  13. .WS.Activate
  14.  
  15. .XL.CommandBars("Control Toolbox").Visible = False
  16. .WS.OLEObjects.Delete
  17.  
  18. Set myRng = .WS.Range("E2:E12")
  19. For Each myCell In myRng.Cells
  20. With myCell
  21.     .NumberFormat = ";;;" 'hide the value in the cell
  22.     Set OLEObj = .Parent.OLEObjects.Add _
  23.                 (ClassType:="Forms.ComboBox.1", Link:=False, _
  24.                 DisplayAsIcon:=False, _
  25.                 Top:=.Top, _
  26.                 Left:=.Left, _
  27.                 Width:=.Width, _
  28.                 Height:=.Height)
  29. End With
  30. .Abc OLEObj.Object
  31. Next myCell
  32. End With
  33. End Function
  34.  
  35. Function NewItem() As String
  36. Dim itemStr As String, j As Integer
  37. With eXL
  38. itemStr = .XL.InputBox(Prompt:="Please enter a new item in to the list.", _
  39.                 Title:="New Item", Type:=2)
  40. If (itemStr = "False") Then
  41. NewItem = "False"
  42. Else
  43. NewItem = itemStr
  44. End If
  45. End With
  46. End Function
2. "eventsXL" class module

Expand|Select|Wrap|Line Numbers
  1. Public WithEvents XL As Excel.Application
  2. Public WithEvents WB As Excel.Workbook
  3. Public WithEvents WS As Excel.Worksheet
  4. Private myObj As New VBA.Collection
  5.  
  6. Public Property Get myComboBoxes() As VBA.Collection
  7.     Set myComboBoxes = myObj
  8. End Property
  9.  
  10. Public Function Abc(Obj As Variant) As Class1
  11.     Set Abc = New Class1
  12.     Set Abc.myComboBox = Obj
  13.     Me.myComboBoxes.Add Abc
  14. End Function
3. "Class1" class module to handle combo box events

Expand|Select|Wrap|Line Numbers
  1. Public WithEvents myOLE As MSForms.ComboBox
  2.  
  3. Public Property Get myComboBox() As MSForms.ComboBox
  4. Set myComboBox = myOLE
  5. End Property
  6.  
  7. Public Property Set myComboBox(ByRef myCombo As MSForms.ComboBox)
  8. Set myOLE = myCombo
  9. myOLE.AddItem "A"
  10. myOLE.AddItem "B"
  11. myOLE.AddItem "Add New Item"
  12. End Property
  13.  
  14. Public Sub myOLE_Change()
  15. Dim Item As String
  16.  
  17. Select Case myOLE.Text
  18. Case "Add New Item"
  19. Item = NewItem
  20.  
  21. If (Item <> "False") And (Len(Item) <> 0) Then
  22. myOLE.AddItem Item
  23. End If
  24.  
  25. Case Else
  26. End Select
  27. End Sub
Aug 21 '08 #24

FishVal
Expert 2.5K+
P: 2,653
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub AddNewItemToComboboxes()
  2.  
  3.     Dim Item As String
  4.  
  5.     Item = NewItem
  6.     If (Item <> "False") And (Len(Item) <> 0) Then
  7.     For Each obj In Me.myComboBoxes
  8.         obj.myComboBox.AddItem Item
  9.     Next
  10.  
  11. End Sub
  12.  
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.
Expand|Select|Wrap|Line Numbers
  1. ....
  2. Public objParent As eventsXL
  3.  
  4. Public Property Get Parent() As eventsXL
  5.     Set Parent = objParent
  6. End Property
  7.  
  8. Public Property Set Parent(objNewValue As eventsXL)
  9.     Set objParent = objNewValue
  10. End Property
  11. .....
  12.  
  13. Public Sub myOLE_Change()
  14.  
  15.     Dim Item As String
  16.  
  17.     Select Case myOLE.Text
  18.         Case "Add New Item"
  19.             Me.Parent.AddNewItemToComboboxes
  20.         Case Else
  21.     End Select
  22.  
  23. End Sub
  24.  
eventsXL module:
Expand|Select|Wrap|Line Numbers
  1. Public Function Abc(Obj As Variant) As Class1
  2.     Set Abc = New Class1
  3.     Set Abc.Parent = Me
  4.     Set Abc.myComboBox = Obj
  5.     Me.myComboBoxes.Add Abc
  6. End Function
  7.  
Aug 21 '08 #25

100+
P: 126
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub AddNewItemToComboboxes()
  2.  
  3.     Dim Item As String
  4.  
  5.     Item = NewItem
  6.     If (Item <> "False") And (Len(Item) <> 0) Then
  7.     For Each obj In Me.myComboBoxes
  8.         obj.myComboBox.AddItem Item
  9.     Next
  10.  
  11. End Sub
  12.  
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.
Expand|Select|Wrap|Line Numbers
  1. ....
  2. Public objParent As eventsXL
  3.  
  4. Public Property Get Parent() As eventsXL
  5.     Set Parent = objParent
  6. End Property
  7.  
  8. Public Property Set Parent(objNewValue As eventsXL)
  9.     Set objParent = objNewValue
  10. End Property
  11. .....
  12.  
  13. Public Sub myOLE_Change()
  14.  
  15.     Dim Item As String
  16.  
  17.     Select Case myOLE.Text
  18.         Case "Add New Item"
  19.             Me.Parent.AddNewItemToComboboxes
  20.         Case Else
  21.     End Select
  22.  
  23. End Sub
  24.  
eventsXL module:
Expand|Select|Wrap|Line Numbers
  1. Public Function Abc(Obj As Variant) As Class1
  2.     Set Abc = New Class1
  3.     Set Abc.Parent = Me
  4.     Set Abc.myComboBox = Obj
  5.     Me.myComboBoxes.Add Abc
  6. End Function
  7.  
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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub myOLE_Change()
  2. Dim Item As String
  3.  
  4. Select Case myOLE.Text
  5. Case "Add New Item"
  6. Item = NewItem
  7.  
  8. If (Item <> "False") And (Len(Item) <> 0) Then
  9. AddNewItemToComboboxes Item
  10. End If
  11.  
  12. Case Else
  13. End Select
  14. End Sub
2. Added 'AddNewItemToComboboxes' function to the Access VBA module
Expand|Select|Wrap|Line Numbers
  1. Public Function AddNewItemToComboboxes(iVal As String)
  2. For Each Obj In eXL.myComboBoxes
  3. Obj.myComboBox.AddItem iVal
  4. Next
  5. 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 :)
Aug 21 '08 #26

FishVal
Expert 2.5K+
P: 2,653
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
Aug 21 '08 #27

Post your reply

Sign in to post your reply or Sign up for a free account.