ComboBox must have a value before form submission

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,284
Office Version
  1. 2007
Platform
  1. Windows
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Hi,
Thanks but it still will not submit even though all the comboboxes have values selected.
 
Upvote 0

Forum statistics

Threads
1,217,433
Messages
6,136,598
Members
450,021
Latest member
Jlopez0320

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top