Announcement

Collapse
No announcement yet.

Class Module codes for Change Event of comboboxes

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Class Module codes for Change Event of comboboxes



    I have a userform where I put 10 rows of comboboxes for 7 columns. Which means I got 70 comboboxes altogether. To ease your understanding, I will refer the first combobox as (1,1) for (row,column).

    What am I trying to do is, when a user input values on any combobox on Row 1, I want the values to be copied on adjacent combobox at Row 2.

    For example, if I select value on (1,3), same value will appear on (2,3). The same thing goes to Row 3 & 4, Row 5 & 6, and so on.

    This is the code on my class module clsLineCopy:

    Code:
    Public WithEvents myCbo As msForms.ComboBox
    
    Private Sub myCbo_Change()
    Dim i As Integer
    
        'MsgBox "Combo Box " & myCbo.Value & " has changed"
        If myCbo.Value <> "" Then
        myCbo.Copy
        myCbo.Paste
        End If
        
    
    End Sub
    This one is my code on my userform_initialize:
    Code:
    Dim myCommonCbo As New Collection
    Dim cbo As clsLineCopy
    For i = 1 To 70
        Set cbo = New clsLineCopy
        Set cbo.myCbo = Me.Controls("ComboBox" & i)
        myCommonCbo.Add Item:=cbo
    Next i
        Set cbo = Nothing
    I know my code in the class module is wrong as I have no idea about it.


    P/s: I refer these codes on this thread https://www.ozgrid.com/forum/forum/h...ple-comboboxes
    Thanks,
    Izz.





  • #2
    The code itself is not too problematic, but the change event will trigger itself and change all the boxes below, so I would need to look up how to turn off events in a userform. I'll see if I can do that.

    Comment


    • #3
      Originally posted by StephenR View Post
      The code itself is not too problematic, but the change event will trigger itself and change all the boxes below, so I would need to look up how to turn off events in a userform. I'll see if I can do that.
      Thanks, I appreciate that.

      Comment


      • #4


        or have the comboboxes numbered/label in certain series sequence
        If the solution helped please donate to RSPCA

        Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

        Comment

        Working...
        X