MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Avoid multiple select constructs

  Asked By: Jodon    Date: Sep 04    Category: MS Office    Views: 1355

I have these lines that checks the TextBox value, total of 4. I want
to avoid writing this 4 times by using a loop, coz Im a newbie, any
good sumaritan out there,

Select Case Me.TextBox1.Value

Case Is < 2
Me.TextBox1.BackColor = &H80FF80

Case 2 To 4
Me.TextBox1.BackColor = &H80FFFF

Case Is > 4
Me.TextBox1.BackColor = &HFF
End Select



2 Answers Found

Answer #1    Answered By: Jake Williams     Answered On: Sep 04

Your decision logic using the SelectCase function is easier to debug or

If you insist on less lines  of code; see if this gets you what you are looking

vValue = Me.TextBox1.Value
vLogicSum = 3 + (((vValue < 2)*1) + ((vValue<=4)*1))
Me.TextBox1.BackColor = Choose ( vLogicSum, &H80FF80, &H80FFFF, &HFF )

You can take it 1 step further by replacing the vLogicSum variable in line3 with
its expression from line2; then removing line 2.

As you can see, the original code is simpler to debug than the solution

Answer #2    Answered By: Muriel Dunn     Answered On: Sep 04

You're may want to go through the Form Controls:

For Each cntl In MyForm.Controls
If InStr(cntl.Name, "TextBox") Then
Select Case cntl.Value
Case Is < 2

End Select

End If

This assumes that you want to do this with all TextBoxes in the Form,
and that they've got "TextBox" as part of their name.

There is also a TypeName(cntl) = "TextBox" construct, but I've not had
consistent results with it. It could be because I don't know how to
use it.

Didn't find what you were looking for? Find more on Avoid multiple select constructs Or get search suggestion and latest updates.