Changing backcolor

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with 62 controls on it. Some are combo boxes and some are text
boxes. I have a combo box that lets the users distinguish between an internal
and external order. When they choose external I want the backcolors of all my
fields to remain the colors that they currently are. When they choose
internal I want the back color of all my fields to change to a specific color
for that specific record and stay that way unless they select external from
the combo box. Is there an easy way to do this without having to write code
for every single control?
 
try adding the following procedure to the form's module, as

Private Sub isColored(ByVal lngColor As Long)

On Error GoTo isColored_Err

Dim ctlObject As Control

For Each ctlObject In Me.Controls
If TypeOf ctlObject Is TextBox Or _
TypeOf ctlObject Is ComboBox Then
If Me!ComboBoxName = "external" Then
ctlObject.BackColor = vbWhite
Else
ctlObject.BackColor = vbRed
End If
End If
Next ctlObject

isColored_Exit:
Exit Sub

isColored_Err:
Select Case err.Number
'Err.Description = "The expression you entered requires
' the control to be in the active window."
Case 2474
Resume isColored_Exit
Case Else
MsgBox err.Number & " " & err.Description, , _
"Public: isColored()"
Resume isColored_Exit
End Select

End Sub

substitute the correct name of the combo box control (where
"internal/external" is chosen), of course. also change the colors to
whatever you need. call the procedure from the form's Current event, and
also from the combo box control's AfterUpdate event.

note that with a little tweaking, you can put this code in a public module
and call it from multiple forms in your database. also note that the colors
will only be "record specific", if the form is set to SingleForm view. in
ContinuousForms view, all records will show the color that is appropriate to
the currently selected record.

hth
 
That's perfect Tina! Thank you very much. I will test it out once I finish
this other project I'm working on. But one question.... The backcolor is
usually a number of some sort that tells it the color. Do I put that number
where you have "vbWhite"? I'm using 16776960 as my back color.
 
you're welcome :)
one question.... The backcolor is
usually a number of some sort that tells it the color. Do I put that number
where you have "vbWhite"?
Click to expand...

yes, if you're not using "standard" colors that have VB constants available
for them, just code the actual numbers for the colors in the expressions, as

ctlObject.BackColor = 16776960

btw, i didn't test this code, just tweaked some existing code i have that
does something else - and my original code is in a public module, so... at
any rate, if the code doesn't work for you, post back and i'll have a deeper
look at it.

hth
 
Good Morning Tina,
Ok I'm working on this right now but I have another question. You said to
call this procedure from the forms OnCurrent event. I already have procedures
in that event. How should I write it to call that procedure?
 
Me again...I put the code in the forms module and then I put isColored in the
AfterUpdate event of the combo box but it keeps telling me that it can't find
the macro. Am I writing this call procedure correctly?
 
Hi Larry,

It sounds to me like you entered "isColored" into the property sheet. The
property sheet should be indicating Event Procedure, with code similar to
this (my test combo box was named "cboCountry"):

Private Sub cboCountry_AfterUpdate()
isColored
End Sub


Also, remove the required parameter for the isColored subroutine, ie. change
this:
Private Sub isColored(ByVal lngColor As Long)

to this:
Private Sub isColored()

since you are not passing a parameter into this procedure. I tested Tina's
code and it works great with these slight tweaks.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Also, remove the required parameter for the isColored subroutine, ie.
change
this:
Private Sub isColored(ByVal lngColor As Long)
Click to expand...

oops!! as i said, i tweaked this code from an existing procedure that does
something else, and i completely forgot to remove the parameter declaration.
thanks for catching my back, Tom. :)
 
as Tom said, you need to call the isColored procedure from the form's
Current event *procedure*, and the combo box control's AfterUpdate event
*procedure* - sorry i wasn't more explicit in my previous post. if you don't
know how to create an event procedure, go to
http://home.att.net/~california.db/instructions.html and click on the
"CreateEventProcedure" link for step-by-step, illustrated instructions.

hth
 
Hi Tom,
Thanks for your help! I am noticing one problem though. When I select
"Internal" from my combo box it changes all the records and not just the one
I have open on the screen. I do have that proceudre being called in the forms
OnCurrent event.

Also, When i choose external how do I make the colors go back to the way
they were? They weren't all the same color in the beginning. I have some that
are different colors to designate who is required to fill them in. When
"internal" is selected then only one person is responsible, when they are
"external" multiple people are responsible for certain fields.
 
comments inline.

Secret Squirrel said:
Hi Tom,
Thanks for your help! I am noticing one problem though. When I select
"Internal" from my combo box it changes all the records and not just the one
I have open on the screen. I do have that proceudre being called in the forms
OnCurrent event.
Click to expand...

as i said, "the colors will only be "record specific", if the form is set to
SingleForm view. in ContinuousForms view, all records will show the color
that is appropriate to
the currently selected record." if you want the colors to be record-specific
in ContinuousForms view, and if you are using A2000 or newer, you'll have to
use the Conditional Formatting option, and set two conditions for each
control - one for the "internal" value and one for the "external" value.
AFAIK, there's no way around that.
Also, When i choose external how do I make the colors go back to the way
they were? They weren't all the same color in the beginning.
Click to expand...

ok, i didn't catch that in your initial post, sorry. in each control's Tag
property, type the color number you want to see on "external" records, then
change the code to

Private Sub isColored()

On Error GoTo isColored_Err

Dim ctlObject As Control

For Each ctlObject In Me.Controls
If TypeOf ctlObject Is TextBox Or _
TypeOf ctlObject Is ComboBox Then
If Me!cboChoice = "external" Then
ctlObject.BackColor = ctlObject.Tag
Else
ctlObject.BackColor = vbWhite
End If
End If
Next ctlObject

isColored_Exit:
Exit Sub

isColored_Err:
Select Case err.Number
'Err.Description = "The expression you entered requires
' the control to be in the active window."
Case 2474
Resume isColored_Exit
Case Else
MsgBox err.Number & " " & err.Description, , _
"Public: isColored()"
Resume isColored_Exit
End Select

End Sub

again, changing the vbRed to whatever color you want for "internal" records.

hth
 
Thanks for your help! I am noticing one problem though. When I select
"Internal" from my combo box it changes all the records and not just the one
I have open on the screen.
Click to expand...

It sounds to me like you are not calling the procedure correctly from the
Form's CurrentEvent procedure. You should have something like this:

Private Sub Form_Current()
isColored
End Sub

Also, When i choose external how do I make the colors go back to the way
they were? They weren't all the same color in the beginning. I have some that
are different colors to designate who is required to fill them in. When
"internal" is selected then only one person is responsible, when they are
"external" multiple people are responsible for certain fields.
Click to expand...

You've got a tricky situation going on here. I think you will need to define
your business rules for the various colors of your controls. Something like
this (air code):


For Each ctlObject In Me.Controls
If TypeOf ctlObject Is TextBox Or _
TypeOf ctlObject Is ComboBox Then

Select Case cboOrderType
Case "Internal"
ctlObject.BackColor = vbRed 'or whatever color you want
Case "External"
Select Case ctlObject.tag
Case "Fred"
ctlObject.BackColor = 16776960
Case "Wilma"
ctlObject.BackColor = 8421440
Case "Barney"
ctlObject.BackColor = 4227327
Case Else
ctlObject.BackColor = vbWhite
End Select
Case Else
ctlObject.BackColor = vbWhite 'what to do if neither
int. nor ext.
End Select

End If
Next ctlObject


Note that the Else in Case Else is not surrounded in quotes.

You will then enter a tag property for each control that matches (without
the quotes). So, for example, some textboxes might have "Fred" as the tag
property, others might have "Wilma", and yet other's might have "Barney". The
tag property is the last one shown on the Other tab.

Disclaimer: "air code" is another way of saying I didn't test it! <smile>


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Hi Tom,
I must be missing something here then because I have that exact code in my
form's current event. But when I make the selection to "Internal" and then I
move to another record is keeps the changes the back colors for all my
records even though I only selected it for one record. Might I look somewhere
else for what's causing this problem?

As for my other problem about changing the colors back...I haven't decided
on how to go about that. Although when I change that selection from
"Internal" to "External" and then exit the form and go back in the colors are
restored to the original colors. Could I use some type of code for restoring
these back when I choose "External"?
 
did my previous post not answer your questions, or did you not read it? if
it didn't come through to your newsreader, and if you would like to read it,
i can post it again.
 
Back
Top