ComboBox must have a value before form submission

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
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

Code:
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,815
Office Version
2019
Platform
Windows
Hi,
untested but see if this update helps

Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,815
Office Version
2019
Platform
Windows
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

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

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
Hi,
Thanks but it still will not submit even though all the comboboxes have values selected.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,082
Messages
5,466,535
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top