Forcing userform text boxes to be filled

scoha

Active Member
Joined
Jun 15, 2005
Messages
428
I have a user form with a number of text boxes that are mandatory and have come up with some code that I thought was a neat way of checking these mandatory boxes for a value before the user form is advanced to the next record - but I gey a type mismatch error and cannot understand why - any one help me?:

Mandatory text boxes are txtTot, txtAddress1, txtCon etc


Code:
Private Function CheckComplete()

Dim cCont As Control

    For Each cCont In Me.Controls
           If cCont.Name = "txtTot" Or "txtAddress1" Or "txtCon" Then
            If cCont.Value = "" Then MsgBox "You need to fill in " & cCont.Name
            End If
     Next cCont

End Function
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Try

Code:
Sub CheckComplete()

    Dim cCont As Control

    For Each cCont In Me.Controls
        If TypeName(cCont) = "TextBox" Then
            If cCont.Text = "" Then MsgBox "You need to fill in " & cCont.Name: cCont.SetFocus: Exit Sub
        End If
    Next cCont

End Sub
Regards
Northwolves
 
Upvote 0
Try this.
Code:
Private Sub CheckComplete()
Dim cCont As Control
    For Each cCont In Me.Controls
           Select Case cCont.Name
                Case "txtTot", "txtAddress1", "txtCon"
                    If cCont.Value = "" Then MsgBox "You need to fill in " & cCont.Name
                Case Else
                    'do nothing
            End Select
     Next cCont
End Sub
 
Upvote 0
The line
Code:
If cCont.Name = "txtTot" Or "txtAddress1" Or "txtCon" Then
is causing the type mismatch error

OR is a operation on two Boolean values and the string "txtAddress1" is not Boolean.

Nothwolves' solution avoids the clumbsy expression
Code:
If (cCont.Name = "txtTot") Or (cCont.Name  = "txtAddress1") Or (cCont.Name = "txtCon") Then
While Norie's handles the situation where the UF has more than those three textboxes.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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