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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,239
Messages
5,509,991
Members
408,768
Latest member
ndg4405

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top