0

I found this and tried to apply the code from the most voted answer to my project. What I want to do is to create comboboxes dynamically and assign a function on change event for each one of those. Here is how I tried to do it based on the other thread.

Here is code on my worksheet where the button locates (Sheet1):

Option Explicit

Dim cmdArray() As New Class1

Private Sub CommandButton2_Click()

    Dim ctop#, cleft#, cht#, cwdth#
    
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    
    Dim listfillCell As Range
    
    Dim ColumnLetter As String
    Dim StartAddress As String

    Dim i As Long
    
    Dim ctl_Command As OLEObject
    
    Set sht1 = ThisWorkbook.Worksheets("Sheet1")
    Set sht2 = ThisWorkbook.Worksheets("Sheet2")
        
        For i = 1 To 5
            
            Set listfillCell = sht2.Cells(1, i)
            ColumnLetter = "$" & Split(listfillCell.Address, "$")(1) & "$"
            StartAddress = listfillCell.Address
                
                With sht1.Range("J" & i + 8)
                ctop = .Top
                cleft = .Left
                cht = .Height
                cwdth = .Width
                End With
                
                With sht1
                Set ctl_Command = .OLEObjects.Add(ClassType:="Forms.ComboBox.1", Left:=cleft, Top:=ctop, Width:=cwdth, Height:=cht)
                End With
                
                ctl_Command.Placement = xlMoveAndSize
                ctl_Command.Select
                    
                    With Selection
                        .ListFillRange = ("Sheet2!" & StartAddress & ":" & ColumnLetter) & sht2.Range(listfillCell.Address).End(xlDown).Row
                        .LinkedCell = Cells((i + 8), 6).Address(0, 0)
                        .Object.FontSize = 14
                        .Object.BackColor = RGB(226, 239, 218)
                    End With
                
                ReDim Preserve cmdArray(1 To i)
                
                Set cmdArray(i).CmdEvents = ctl_Command
                Set ctl_Command = Nothing
        
        Next

End Sub

And here is the code in my class module:

Option Explicit

Public WithEvents CmdEvents As MSForms.ComboBox

Private Sub CmdEvents_Change()

    MsgBox "Hello Word"

End Sub

So now it goes up to creating a single combobox and then breaks after line "ReDim Preserve cmdArray(1 To i)" with "Run-time error '13': Type mismatch". What might be wrong with this?

braX
  • 11,506
  • 5
  • 20
  • 33
vaemps
  • 27
  • 6

1 Answers1

0

Two issues. Here's the first:

Dim cmdArray() As New Class1

You probably mean:

Dim cmdArray() as Class1

And here's the second issue:

ReDim Preserve cmdArray(1 To i)

This probably shouldn't be in the loop. Instead, put it above your for loop as

ReDim cmdArray(5)
Xavier J
  • 4,326
  • 1
  • 14
  • 25
  • `ReDim Preserve cmdArray(1 To i)` is not an issue where it is at. It is resizing the array to the current loop count making space to add the new object. Sure, setting the size of the array to the total size needed before the loop is nice if possible, but the way it is in the OP is not an issue. – Daniel Sep 14 '22 at 04:34
  • @Daniel It is absolutely, positively an issue to keep reallocating memory in such a way. It makes for slow, poor-performing code. Please don't make this kind of recommendation with anyone. – Xavier J Sep 14 '22 at 04:41
  • I agree that that is the way to go when the size of the array is known, which is it here. My point is that is not what is causing the poster's error and is just a recommendation which should probably be more clearly stated – Daniel Sep 14 '22 at 04:45