ComboBox must have a value before form submission

ipbr21054

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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. 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,935
Office Version
  1. 2007
Platform
  1. Windows
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
6,515
Office Version
  1. 2019
Platform
  1. 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,935
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Thanks but it still will not submit even though all the comboboxes have values selected.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
I found my error & now working.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,336
Members
417,021
Latest member
moon miner

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
Top