An Easier Way

[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

 

[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

 

[Douglas J. Steele]

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

 

[DS]

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

 

[BruceM]

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.

 

[DS]

Sounds good also. But is conditional formating stable?
Thanks
DS

 

[Douglas J. Steele]

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.

 

[BruceM]

I haven't heard otherwise.

Sounds good also. But is conditional formating stable?
Thanks
DS

 

[DS]

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

 

[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

 

[Douglas J. Steele]

Why do you have 41 text boxes, then? Sounds like you'll never use D32
through D41!

 

[DS]

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

 

[DS]

Thanks, I'm playing with that option as well.
DS

I haven't heard otherwise.