VBA Userform Stop submission if combobox is not selected

[nelsonsix]

Hi,

I have about 20 users each with there own userform spreadsheet, on submission the data is fed to a separate 'database' spreadsheet. At present, there is a check box which has to be clicked or the submit button is disabled.

The problem I am finding is the users are submitting the forms without choosing an option within the combo box which is messing up all my data.

Is it possible to disable the submit button until a choice is made from the combo box as well as the 'completed' check box being ticked?

Hope this makes sense...code below:

Option Base 1
Private Sub cboPara_Change()
End Sub
Private Sub chkCompleted_Click()
    With Me.chkCompleted
    Me.cmdAdd.Enabled = .Value
    Me.lblCompleted.ForeColor = IIf(.Value, RGB(0, 0, 0), RGB(255, 0, 0))
    End With
End Sub

Private Sub cmdAdd_Click()
    Dim lRow As Long
    Dim FileName As String
    Dim msg As Variant
    Dim DatabaseOpenPassword As String, wsDatabasePassword As String
    Dim wbReportDatabase As Workbook
    
    
'********************************************************************************************
'*******************************************SETTINGS*****************************************
    FileName = ThisWorkbook.Worksheets("Settings").Range("F10").Text
    
    DatabaseOpenPassword = ""
    
    wsDatabasePassword = ""
    
'********************************************************************************************
    
    
    On Error GoTo exitsub

'check file exists
        If Not Dir(FileName, vbDirectory) = vbNullString Then
        Application.ScreenUpdating = False
'open database
            Set wbReportDatabase = Workbooks.Open(FileName, UpdateLinks:=False, _
                                                  ReadOnly:=False, Password:=DatabaseOpenPassword, _
                                                  IgnoreReadonlyRecommended:=True)
    
                With wbReportDatabase
                    
                    With .Worksheets(1)
                      .Unprotect Password:=wsDatabasePassword
'find first empty row in database
                        lRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _
                        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'post form data to the database
                        .Cells(lRow, 1).Resize(, 11).Value = Array(Me.cboPara.Value, Me.txtClient.Value, Me.cboReport.Value, _
                                                                  Me.chkCashflow.Value, Me.chkIncome.Value, Me.chkInvestment.Value, Me.chkPension.Value, Me.txtCeding.Value, _
                                                                  Me.chkAQ.Value, Me.chkCompleted.Value, _
                                                                  DateValue(Me.txtDate.Value))
'protect database
                    If Len(wsDatabasePassword) > 0 Then .Protect Password:=wsDatabasePassword
                    End With
'save & close file
                    .Close True

                End With
'save success
                    msg = Array("Record Saved To Database", "Record Saved")
'reset form
                    ResetControls
                
            Else
'file / folder not found
                msg = Array(FileName & Chr(10) & "File Not Found", "Not Found")
                
            End If
'release object variable
        Set wbReportDatabase = Nothing
        
exitsub:
'ensure database workbook closed
        If Not wbReportDatabase Is Nothing Then wbReportDatabase.Close False
'refresh screen
        Application.ScreenUpdating = True

        If Err > 0 Then
'report errors
            MsgBox (Error(Err)), 48, "Error"
        Else
'inform user
            MsgBox msg(1), 48, msg(2)
        End If
        
        
        
End Sub
Private Sub cboReport_Click()
    
    'EAR
    If cboReport.Value = "EAR" Then
        chkInvestment.Enabled = False
        chkPension.Enabled = False
    Else
        chkInvestment.Enabled = True
        chkPension.Enabled = True
    End If

    'Db Report & Cashflow
    If cboReport.Value = "DB Report" Or cboReport.Value = "Cashflow" Or cboReport.Value = "Cashflow Update/LFR" Then
        chkCashflow.Enabled = False
    Else
        chkCashflow.Enabled = True
    End If
     
    'Income
    If cboReport.Value = "Income Report" Then
        chkIncome.Enabled = False
    Else
        chkIncome.Enabled = True
    End If
    
End Sub

Sub ResetControls()
'clear the data
Me.cboPara.Value = "Neil Fryer"
Me.txtClient.Value = ""
Me.cboReport.Value = ""
Me.chkCashflow.Value = False
Me.chkIncome.Value = False
Me.chkInvestment.Value = False
Me.chkPension.Value = False
Me.txtCeding.Value = ""
Me.chkAQ.Value = False
Me.chkCompleted.Value = False
chkCashflow.Enabled = True
chkIncome.Enabled = True
chkInvestment.Enabled = True
chkPension.Enabled = True
Me.cboPara.SetFocus

txtDate = Format(Date, "dd mmmm yyyy")
End Sub

Private Sub txtDate_AfterUpdate()
    With Me.cmdAdd
    If Not IsDate(Me.txtDate.Text) Then
        .Enabled = False
        MsgBox "Invalid Date Entry", 16, "Invalid Date"
    Else
        .Enabled = True
    End If
    End With
End Sub

Private Sub UserForm_Initialize()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("LookupSheet")

    Me.cboReport.List = ws.Range("ReportType").Value

    txtDate = Format(Date, "dd mmmm yyyy")
    
    Call chkCompleted_Click

End Sub

Private Sub cmdClose_Click()
    Unload Me
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
End Sub

 

[davesexcel]

Something in the lines of this code, if nothing is selected in the combobox and if the checkbox is not check as well.

Private Sub CommandButton1_Click()
    If Me.ComboBox1 = "" Or Me.CheckBox1 <> True Then
        MsgBox "You need to do something else"
    Else
        MsgBox "Doing it"
    End If
End Sub

 

[nelsonsix]

Hi,

Thanks for your reply.

I have actually changed my approach with this. Im getting rid of the checkbox requirement all together.

Is there anyway to enable the submit button based solely on a combo box having a value?

Thanks
Neil

 

[davesexcel]

Would you not just remove the part about the checkbox?

Private Sub CommandButton1_Click()
    If Me.ComboBox1 = "" Then
        MsgBox "You need to do something else"
    Else
        MsgBox "Doing it"
    End If
End Sub