Use of SetFocus to Validate Data

[Don]

I have a relatively simple Form with some textboxes and
comboboxes. All I simply want to do is that when a user
clicks on "OK" that the procedure verify every field has
data. If every field is populated then the procedure runs
flawlessly.

OTOH, if a field is left blank, I want a msgbox to pop up
that tells the user what fields need to be populated and
outline the control in red. (Also works great!) But
then... I want the focus to go to the FIRST EMPTY conrol
(which I am having a difficult time with) The following
code gives me an object required error.

Can anyone tell me where I am going wrong? Thanks so much
in advance.

Don

Dim Control
Dim FirstEmptyControl
Dim x As Integer
Dim strnames As String

On Error GoTo HandleError

x = 0

For Each Control In Me.Controls
If TypeOf Control Is MsForms.ComboBox Or TypeOf
Control Is MsForms.TextBox Then
If x = 0 Then FirstEmptyControl = Control.Name

If Control.Value = "" Or IsNull(Control.Value)
= True Then
strnames = strnames & Control.Tag & vbCrLf
Control.BorderStyle = 1
Control.BorderColor = vbRed
x = 1
Else
Control.BorderStyle = 0
Control.SpecialEffect = 2
End If
End If
Next

If x = 1 Then
MsgBox "You Must Populate the Following Fields: "
& vbCrLf & vbCrLf & strnames
FirstEmptyControl.SetFocus "ERROR IS HERE????
Exit Sub
End If

 

[Jay Freedman]

Hi, Don,

The cause of the error message is that FirstEmptyControl is just a string
(well, more accurately, a Variant of subtype String), where the .SetFocus
method has to apply to an object of type Control or something specialized
from Control. You can use the FirstEmptyControl string as a sort of "index"
into the Controls collection like this:

Me.Controls(FirstEmptyControl).SetFocus

Your code could benefit greatly from declaring *every* variable as having a
specific data type, rather than letting any of them default to Variant. I'd
als recommend using a Boolean (True/False) variable in place of the integer
x. I've verified that this version of your code works:

Private Sub CommandButton1_Click()
Dim MyControl As Control
Dim FirstEmptyControl As String
Dim bHasEmptyControl As Boolean
Dim strnames As String

On Error GoTo HandleError

bHasEmptyControl = False

For Each MyControl In Me.Controls
If TypeOf MyControl Is MsForms.ComboBox Or _
TypeOf MyControl Is MsForms.TextBox Then
With MyControl
If Not bHasEmptyControl Then FirstEmptyControl = .Name

If .Value = "" Or IsNull(.Value) = True Then
strnames = strnames & .Tag & vbCrLf
.BorderStyle = 1
.BorderColor = vbRed
bHasEmptyControl = True
Else
.BorderStyle = 0
.SpecialEffect = 2
End If
End With
End If
Next

If bHasEmptyControl Then
MsgBox "You Must Populate the Following Fields: " & vbCrLf & vbCrLf
& strnames
Me.Controls(FirstEmptyControl).SetFocus
Exit Sub
End If

Me.Hide
Exit Sub

HandleError:
MsgBox Err.Description, , "Error"
End Sub