ComboBox must have a value before form submission

[ipbr21054]

Morning,

I am using the code shown below but need some advice please.
There is nothing to stop me pressing the CommandButton and saving blank info to the worksheet cells.

It would be nice that each ComboBox MUST have a value in it BEFORE it can be sent to the worksheet,maybe in the way of an msg box & advising you which ComboBox it is refering to.

Example for the ComboBox with chip selection,
"Please select a chip"


Thanks

Private Sub CommandButton1_Click()


    With Sheets("CHIPS")


     Sheets("CHIPS").Range("D4").Select
     ActiveCell.EntireRow.Insert Shift:=xlDown
     Sheets("CHIPS").Range("D4:I4").Select
     Selection.Borders.Weight = xlThin
     Sheets("CHIPS").Range("D4").Select
     
    .Range("D4").Value = Me.ComboBox1.Text
    .Range("E4").Value = Me.ComboBox2.Text
    .Range("F4").Value = Me.ComboBox3.Text
    .Range("G4").Value = Me.TextBox1.Text
    .Range("H4").Value = Me.TextBox2.Text
    .Range("I4").Value = Me.ComboBox4.Text
    End With


Dim ctrl As MSForms.Control
 
For Each ctrl In Me.Controls
    Select Case True
                Case TypeOf ctrl Is MSForms.TextBox
            ctrl.Value = ""
        Case TypeOf ctrl Is MSForms.combobox
            ctrl.Value = ""
    End Select
    
Next ctrl
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    
    ComboBox1.SetFocus


End Sub

 

[dmt32]

Hi,
untested but see if this update helps

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim ControlsArr As Variant
    
    For i = 1 To 4
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "Entry Required", 48, "Entry Required"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
        
    
    ControlsArr = Array(Me.ComboBox1, Me.ComboBox2, Me.ComboBox3, Me.TextBox1, Me.TextBox2, Me.ComboBox4)
    
    With ThisWorkbook.Worksheets("CHIPS")
        .Range("D4").EntireRow.Insert Shift:=xlDown
        .Range("D4:I4").Borders.Weight = xlThin
        .Range("D4:I4").Value = ControlsArr
    End With
    
    For Each ctrl In ControlsArr
        ctrl.Text = ""
    Next




    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    
    Me.ComboBox1.SetFocus


End Sub

Dave

 

[ipbr21054]

Thanks,
I have edited this line as all are ComboBoxes
ControlsArr = Array(Me.ComboBox1, Me.ComboBox2, Me.ComboBox3, Me.ComboBox4, Me.ComboBox5, Me.ComboBox6)

I have noticed that even when all the boxes are complete i still get the message as if one was empty.

 

[dmt32]

Hi,
solution was based on code you provided. If you add to the array, you will also need to amend other parts of the code

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    
    For i = 1 To 6
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "Entry Required", 48, "Entry Required"
                .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("CHIPS")
        .Range("D4").EntireRow.Insert Shift:=xlDown
        .Range("D4:I4").Borders.Weight = xlThin
        .Range("D4:I6").Value = ControlsArr
    End With
    
    For Each ctrl In ControlsArr
        ctrl.Text = ""
    Next


    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    
    Me.ComboBox1.SetFocus


End Sub

Dave

 

[ipbr21054]

Hi,
Thanks but it still will not submit even though all the comboboxes have values selected.

 

[ipbr21054]

I found my error & now working.

Thanks