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]

Row 4 was based on code you posted

.Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr

changing value shown in bold should post to correct row

Dave

 

[ipbr21054]

I dont understand what you wrote,you have used the wrong code to highlite it.
I changed it to 8 but it puts it in row 12 not row 8

 

[dmt32]

Your code sorts the data after record has been posted to worksheet - could this be reason your record ends up in row 12?
you can check this by commenting out the sort line in the code & see if new record remains in Row 8

'.Range("A7:I" & x).Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlGuess

If still having issues place copy of your workbook in dropbox - myself or another maybe can then have closer look at your project

Dave

 

[ipbr21054]

Many thaniks with your help its now sorted.