An Easier Way

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

Is there an easier way to code this? I have 41 Textboxes and I'd like to
use a variable or something so I don't have to write 41 select cases or if
statements.

Me.TxtDay = Format(Date,"d")

If CLng(D11) = Me.TxtDay Then
D11.Forecolor = vbRed
Else
End if

Thanks
DS
 
PS D1 to D41 shows the actual date (number) of the day, so it changes. I
just want it to turn red if it matches the days current day.
Thanks
DS
 
Hopefully you have a way of knowing what number is in each of the 41 text
boxes.

For example, if you know that 1 is in box 5, you could do something like:

Me.Controls("D" & Day(Date) + 4).ForeColor = vbRed

You may also want to loop through all 41 text boxes first, making sure their
Forecolor isn't red before you do this:

For intLoop = 1 To 41
Me.Controls("D" & intLoop).ForeColor = vbBlack
Next intLoop
 
Thanks Doug!
But is there another way other than using the Me.Controls it seems that
Me.Controls occasionaly gives me trouble when I move from one machine to the
next.
Thanks
DS
 
Have you looked at conditional formatting? Click the text box to select it,
then click Format > Conditional Formatting. Set it up so in the three boxes
you have:
Cell Value
Equal To
Date()

Then choose the formatting (such as red font, bold font, etc.).

I don't know where the number 1 to 41 is coming from. The format of the
field or of the text box is irrelevant, as long as it is a Date/Time field.
 
I've never had a problem using the Controls collection. What have you
experienced?

If you don't use the Controls collection like that, then you have no
alternative but to write 41 separate statements.
 
OK Doug I'm using the Me.Control thing and upon trail and error. I need the
value that is inside the "D" so is thsi OK,
Me.Controls("D" & Day(Date) .Value).ForeColor = vbRed

I guess not because it doesn't work!
Any help appreciated.
Thanks
DS
 
OK Here is the exact code that I am using...

If Me.Controls("D" & Day(Date)).Value = Day(Date) Then
Me.Controls("D" & Day(Date)).ForeColor = vbRed
Else
End If

Thanks
DS
 
Why do you have 41 text boxes, then? Sounds like you'll never use D32
through D41!
 
I Know it sounds crazy but it works! I just need to pull the value from the
"D" textbox now and I'm Good to Go!
I have this and it works but just for the one Textbox.

If CLng(Me.D11) = Me.TxtDay Then
Me.D11.ForeColor = RGB(255,0,0)
Else
Me.D11.Forecolor = RGB(192,192,192)
End If

Thanks
DS
 
Back
Top