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]

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.myComb oBoxes 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.myComb oBoxes 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.AddIte m 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 AddNewItemToCom boboxes() 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.AddIte m 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 AddNewItemToCom boboxes() 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 'AddNewItemToCom boboxes' 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.AddNew ItemToComboboxe s 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