0

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 its 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:

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:

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.

Thanks, Izz.

Idzkandar Askzra
  • 3
  • 2
  • When you're using `mybo.Copy` and `myCbo.Paste`, there is no code or indication of where you intend the Paste to go. You'd probably have to tell each `clsLineCopy` object which other `clsLineCopy` object to transfer data to. – PeterT May 21 '18 at 03:03

1 Answers1

4

In my demo I named the Userform -> FormComboGrid

Here are the changes you need:

  • Userform: Public CallBack method

  • Userform: Class level boolean variable used to prevent cascading CallBacks

  • myCommonCbo has to be elevated to a Class Level Variable. This keeps the references valid after the UserForm_Initialize finishes execution.

    • clsLineCopy should have an Init method used to pass a reference of the Userform instance and the Combobox that is being hooked.

enter image description here

FormComboGrid:Class

Option Explicit
Private myCommonCbo  As New Collection
Private ComboBoxEventEnabled As Boolean

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim cbo As clsLineCopy
    For i = 1 To 70
        Set cbo = New clsLineCopy
        cbo.Init Me, Me.Controls("ComboBox" & i)
        myCommonCbo.Add Item:=cbo
       ' Me.Controls("ComboBox" & i).List = Array(1, 2, 3, 4, 5, 6, 7)
    Next i

    ComboBoxEventEnabled = True
End Sub

Public Sub ComboboxChange(cbo As MSForms.ComboBox)
    If Not ComboBoxEventEnabled Then Exit Sub
    ComboBoxEventEnabled = False

    Dim index As Long, r As Long, c As Long
    Dim myCbo As MSForms.ComboBox
    index = Replace(cbo.Name, "ComboBox", "")
    c = index Mod 10
    r = Int(index / 10) + 1
    If r = 7 Then Exit Sub

    index = ((r * 10) + c)
    Set myCbo = Me.Controls("ComboBox" & index)
    myCbo.Value = cbo.Value

    ComboBoxEventEnabled = True
End Sub

clsLineCopy:Class

Option Explicit

Private WithEvents myCbo As MSForms.ComboBox
Private mForm As FormComboGrid

Private Sub myCbo_Change()
    mForm.ComboboxChange myCbo
End Sub

Public Sub Init(Form As FormComboGrid, cbo As MSForms.ComboBox)
    Set mForm = Form
    Set myCbo = cbo
End Sub
  • I got 'Method or data member not found' and the VBA window highlighted .Init in Sub UserForm_initialize. What i did were, 1) Copypaste your Option explicit and Sub Userform_initialize into my userform (frmCapacity). 2) Copypaste the Public Sub ComboBoxChange into clsLineCopy. 3) create new class 'FormComboGrid' and pasted the codes above. Anything wrong that I've done? my userform name is 'frmCapacity' and I create a button to load it in my excel. – Idzkandar Askzra May 21 '18 at 08:48
  • You either didn't add an `Init` method into the `clsLineCopy:Class` or you made it a private method. I edited my post because my Class Titles were backwards. –  May 21 '18 at 08:53
  • Okay now I can open the userform. The code can copy the input to the next row but it is not adjacent. I input value on (1,1), the value copied to (2,4). In other word, I input value on ComboBox1, the value copied to ComboBox 11, not (2,1) a.k.a ComboBox7. So now which formula in the code that I need to modify? – Idzkandar Askzra May 21 '18 at 09:07
  • I have my combos 10 x 7 instead of 7 x 10. In the `ComboboxChange` change the `10's` to `7's` and the `7` to `10`. –  May 21 '18 at 09:13
  • 1
    Now already working as intended. Thank you for your help Thomas! – Idzkandar Askzra May 22 '18 at 01:27
  • Hello,it didnt work for me: 1) created these 2 classes and added module: Sub start() UserForm1.Show End Sub. Why it didnt work for me? What i am doing wrong? – Jacek Antek Mar 18 '22 at 08:36