verifying text boxes arent left blank in user forms

[VBAmalta89]

I have a user form which consists of a drop box and 3 text boxes.

As a sort of data validation when clicking the OK button on the user form I need the following:

If the user selects an item from the drop box then each of the text boxes must be filled with a value, otherwise a message is prompted when pressing OK and doesnt allow the user to continue.

(The drop boxes and text boxes are within a frame - dont know if that makes a difference)

How can i code this as I'm new to VBA and having trouble doing this?

 

[Airfix9]

I tend to code this in the text boxes themselves, like this:

Private Sub TextBox1_Change()
    If TextBox1.Value <> vbNullString Then
        If TextBox2.Value = vbNullString Or TextBox3.Value = vbNullString Then
            cmdOK.Enabled = False
        Else
            cmdOK.Enabled = True
        End If
    Else
        cmdOK.Enabled = False
    End If
End Sub

Edit to suit textbox and command button names and you'll need to do the same for each text box, editing as appropriate in each case.

Finally, set cmdOK to enabled = false in the properties window so that the form is loaded with the button disabled. This way, you control when the user can click OK.

Hope that helps.

 

[VBAmalta89]

sorry but it doesnt seem to be working for me as the command button is remaining disabled after doing exactly what you told me

any idea why this is happening?

 

[Airfix9]

Have you edited everything (including the macro name) to reflect the text box / command button names that you have used?

 

[VBAmalta89]

yep thats why i cant understand why its not working...i tried playing around with the code a bit as well but it still isnt working.

is there any other method that i can use?

 

[Airfix9]

Without seeing the workbook, it's hard to comment. You can PM me for my email address if you can send it to me.

 

[Airfix9]

Solved.

For completeness, this was solved by adding a loop to the command button click event as follows.

There were 6 combo boxes and each had one combo box and two text boxes which must be filled out to be compliant. Handily, VBAmalta89 had named these controls with a logical name structure (always a bonus) so I could loop round the collection of controls, creating an error message where I found a non-compliant set. The form was more complex than this code indicates but I have edited it down for simplicity:

Private Sub AddPrt_Click()
' new code

    Dim MyControlNumber As Integer
    Dim MyErrorMessage As String
    MyErrorMessage = vbNullString
    
    
    For MyControlNumber = 1 To 6
        
        If Me.Controls("proc" & MyControlNumber).Value <> vbNullString Then
            If Me.Controls("ZoneSelect" & MyControlNumber).Value = vbNullString Or _
                    Me.Controls("time" & MyControlNumber).Value = vbNullString Or _
                    Me.Controls("power" & MyControlNumber).Value = vbNullString Then
                If MyErrorMessage = vbNullString Then
                    MyErrorMessage = "Please ensure that you have completed the 'Process in Zone', 'Time for Process' and 'Power Rating' for the following processes:" & vbCrLf & Chr(9) & "- Factory 1, process " & MyControlNumber
                Else
                    MyErrorMessage = MyErrorMessage & vbCrLf & Chr(9) & "- Factory 1, process " & MyControlNumber
                End If
            End If
        End If
    Next MyControlNumber
    
    
    'now we cut out if the error message contains anything
    
    If MyErrorMessage <> vbNullString Then
        
        MsgBox MyErrorMessage, vbCritical, "The form is incomplete"
        Exit Sub
        
    End If

    'REST OF CODE ALREADY IN PLACE
End Sub