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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,917
Messages
5,483,699
Members
407,401
Latest member
Enisi2000

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top