By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,599 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

how to change forecolour in combo box based on selection

SteHawk85
P: 34
Hi

I have a combo box with 3 options - Red, Amber and Green. I have wrote a bit of code (below) that I was hoping would change the fore colour of the box dependent on which option was selected, i.e. red = red etc. My code can be seen below, I am not getting any errors when I run it, but the fore colour is not changing when an option is selected and have tried it in both before update and after update.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboRagRating_AfterUpdate()
  2. On Error GoTo ErrorHandler
  3.  
  4.    If Me.cboRagRating.Value = "Red" Then
  5.       Me.cboRagRating.ForeColor = vbRed
  6.  
  7.    ElseIf Me.cboRagRating.Value = "Amber" Then
  8.           Me.cboRagRating.ForeColor = RGB(255, 245, 238)
  9.  
  10.    ElseIf Me.cboRagRating.Value = "Green" Then
  11.           Me.cboRagRating.ForeColor = vbGreen
  12.    End If
  13.  
  14. CleanUpAndExit:
  15.     Exit Sub
  16.  
  17. ErrorHandler:
  18.     Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
  19.     Resume CleanUpAndExit
  20. End Sub
Any help would be greatly appreciated

Thanks

Ste
Apr 17 '12 #1

✓ answered by Mariostg

Not quite, because if you do that, then when you do a selection on your combo box then the color will not update. So that being said, you need it at both places :)
Or you write a function (basically what you wrote) that you place in a module as opposed to be in the form module. Then each of the On current and After Update events would call the same function. It is a little more advanced, but cleaner.

Other option, would be that the On current event calls the After Update. So you would have something like Call cboRagRating_AfterUpdate() if you see what I mean.

Share this Question
Share on Google+
15 Replies


100+
P: 332
Well, your code is working here and I am on Access 2003.
On the rare occasions I needed something like that, I prefer to alter the color of the caption of the combo box instead. And I change the background instead of the foreground.
Apr 17 '12 #2

SteHawk85
P: 34
Hi

Yeah I figured out what I was doing wrong and its working now except for one thing. Best give you some background first I am building a pupil database base and the form shows 1 pupils details at a time, now when I skip to the next pupil it carries over the fore colour from the previous page i.e. if pupil one is selected as red the fore colour changes to red, however when I skip to the next pupil the fore colour is red even if the option is Green! Below is the new code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboRagRating_AfterUpdate()
  2. On Error GoTo ErrorHandler
  3.  
  4.    If Me.cboRagRating.Value = "1" Then
  5.       Me.cboRagRating.ForeColor = vbRed
  6.  
  7.    ElseIf Me.cboRagRating.Value = "2" Then
  8.           Me.cboRagRating.ForeColor = RGB(255, 245, 238)
  9.  
  10.    ElseIf Me.cboRagRating.Value = "3" Then
  11.           Me.cboRagRating.ForeColor = vbGreen
  12.      End If
  13.  
  14. CleanUpAndExit:
  15.     Exit Sub
  16.  
  17. ErrorHandler:
  18.     Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
  19.     Resume CleanUpAndExit
  20. End Sub
Any ideas? Again help is greatly appreciated

Ste
Apr 17 '12 #3

100+
P: 332
That probably has something to do with the On current property of the form. Basically, when you navigate from one record to another , you have to check what the value of the combo box is and then set the color accordingly.
Apr 17 '12 #4

SteHawk85
P: 34
Mariostg

I'm still quite new to VBA, however the Combo box doesn't seem to have an 'On Current' option in the event tab, can you expand on what you mean?

Thanks by the way for the Back Colour idea have changed my code and that looks a lot better!
Apr 17 '12 #5

100+
P: 332
Welcome to VBA.
The combo box indeed does not, but the form has many event properties and one of them is On Current. The On current Event is activated when you move from one record to another one.
Apr 17 '12 #6

SteHawk85
P: 34
cheers mate would it just be a case of moving the code over to the 'On Current' event of the form?
Apr 17 '12 #7

100+
P: 332
Not quite, because if you do that, then when you do a selection on your combo box then the color will not update. So that being said, you need it at both places :)
Or you write a function (basically what you wrote) that you place in a module as opposed to be in the form module. Then each of the On current and After Update events would call the same function. It is a little more advanced, but cleaner.

Other option, would be that the On current event calls the After Update. So you would have something like Call cboRagRating_AfterUpdate() if you see what I mean.
Apr 17 '12 #8

SteHawk85
P: 34
Nice one mate Super Star that 'Call cboRagRating_AfterUpdate()' has worked an absolute treat! : D
Apr 17 '12 #9

100+
P: 332
You are welcome.
Feel free to mark your question answered.
Apr 17 '12 #10

NeoPa
Expert Mod 15k+
P: 31,186
SteHawk85:
Nice one mate Super Star that 'Call cboRagRating_AfterUpdate()' has worked an absolute treat! : D
That does indeed work Ste. However, it was no accident that Mario first suggested putting the code in a standard procedure and calling this from both event procedures. Maintenance-wise, calling one event procedure from another is considered messy. On the plus side, while putting the standard procedure in a separate, standard module is viable, it isn't necessary. If a procedure is used exclusively for a process related to a specific form, then it makes perfect sense to create it as a Private procedure within the form's module. The result is much tidier than calling one event procedure from another.
Apr 18 '12 #11

SteHawk85
P: 34
so basically i write a module with the code and that works without me having to use the code?
Expand|Select|Wrap|Line Numbers
  1. Call cboRagRating_AfterUpdate()
It's working fine at the min but if this would make it easier to use in future projects I wouldn't mind understanding what you mean fully.
May 14 '12 #12

NeoPa
Expert Mod 15k+
P: 31,186
Let me try to explain then. Assume you have two different events (EG. Form_Current() and X_AfterUpdate()) that require code ({CODE}) to be run whenever they are triggered. There are two way this can be handled within the same module - that of the form itself. #1 is not recommended as it imposes a non-flexible structure and undesirable dependencies on the code. Thus, #2 is more maintainable and easier to understand and deal with.
  1. Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Current()
    2.     {CODE}
    3. End Sub
    4.  
    5. Private Sub X_AfterUpdate()
    6.     Call Form_Current()
    7. End Sub
  2. Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Current()
    2.     Call CommonRoutine()
    3. End Sub
    4.  
    5. Private Sub X_AfterUpdate()
    6.     Call CommonRoutine()
    7. End Sub
    8.  
    9. Private Sub CommonRoutine()
    10.     {CODE}
    11. End Sub
May 14 '12 #13

SteHawk85
P: 34
I see! so all i have to do is move the Code from Form_Current into a module called 'Common Routine' and then use the call function in both the 'After Update' and 'Current Form'.

cheers Neo once again you have helped me out!
May 17 '12 #14

NeoPa
Expert Mod 15k+
P: 31,186
SteHawk85:
so all i have to do is move the Code from Form_Current into a module called 'Common Routine' and then ...
Not unless you mean procedure rather than module. In that case then yes. Absolutely :-)
May 17 '12 #15

SteHawk85
P: 34
haha yeah still getting used to the lingo!
May 18 '12 #16

Post your reply

Sign in to post your reply or Sign up for a free account.