Thread: Check Blank Textbox & Combobox on UserForm

I have spent several hours this afternoon trying to find an answer to what appears to be a simple solution to a competent user of VBA of which I am not.

I have a Userform which populates a spreadsheet using textboxes and comboboxes. If any of the textboxes or comboboxes are blank it should bring up a message box telling you to complete the blank elements and will not send the data across until all the fields on the attached form (except description) are complete.

Can somebody please not only supply me with the code but where I should put it in my existing code as this seems to be my biggest problem. Thanks.

Hi,

The place to make this check is in the Add Time Entry - Private Sub cmdAdd_Click() procedure. Make the checks the first instructions. e.g

Private Sub cmdAdd_Click()

    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("TimeData")

    If Me.TextDate.Value = "" Or Me.TextStaff.Value = "" Then
        MsgBox "Please ensure all fields are completed"
        Exit Sub
    End If

I've only shown a check on Date and Staff, but just add more 'Or' statements.

Rather than doing the check in VBA I always prefer to use regular Excel functions to do the checks. That's because it's easier to apply validation checks. So instead I would write the userform entries to a temporary range , usually a hidden row above the list of values that that you want to capture. I name this range say "NewRecord".

Then in say the row underneath you can use regular Excel IF() functions to test the entries, and if OK return a 0 otherwise a 1. In another working cell named say 'Check' sum the contents of the IF() tests and then in your macro you can just test for the Range called 'Check'

i.e.

IF Range("Check") <> 0 Then
 MsgBox "Please ensure all fields are completed"
        Exit Sub
End If

Regards

Hi,

The place to make this check is in the Add Time Entry - Private Sub cmdAdd_Click() procedure. Make the checks the first instructions. e.g

Private Sub cmdAdd_Click()

    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("TimeData")

    If Me.TextDate.Value = "" Or Me.TextStaff.Value = "" Then
        MsgBox "Please ensure all fields are completed"
        Exit Sub
    End If

I've only shown a check on Date and Staff, but just add more 'Or' statements.

Rather than doing the check in VBA I always prefer to use regular Excel functions to do the checks. That's because it's easier to apply validation checks. So instead I would write the userform entries to a temporary range , usually a hidden row above the list of values that that you want to capture. I name this range say "NewRecord".

Then in say the row underneath you can use regular Excel IF() functions to test the entries, and if OK return a 0 otherwise a 1. In another working cell named say 'Check' sum the contents of the IF() tests and then in your macro you can just test for the Range called 'Check'

i.e.

IF Range("Check") <> 0 Then
 MsgBox "Please ensure all fields are completed"
        Exit Sub
End If

Range("NewRecord").Copy Destination:= Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(1,0)

'etc....

Regards