Loop through Multiple Boolean Variables

ScottyBee · #1 03-19-2019, 05:57 PM

Hello,

I am getting a "type mismatch" error when looping through Boolean variables trying to identify any variables that have a value of FALSE.

My code resides in a Module and is below:

Code:

Public txt1Changed As Boolean
Public txt2Changed As Boolean
Public txt3Changed As Boolean
Public txt4Changed As Boolean
Public txt5Changed As Boolean
Public txt6Changed As Boolean
Public txt7Changed As Boolean
Public txt8Changed As Boolean
Public cbo1Changed As Boolean
Public cbo2Changed As Boolean
Public cbo3Changed As Boolean



Public Sub Test()
    txt1Changed = True
    txt2Changed = True
    txt3Changed = True
    txt4Changed = True
    txt5Changed = True
    txt6Changed = True
    txt7Changed = False
    txt8Changed = True
    Dim i As Integer
      For i = 1 To 8
            If ("txt" & CStr(i) & "Changed") = False Then
                MsgBox ("Textbox " & i & " has not been changed.")
                '.SetFocus
                Exit Sub
            End If
      Next i
End Sub

Once I get this to work, I will be applying this to a VBA form that validates that all fields on a form have been changed before allowing the user to submit the data.

Thanks for any help

Guessed · #2 03-19-2019, 06:58 PM

You can't dynamically convert a string to a variable.

Let's look at the underlying reason for attempting this code. Is it intended to augment or supercede the code in your other thread? If you want to run this as a test for changes, I would be comparing a before/after value for each of the text controls. How is the userform pre-populating the controls in order to determine whether a change has occurred? You could store initial values in hidden text boxes and do a comparison of initial to current
For example some aircode:

Code:

Sub UserForm_Initialize()
  Me.TextBox1 = "Initial value"
  Me.PreTextBox1 = "Initial value"  'each visible TextBox has a hidden partner prefilled with same initial content
End Sub
Sub UserForm_Validate()
  If Me.TextBox1 <> Me.PreTextBox1 then
    MsgBox "A change was detected"
  end if
End Sub

Handy Hint: Stick the number at the end of the control names so the vba strings are simpler to code

ScottyBee · #3 03-19-2019, 09:31 PM

Hello Guess,

Yes, my latest code was to augment the code you posted earlier shown here:

Private Sub btnOK_Click()
Dim i As Integer, aCtl As Control
For i = 1 To 8
With Me.Controls("TextBox" & i)
If .Value = "" Then
MsgBox "Nothing in this one"
.SetFocus
Exit Sub
Else
MsgBox .Name & vbCr & .Value
End If
End With
Next i
End Sub

I adapted it a little but it worked great in identifying null values and returning focus to the textbox control that needed to be entered.

My most recent post was trying to take the ideas you gave me to solve another issue. That issue involves checking that all textboxes have been changed from their default values. A snapshot of my user form is shown here:

User Form.JPG

I added your code to the "Check for Nulls" command button and it work like a charm. As you can see from the user form there are default values presented to the user. They are required to enter all fields. The code you provided catches when the user presses delete then tabs to the next field leaving the previous one blank.

Taking your idea, I tried to code the "Verify all Fields Updated" button to check for changes. In a module, I have declared public boolean variables to be used in the Change Event for each textbox control on the form. When the textbox is changed, then the code changes the boolean value to True.

I was trying to think of a way to loop all of the boolean variables like you did with the text objects but I guess that cannot be done. It looks like I will have to text them individually.

I did have a question about your code here:

Sub UserForm_Initialize()
Me.TextBox1 = "Initial value"
Me.PreTextBox1 = "Initial value" 'each visible TextBox has a hidden partner prefilled with same initial content
End Sub
Sub UserForm_Validate()
If Me.TextBox1 <> Me.PreTextBox1 then
MsgBox "A change was detected"
end if
End Sub

Questions:

1. Since I have 8 textboxes, I assume I will duplicate your code for each control and have the focus returned to the control that did not change. I also have 3 combo boxes but will code them later.

2. Looks like I have two approaches to this issue. Use your logic using hidden textboxes or use a change event for each control with code to test for any change? Is any better than the other?

3. A final validation for my form will involve checking the textbox values for a fixed number of numerical characters. Are you aware of any resources online going over VBA validation techniques for form texboxes or other controls?

This will be a project a week or two down the road.

Thanks much!

Guessed · #4 03-19-2019, 10:56 PM

Scotty

I usually try to make the controls as self-sufficient as possible. For instance, I would use the tag property of each text box to put the default values in. Then I could use that value to both fill the initial view and test for changes to default values. See the following code example

Code:

Private Sub UserForm_Initialize()
  Dim i As Integer, aCtl As Control
  For i = 1 To 8
    Set aCtl = Me.Controls("TextBox" & i)
    aCtl = aCtl.Tag
  Next i
End Sub

Private Sub UserForm_Click()
  Dim i As Integer, aCtl As Control
  For i = 1 To 8
    Set aCtl = Me.Controls("TextBox" & i)
    If aCtl = aCtl.Tag Then
      MsgBox aCtl.Name & " still has default value"
    ElseIf aCtl = "" Then
      MsgBox aCtl.Name & " is empty"
    End If
  Next i
End Sub

ScottyBee · #5 03-20-2019, 04:50 PM

Thanks Guessed, that code is much more efficient and easier to maintain.

ScottyBee · #6 03-22-2019, 04:37 PM

Hello Guessed and GMaxey,

I have implemented both of your ideas into my Validation process but am having problems with the focus being returned to my first texbox (txt1) if validation fails. Currently, the focus moves to my second textbox (txt2). I am using the TAB key to advance the cursor from textbox to textbox. Maybe it is the end of the week and I am missing something easy.

Here is my code for the Validate procedure I have created:

Code:

Sub Validate(ControlNumber As Integer)
Select Case ControlNumber
    Case 1
    'number only
    If Not IsNumeric(txt1.Text) Then
        MsgBox ("Must be a Number")
        txt1.SetFocus
    End If
    'Case 2,3,4,5,6,7,8 to follow
End Select
End Sub

Here is the code for the txt1 AfterUpdate event:

Code:

Private Sub txt1_AfterUpdate()
    txt1.BackColor = vbWhite
    Validate (1)
End Sub

If I run the form and type in a letter, the code detects the error and displays the message box as planned. However, when I click OK, the focus is not returned to the txt1 control. It moves to the txt2 control meaning the user could ignore the error.

How do I get the focus to return to txt1 and keep it there as many times as necessary until user puts in only numbers? Maybe I am using the incorrect event or missing something easy? Thanks

Guessed · #7 03-22-2019, 04:48 PM

There is no point having a separate validation macro if you are going to put all 8 cases there. You want the validate macro to be passed some variables so you can merge the processing

Code:

Private Sub txt1_AfterUpdate()
    Validate (Me.txt1, "Number")
End Sub

Sub Validate(aCtrl as control, sValidate as string)
  Select Case sValidate
    Case "Number"
      If Not IsNumeric(aCtrl.Text) Then
        MsgBox ("Must be a Number")
        aCtrl.SetFocus
      Else
        aCtrl.BackColor = vbWhite
      End If
    Case "Text"
    
  End Select
End Sub

gmaxey · #8 03-24-2019, 08:35 AM

Code:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  If Not fcnValidate(TextBox1, "Number") Then
    Cancel = True
  End If
End Sub

Function fcnValidate(oCtrl As Control, strValType As String) As Boolean
  fcnValidate = True
  Select Case strValType
    Case "Number"
      If Not IsNumeric(oCtrl.Text) Then
        MsgBox ("Must be a Number")
        With oCtrl
          .SetFocus
          .SelStart = 0
          .SelLength = Len(.Text)
        End With
        fcnValidate = False
      Else
        oCtrl.BackColor = vbWhite
      End If
    Case "Text"
  End Select
End Function

ScottyBee · #9 03-25-2019, 03:24 PM

The code works great and is scale able as I will have multiple validation requirements in future. Thanks for pointing me in the right direction Guessed and GMaxey. How do I mark this post as RESOLVED or give you guys credit in here? Thanks

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Generate template-based letter with multiple variables	shallaes	Word VBA	5	07-12-2018 12:18 AM
Multiple Problems with a loop	Radtastic10	Excel Programming	3	06-09-2017 09:38 AM
Testing multiple values of two independant variables and charting results	sgcannon	Excel	0	10-20-2015 10:10 AM
Array to iterate through variables and trap blank variables	Marrick13	Word VBA	5	08-04-2015 06:19 AM
Same values of variables in word add-in are being shared across multiple document	naq.abbas@gmail.com	Word	0	01-13-2014 10:41 AM