Update code for userform source

[ipbr21054]

Hi,

I have a userform which has Comboboxes for which i select an item from the drop down list.
Once all the boxes are complete i then click my transfer button to entere it on my worksheet.

This is the code on my transfer button.

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 6
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "MUST SELECT ALL OPTIONS", 48, "CLONING TRANSFER SHEET"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
    
    ControlsArr = Array(Me.ComboBox1, Me.ComboBox2, Me.ComboBox3, Me.ComboBox4, Me.ComboBox5, Me.ComboBox6)
    
    With ThisWorkbook.Worksheets("CLONING")
        .Range("A4").EntireRow.Insert Shift:=xlDown
        .Range("A4:F4").Borders.Weight = xlThin
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 3
               .Cells(4, i + 1) = Val(ControlsArr(i))
               ControlsArr(i).Text = ""
            Case Else
               .Cells(4, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    Next i
    End With
    
    Application.ScreenUpdating = False
    With Sheets("CLONING")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A3:F" & x).Sort key1:=Range("A4"), order1:=xlAscending, Header:=xlGuess
    End With
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    Sheets("CLONING").Range("A4").Select
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    Unload CloningForm
End Sub

The comboboxes currently get populated from Rowsource "example of 1 combobox INFO!AY2:AY21" BUT this isnt good enough for me or the correct way to do it, so i wish it to update itself as this currently is fixed to AY2:AY21

The other comboboxes are populated the same way but a different range.

Please can you advise the correct way for this to be written & i can then follow your example & alter the other comboboxes location etc

 

[DanteAmor]

You can fill them in the Activate event:

Private Sub UserForm_Activate()
  Dim sh As Worksheet
  Set sh = Sheets("INFO")
[COLOR=#008000]  ComboBox1.List = sh.Range("AY2:AY" & sh.Range("AY" & Rows.Count).End(xlUp).Row).Value[/COLOR]
[COLOR=#0000ff]  ComboBox2.List = sh.Range("AZ2:AZ" & sh.Range("AZ" & Rows.Count).End(xlUp).Row).Value[/COLOR]
End Sub