Conditional BackColor Loop

[obsessive]

I have a userform with 5 textboxes (Sample1, Sample2, ...etc). I want
to ensure the user is aware of what he or she is entering to prevent
inaccuracies. My goal is to create a loop for the following script and
another script (or combined script) to prohibit entries if the value
entered is <= -10 and >= 10 the standard value.
This is the userform module I want to loop....

Private Sub ChkEditValues()
Dim lAnswer As Long


SampleVal = Val(Sample1.Text)
StandardVal = Val(Standard.Text)

'Checks values for plus or minus 3 and hilights backcolor if one of the
conditions are met'
If SampleVal <= (StandardVal - 3) Or SampleVal >= (StandardVal + 3)
Then
Sample1.BackColor = &HFFFF&
lAnswer = MsgBox("Check the Hilighted Entries. If OK press YES",
vbYesNo, "Double Check")
If lAnswer = vbNo Then
Exit Sub
Else
Sample1.BackColor = &H80000005
PutData
MsgBox "Edits have been entered"
End If
Else
Sample1.BackColor = &H80000005

End If

End Sub

 

[martin]

Something like this might work if your text boxes are consistently
named "SampleX" where X ={1, 2, 3, 4, }. Otherwise you can just write
"TextBoxes(1) = "Sample1", "TextBoxes(2) = Sample2", etc. You call this
subroutine with a value for Variance, e.g., 3 or 10 or whatever.

Private Sub ChkEditValues(Variance as Long)

Dim Answer, SampleVal, StandardVal as Long
Dim TextBoxes(5) as String
Dim MyMsg as String
Dim ErrFlag as Boolean

StandardVal = Val(Me.Controls("Standard").Text)

For Ctr = 1 to 5
TextBoxes(Ctr) = Me.Controls("Sample" & CStr(Ctr)).Name
Next

ErrFlag = False
For Ctr = 1 to 5
Set MyTB = Me.Controls(TextBoxes(Ctr))
SampleVal = Val(MyTB.Text)
If SampleVal <= StandardVal - Variance then ErrFlag = True
If SampleVal > StandardVal + Variance then ErrFlag = True
If ErrFlag Then
MyTB.BackColor = &HFFFF&
End If
Next
MyMsg = "Please check the highlighted entries. "
MyMsg = MyMsg & "Click YES if OK."
Answer = MsgBox(MyMsg, vbYesNo, "Double Check")
If Answer = vbYes then
For Ctr = 1 to 5
Set MyTB = Me.Controls(TextBoxes(Ctr))
MyTB.BackColor = &H80000005
Next
PutData
MsgBox "Edits have been entered"
End If

End Sub

 

[obsessive]

Excellent. Thanks for the quick reply. Will try this tomorrow and post
results.
Regards

 

[martin]

You're welcome. I didn't test it so there's probably bugs. I also
noticed the last third or so needed some reworking:

If ErrFlag then
MyMsg = "Please check the highlighted entries. "
MyMsg = MyMsg & "Click YES if OK."
Answer = MsgBox(MyMsg, vbYesNo, "Double Check")
If Answer = vbYes then
For Ctr = 1 to 5
Set MyTB = Me.Controls(TextBoxes(Ctr))
MyTB.BackColor = &H80000005
Next
Else
Exit Sub
End If
End If
PutData
MsgBox "Edits have been entered"

End Sub

 

[obsessive]

Thanks again for the code.
Am trying to work out a small bug however.
The current code highlights ALL the textboxes, not just the ones
outside the variance.
I am trying to only highlight the textboxes with values that are
outside the variance.
If they are "within spec" the backcolor should not be changed.
Will post again if/when I figure it out.
Regards

 

[martin]

you might hard-code the variance into the procedure to see whether
passing it as a parameter is causing the problem. I also noticed that
if you answer Yes to the error message, then put in a valid value, then
hit the OK button or whatever, the background stays yellow - not what
you wanted, I don't think, but simple to fix.

 

[obsessive]

This did it... thanks for your help...FYI...I prohibited entries with a
variance of 10 through the textbox exit function.. everything
works perfect (so far anyway)

Private Sub ChkEditValues(Variance As Long)


Dim Answer, SampleVal, StandardVal As Long
Dim TextBoxes(5) As String
Dim MyMsg As String
Dim ErrFlag As Boolean


StandardVal = Val(Me.Controls("Standard").Text)

For Ctr = 1 To 5
TextBoxes(Ctr) = Me.Controls("Sample" & CStr(Ctr)).Name
Next


ErrFlag = False
For Ctr = 1 To 5
Set MyTB = Me.Controls(TextBoxes(Ctr))
SampleVal = Val(MyTB.Text)
If SampleVal <= StandardVal - 3 Then MyTB.BackColor = &HFFFF&
If SampleVal >= StandardVal + 3 Then MyTB.BackColor = &HFFFF&
If MyTB.BackColor = &HFFFF& Then
ErrFlag = True
End If
Next
If ErrFlag Then
MyMsg = "Please check the highlighted entries. "
MyMsg = MyMsg & "Click YES if OK."
Answer = MsgBox(MyMsg, vbYesNo, "Double Check")
If Answer = vbYes Then
For Ctr = 1 To 5
Set MyTB = Me.Controls(TextBoxes(Ctr))
MyTB.BackColor = &H80000005
Next
Else
ErrFlag = False
Exit Sub
End If
End If
PutData
MsgBox "Edits have been entered"


End Sub