Add combobox to sheet and use Class Module to access events of combobox

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Andrew Paulsom supplied code to add a combobox and programmatically add code to its events here:

http://www.mrexcel.com/forum/showthread.php?t=281230

However, I would like to investigate using a class module to hold a WithEvents object that could hold pre-determined event code. I would like to create the combobox on the sheet, then assign this combobox via a Property Set statement to my Class. I am having great difficulty doing so, however. This is the code I have tried so far:

Code:
'Standard module:
Sub test()
Dim cbo As OLEObject, objClass As Class1
Set cbo = ActiveSheet.OLEObjects.Add("Forms.Combobox.1")
Set objClass = New Class1
Set objClass.obj = cbo
End Sub
 
'Class module:
Dim WithEvents cbo As msforms.ComboBox
Property Set obj(Combo As Variant)
Set cbo = Combo
End Property
Private Sub cbo_Change()
ActiveSheet.Range("A1").Value = cbo.Value
End Sub

I believe it is erroring out at the Set objClass.obj = cbo line (but I am unable to debug due to adding an ActiveX control to the sheet with stops me from Stepping).

I get a Type Mismatch error here (presumably because an OLEObject is not a MSForms.Combobox) but I have tried variations (including using OLEObject as the declaration in both Class and standard module) and I can get nothing to work.

Can someone put me out of misery and tell me where I'm going wrong (or let me know if it isn't possible)?

Thanks.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
There are a couple of problems with your code, eg you need to assign the OLEObject's Object to the class. That aside, Excel has a problem recognising controls added at run time. The workaround is to use the OnTime method to wait a bit and call another procedure to add the control to the class.

Code:
'Class module named Class1

Option Explicit

Private WithEvents cbo As MSForms.ComboBox

Public Property Set obj(Combo As MSForms.ComboBox)
    Set cbo = Combo
End Property

Private Sub cbo_Change()
    ActiveSheet.Range("A1").Value = cbo.Value
End Sub

'General module
Option Explicit

Dim cboEvents As Collection

Sub test()
    Dim cbo As OLEObject, objClass As Class1
    Set cbo = ActiveSheet.OLEObjects.Add("Forms.Combobox.1")
    With cbo.Object
        .List = Array("a", "b", "c", "d")
    End With
    Application.OnTime Now + TimeValue("00:00:01"), "Initialize"
End Sub
 
Sub Initialize()
    Dim cbo As OLEObject
    Dim Sh As Worksheet
    Dim objClass As Class1
    Set Sh = ThisWorkbook.Worksheets(1)
    If cboEvents Is Nothing Then
        Set cboEvents = New Collection
    End If
    For Each cbo In Sh.OLEObjects
        If TypeName(cbo.Object) = "ComboBox" Then
            Set objClass = New Class1
            Set objClass.obj = cbo.Object
            cboEvents.Add objClass
        End If
    Next
End Sub
 
Upvote 0
Thanks for both your replies. I will have to wait until later to check them out (work is limiting my activitiy at the moment).

Andrew - that's very interesting about Excel having problems recognising controls added at runtime. Even with the workaround, for what I wanted that will prevent me from using my anticipated code solution (I have another way to achieve what I want).

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top