Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional BackColor Loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional BackColor Loop
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional BackColor Loop
Excellent. Thanks for the quick reply. Will try this tomorrow and post
results. Regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional BackColor Loop
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional BackColor Loop
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional BackColor Loop
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional BackColor Loop
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiselect listbox backcolor | Excel Programming | |||
Change BackColor | Excel Programming | |||
TextBox BackColor | Excel Programming | |||
Changing backcolor of commandbutton | Excel Programming | |||
BackColor property code | Excel Programming |