Could you check my userform code please

[ipbr21054]

Hi,

Here is my code which needs a checkover please.

I had changed from 9 comboboxes to two textboxes and 7 comboboxes.
I am not sure what needs to be changed in the line With Me.Controls("ComboBox" & i)

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 9
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "MUST SELECT ALL OPTIONS", 48, "MC LIST TRANSFER"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
    
    ControlsArr = Array(Me.TextBox1, Me.TextBox2, Me.ComboBox3, Me.ComboBox4, Me.ComboBox6, Me.ComboBox7, Me.ComboBox8, Me.ComboBox9)
    
    With ThisWorkbook.Worksheets("MC LIST")
        .Range("A8").EntireRow.Insert Shift:=xlDown
        .Range("A8:I8").Borders.Weight = xlThin
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 1, 2, 4
               .Cells(4, i + 1) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))
            Case Else
               .Cells(4, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    Next i
    End With
    
    Application.ScreenUpdating = False
    With Sheets("MC LIST")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A7:I" & x).Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlGuess
    End With
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    Sheets("MC LIST").Range("A8").Select
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    Unload McListForm
End Sub

 

[dmt32]

Hi,
untested but try this update to your code & see if does what you want

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim x As Long
    Dim ControlsArr(1 To 9) As Variant
    
    
    For i = 1 To 9
        If i > 2 Then
            With Me.Controls("ComboBox" & i)
                If .ListIndex = -1 Then
                    MsgBox "MUST SELECT ALL OPTIONS", 48, "MC LIST TRANSFER"
                    .SetFocus
                    Exit Sub
                Else
                    ControlsArr(i) = .Value
                End If
            End With
        Else
            ControlsArr(i) = Me.Controls("TextBox" & i).Value
        End If
    Next i
        
        Application.ScreenUpdating = False
        
        With ThisWorkbook.Worksheets("MC LIST")
            .Range("A8").EntireRow.Insert Shift:=xlDown
            .Range("A8:I8").Borders.Weight = xlThin
            .Cells(4, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
            
            If .AutoFilterMode Then .AutoFilterMode = False
            x = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A7:I" & x).Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlGuess
            .Range("A8").Select
        End With
        
        ActiveWorkbook.Save
        Application.ScreenUpdating = True
        MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
        Unload Me
End Sub

Note: You state there are 7 comboboxes but ComboBox5 is missing in your array list - I take it this is a typo error as my code includes it?

Dave

 

[ipbr21054]

Hi,

It keeps debugging to this.

With Me.Controls("ComboBox" & i)

Lets start again to be clear.

TextBox 1 and 2

ComboBox 1 - 6

 

[dmt32]

Hi,

It keeps debugging to this.

With Me.Controls("ComboBox" & i)

Lets start again to be clear.

TextBox 1 and 2

ComboBox 1 - 6

In your post you stated

I had changed from 9 comboboxes to two textboxes and 7 comboboxes.

which is 9 controls

I also mentioned that in your array list ComboBox5 is missing and asked if it was a Typo error as my code included it & probably explains the error you are getting

So question is - You are now saying it's Two Textboxes & 6 Comboxes (8 Controls) Is the combobox sequence 3 - 8 or is ComboBox5 missing & it's 3 - 9 (5 excluded) ?

Dave

 

[ipbr21054]

Sorry been a real bad day.

Here we go

TextBox1, TextBox2, ComboBox1, ComboBox2, ComboBox3, ComboBox4, ComboBox5 ,ComboBox6

See screenshot supplied.

So i will type in TextBox1 & 2 BUT then select from a drop down for ComboBox1 through to 6
I will then insert a row and paste the userform values to row 8

So each time moving the names down the list.
Userform will always paste value so they end up in row 8

Thanks

 

[dmt32]

if you can, rename your Comboxes "ComboBox3", "ComboBox4" etc

and then modify the code portions shown in bold

Dim ControlsArr(1 To 8) As Variant

For i = 1 To 8
        If i > 2 Then
            With Me.Controls("ComboBox" & i)
                If .ListIndex = -1 Then
                    MsgBox "MUST SELECT ALL OPTIONS", 48, "MC LIST TRANSFER"
                    .SetFocus
                    Exit Sub
                Else
                    ControlsArr(i) = .Value
                End If
            End With
        Else
            ControlsArr(i) = Me.Controls("TextBox" & i).Value
        End If
    Next i

Dave

 

[ipbr21054]

I dont understand what you mean by rename them ?
If i right click on them and select properties they are as you mention

 

[ipbr21054]

I have this at present

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim x As Long
    Dim ControlsArr(1 To 8) As Variant
    
    
    For i = 1 To 8
        If i > 2 Then
            With Me.Controls("ComboBox" & i)
                If .ListIndex = -1 Then
                    MsgBox "MUST SELECT ALL OPTIONS", 48, "MC LIST TRANSFER"
                    .SetFocus
                    Exit Sub
                Else
                    ControlsArr(i) = .Value
                End If
            End With
        Else
            ControlsArr(i) = Me.Controls("TextBox" & i).Value
        End If
    Next i
        
        Application.ScreenUpdating = False
        
        With ThisWorkbook.Worksheets("MC LIST")
            .Range("A8").EntireRow.Insert Shift:=xlDown
            .Range("A8:I8").Borders.Weight = xlThin
            .Cells(4, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
            
            If .AutoFilterMode Then .AutoFilterMode = False
            x = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A7:I" & x).Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlGuess
            .Range("A8").Select
        End With
        
        ActiveWorkbook.Save
        Application.ScreenUpdating = True
        MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
        Unload Me
End Sub

 

[dmt32]

I dont understand what you mean by rename them ?
If i right click on them and select properties they are as you mention

Select each combobox & just change its name in the properties so that they run in sequence 3 to 8

 

[ipbr21054]

ok
done that and now it transfers values to worksheet but in row 4

I thought by changing .Cells(4, 1 to 8, 1 would be the answer but it then put values in row 12