Can you advise a portion of my code to delete please

[ipbr21054]

Morning,
I am using the working code below.
Currently when the command button is pressed the code checks to make sure each textbox etc has a value then transfers the values to my worksheet or show the msgbox message.

I would like to remove the check option.
I no longer need to check if all textboxes etc are complete.
On some textboxes now a few can be left empty but im not sure which portion needs to be deleted.

Am i correct to just delete the code in Red ?

Private Sub CommandButton1_Click()
If Len(Me.TextBox2.Value) = 17 Then
    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 "YOU MUST COMPLETE ALL FIELDS", vbCritical, "MC LIST TRANSFER"
                    TextBox1.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(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
            .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = -16776961
            .Cells(8, 9).Value = GetYear(Mid(.Cells(8, 2).Value, 10, 1))

        End With
        Range("B8").Select
        Range("A8").Select
        ActiveWorkbook.Save

        Application.ScreenUpdating = True
        MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
       
       With ThisWorkbook.Worksheets("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
            .Range("B8").Select
            .Range("A8").Select
        End With
       
        Unload McListForm
    Else
        MsgBox "VIN MUST BE 17 CHARACTERS" & vbCr & vbCr & "DATABASE WAS NOT UPDATED", vbCritical, "MC LIST TRANSFER"
        TextBox2.SetFocus
    End If
End Sub

 

[dmt32]

Hi,
the test for completed controls are with the Comboboxes only in your code - the two textboxes are not tested?

If this is what you mean then change this portion of your code

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

To this

For i = 1 To 8
        If i > 2 Then
'comboboxes
            ControlsArr(i) = Me.Controls("ComboBox" & i).Value
            
        Else
'textboxes
            ControlsArr(i) = Me.Controls("TextBox" & i).Value
        End If
    Next i

and see if does what you want

Dave

 

[ipbr21054]

Hi,
I was able to complete 1 field and transfer fine without the message advising me, Thanks.

Can you advise briefly what the code is being told after the piece If i, 2 Then

 

[dmt32]

This line in your For Next Loop

If i > 2 Then

simply means that any value above 2 is referring to a Combobox control on your form e.g. Combobox3, Combobox4 etc

Dave