Generic way for controlling forms on an Excel UserForm 3

[kjv1611]

I have the following code:

Private Sub cmdOk_Click()
    Dim frm As UserForm
    Set frm = frmBatchAudit
    Dim ctl As Control
    Dim CR As String
    CR = Chr$(13)

    For Each ctl In frm.Controls
        If ctl = vbNullString Then
        Else
            MsgBox "Please Enter Batch Id's in the" & CR & _
                "appropriate fields, or hit <Cancel>" & CR & _
                "to exit form."
            Exit Sub
        End If
    Next ctl
End Sub

I have some totally unrelated code in the same procedure, but it runs fine when this code is commented out. What happens, is that when the user hits the OK button on the form, the message box I have coded above comes up, and the vb code is exited, whether there is data on the form or not. I think it has something to do with my variable for the Controls, b/c that has worked fine in Access VBA, but there must be some other way to do it with Excel, b/c I think it is assuming that I mean for each control on the Excel worksheet, and not for each control on the UserForm. The code is located in the Uswer form module.

Any advice/help here would be greatly appreciated.


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[TonyJollans]

Hi Stephen,

I don't see how this can work. You check all the controls on your form and one of them, by definition, is a command button which you have just clicked and I don't think that can ever be equal to vbnullstring (I did expect it to be True in the click event but it is reporting as False).

Not sure what you mean about Access - Access Forms are quite different from Excel UserForms.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[kjv1611]

Well, in Access I use some code (that I've picked up from a couple different places in here) that loops through all of the controls. I did not take into consideration the TypeOf statement for this though, so that could be my problem. But what I'm talking about is like this: Say in Access you have a form, call it Form1, and you want to loop through all the text boxes.. so you would do something like this:

Private Sub CheckForNoValues()
  Dim ctl As Control
  Dim frm as Form
  Set frm = Forms!Form1
  For Each ctl in frm.Controls
    If TypeOf ctl Is vbTextBox Then
      [green]'Do nothing, just loop through[/green]
      If ctl = vbNullString Then
        MsgBox "Error Message"
        Exit Sub
      End If
    End If
  Next ctl
End Sub

Basically, I thought it would work the same, but I left out the TypeOf statement. Let me try that and post back, but I still think that it is looking for controls on the worksheet/workbook instead of the user form.. I'll post back with what I find..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Ok, now it seems adding the TypeOf statement may have fixed it, but I need to do some more testing. I think I have my logic all whacky! Basically, all I want to do is to tell the user to enter some data in the fields if they are blank, or hit the Cancel button to not run the operation. Any advice on that type of logic would be appreciated, though I'm sure I can crack it out of my thick skull once I get it cranked. I'm not that old, myself, yet, but sometimes it's like trying to crank an old clunker of a car to get my brain to think 100% logically, it seems!

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

A start to Tony Jollans for at least getting me to think about the TypeOf statement!

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Well, an update:
It seems the TypeOf command does not work correctly. I changed the code to this:

    For Each ctl In frm.Controls
        If TypeOf ctl Is CommandButton Then
            Debug.Print "CommandButton " & ctl.Name
        End If
        If TypeOf ctl Is TextBox Then
            Debug.Print "Text Box " & ctl.Name
            If ctl = vbNullString Then
            Else
                MsgBox "Please Enter Batch Id's in the" & CR & _
                    "appropriate fields, or hit <Cancel>" & CR & _
                    "to exit form."
                Exit Sub
            End If
        End If
    Next ctl

And now, it will not enter either of the conditional statements, though if I hold my cursor over the ctl variable when it is a textbox, it shows the correct value. Any ideas what I'm missing in the TypeOf part?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[TonyJollans]

Hi Stephen,

On a UserForm you are much better off using TypeName. The TypeOf is something like MSForms!Textbox

[blue]If TypeName(ctl) = "CommandButton" Then ...[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[fumei]

You may want to think it out a bit better as well. I don't know how many textbox controls you have, but it seems to me you are going to get the message for every one that is empty.

Would it not be better to determine if ANY are empty and give ONE message; possibly setting focus on the first control that is empty?

How many are there? Are all of them required to be empty? Particular ones?

Gerry

 

[Crystalyzer]

Stephen,

The problem you described

What happens, is that when the user hits the OK button on the form, the message box I have coded above comes up, and the vb code is exited, whether there is data on the form or not.

occurs because your IF statement isn't properly sequenced and is missing some code.

YOUR CODE
************************** 
If ctl = vbNullString Then
Else
   MsgBox "Please Enter Batch Id's in the" & CR & _
      "appropriate fields, or hit <Cancel>" & CR & _
      "to exit form."
   Exit Sub
End If

REVISED CODE
**************************
If ctl = vbNullString Then
   MsgBox "Please Enter Batch Id's in the" & CR & _
      "appropriate fields, or hit <Cancel>" & CR & _
      "to exit form."
   Exit Sub
Else
{steps to take if condition is NOT met}
End If

Hope this helps

Lloyd

 

[TonyJollans]

Hi Lloyd,

There is nothing structurally wrong with Stephen's IF statement. It says ..

IF (conditon is met) THEN
(do nothing)
ELSE
(do something)
ENDIF

It is a fairly common construct which avoids using negative conditions.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[kjv1611]

TonyJollans, thanks for the advice on the last part:
If TypeName(ctl) = "CommandButton" Then ...

That fixed the type problem, then I corrected my logic, so it works perfectly now.. I tried to post back yesterday, but we were having internet connection problems here at work, so it never went through. Anyway, here is the finished code for that part:

Private Sub cmdOk_Click()
    Dim frm As UserForm
    Set frm = frmBatchAudit
    Dim ctl As Control
    Dim CR As String
    CR = Chr$(13)
    Dim x As Integer
    x = 0

    For Each ctl In frm.Controls
        If TypeName(ctl) = "TextBox" Then
            If ctl = vbNullString Then
                x = x + 1
            Else
                Exit For
            End If
        End If
        If x = 12 Then
            MsgBox "Please Enter Batch Id's in the" & CR & _
                "appropriate fields, or hit <Cancel>" & CR & _
                "to exit form."
            Exit Sub
        End If
    Next ctl
End Sub

Thanks again! And thanks to all else who posted in here after TonyJollans, before I was able to post back, I will read the other posts today after this, as I dare not turn aside an opportunity to learn something more.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

TonyJollans, on the last post you put (which was not my sollution, but a correction elsewhere) is very true. One thing I learned in a little 3 day course in SQL coding at a New Horizons school in my area is that using a Not() statement tends to slow down code. So, I figure, that an easy way to get around that is to just use If/Then/Else or ElseIf statements. Thanks for your note there which would more or less put a little more proof to my theory.
I knew I had the If part right, but my logic was terribly lacking. With the new code, if at least one text box is filled in, no message, and the code runs, but if ALL text boxes were left empty, then the error message pops up. It looked right to me, until I stepped through it, and found, hey, no wonder it's been giving me the message regardless!

Anyway, thanks again to all who posted!

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[Crystalyzer]

Tony & Stephen,

Thanks for setting me straight. Star for each of you!

Lloyd