Add combobox to sheet and use Class Module to access events of combobox
Results 1 to 4 of 4

Thread: Add combobox to sheet and use Class Module to access events of combobox
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,703
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    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.
    Richard Schollar

    Using xl2013

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Add combobox to sheet and use Class Module to access events of combobox

    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

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,807
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Add combobox to sheet and use Class Module to access events of combobox

    This was the subject of my first ever post - see if http://www.mrexcel.com/forum/showthread.php?t=284941 helps.

  4. #4
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,703
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Add combobox to sheet and use Class Module to access events of combobox

    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.
    Richard Schollar

    Using xl2013

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •