Error message for missing data in userform

[kalbano]

I have an Excel Userform with option buttons, textboxes, comboboxes, and command buttons. I have written this code to check for missing data:

Private Sub cmdbutMU_Click()
Dim cCont As Control
'FILL ALL MANDATORY CONTROLS TAG PROPERTIES WITH "MUST"
For Each cCont In Me.Controls

If UCase(cCont.Tag) = "MUST" Then
If cCont = vbNullString Then
MsgBox "Missing Data in " & cCont.name
cCont.SetFocus
Exit Sub
End If
End If
Next cCont

End Sub

The problem is that cCont.name is often a dorky name such as txtbxPtName. I would rather use the name of the label just to the left of the control that is missing data but do not know how to write it.

Thank you anyone who can help!

[GTO]

Presuming you don't have a plethora of controls, some consistency in naming and use of the Like operator could work.

In a userform w/three textboxes and three labels...


Option Explicit
Private Sub cmdCheck_Click()
Dim cCont As Control
For Each cCont In Me.Controls

If UCase(cCont.Tag) Like "MUST*" _
And cCont = vbNullString Then

MsgBox "Missing Data in " & Me.Controls("lbl" & Right(cCont.Tag, 5))
cCont.SetFocus
Exit Sub
End If
Next cCont

Unload Me
End Sub
Private Sub UserForm_Initialize()

With Me
.txtFName.Tag = "MUST_FName"
.txtMName.Tag = "MUST_MName"
.txtLName.Tag = "MUST_LName"
.lblFName.Caption = "First Name"
.lblMName.Caption = "Middle Name"
.lblLName.Caption = "Last Name"
End With

End Sub


Does that helps?

Mark

[kalbano]

Dear GTO,

I am getting a "run time error 424 object required" message when I try to adapt your suggestion to my project. I have not put the label captions in the userform initialize procedure as you did since I have already put them in the the properties windows so that they show when I am in design mode. I also put the tags in the porperties window instead of the initialize procedure as well.
I used your example and I found that if I put the tags and the captions in the properties windows and eliminated the initialize userform code I did not get the run time error. However the userform does not unload correctly. Once every textbox is filled the program goes to design mode. I don't understand why it does this.

[Kenneth Hobs]

Sometimes when you are designing and testing from VBE's play button, you will need to close the file and reopen it to fix that sort of problem. Safe testing is not done in VBE.

What I do is to use suffix or prefix naming convention. e.g. tb_Unit1, lbl_Unit1. I can then modify the other control because I know the name.

I sometimes make control arrays and then just iterate a specific array of controls.

e.g.
Private cTB() As msforms.Control
Private cLBL() As msforms.Control

Private Sub UserForm_Initialize()
'Set the control array cTB() to contain all textbox controls with a Tag<>""
CreateControlArrays
End Sub

Private Sub CreateControlArrays()
Dim iTB As Integer, c As msforms.Control
Dim iLBL As Integer

For Each c In frmEntry.Controls
Select Case True
Case TypeName(c) = "TextBox" And c.Tag <> ""
iTB = iTB + 1
ReDim Preserve cTB(1 To iTB)
Set cTB(iTB) = c
Case TypeName(c) = "Label" And Left(c.Name, 8) = "lbl_Unit"
iLBL = iLBL + 1
ReDim Preserve cLBL(1 To iLBL)
Set cLBL(iLBL) = c
'Case TypeName(c)="ComboBox"
End Select
Next c
End Sub
An example where I used this may be found in this thread. http://vbaexpress.com/forum/showthread.php?t=26955

[kalbano]

Dear GTO,

Please ignore second paragraph in my last message. Your example does work if I put the captions and tags in the properties windows rather than the initialize procedure. It was because I was running it from the visual basic window rather than the excel worksheet.

[kalbano]

Dear Mr. Hobs,

What do you mean by the "VE play button"? Thanks for the example. I will give it a try.

[kalbano]

Dear GTO,

I finally made it work! Thanks so much. I just redid all my tags and labelnames. Something was not quite right though I do not know what.