Conditional format - unconditional surrender

[kupe]

A combo is entered with Yes or No.

If Yes, I want it to have a red background and white font - or some other very significant change. The more significant the better. (If No, no change.) For some reason, Format/Conditional Formatting doesn't want to oblige. Help, please, Gurus.

 

[Rich]

I stand to be corrected but I didn't think you could directly format combos

 

[kupe]

Hi Rich
Yes, I suppose that's the case. I should be able to do it with straight- forward coding then, do you think please?

 

[shay]

I would stick the following code behind the combo box:

If Me.MyCombo = "Yes" Then
Me.MyCombo.BackColor = vbRed
Me.MyCombo.ForeColor = vbWhite
Else
Me.MyCombo.BackColor = vbWhite
Me.MyCombo.ForeColor = vbBlack
End If

There might be a better way but I think that should work.

shay

 

[Rich]

Not within the combo, that I know of

 

[kupe]

Shay and Rich

Sorry for delay in returning. Yes, the If Then Else has to work (doesn't it?). Now I have had the chance to apply it and it does - but only while the combo has the focus. I used "On change".

Could it be attached to the Next function, do you think?

It is vital that whenever the form turns up a "YES" that something significant happens, such as the colours.

H e l p ! Please.

 

[kupe]

Yes, it works on the Next.

It would be better, though, if it could be independent of that. (Next is a command button, and sometimes the User might well prefer the nav buttons. I could turn them off, of course, but there are times when they are more useful - like going directly to the end of the table.)

 

[shay]

I'd knocked up a quick example and think I put the code in the combo's beforeupdate event. Thinking about it you might need to use the current event also to ensure the colours change when viewing existing data.

shay

 

[shay]

Kupe

Try this...

Private Sub Form_Current()
FormatCombo
End Sub

Private Sub MyCombo_BeforeUpdate(Cancel As Integer)
FormatCombo
End Sub

Public Sub FormatCombo()
If Me.MyCombo = "Yes" Then
Me.MyCombo.BackColor = vbRed
Me.MyCombo.ForeColor = vbWhite
Else
Me.MyCombo.BackColor = vbWhite
Me.MyCombo.ForeColor = vbBlack
End If
End Sub

hth

shay

 

[kupe]

FormatCombo sits in a module.

Straight after "Else", Access says "Object required".

Does that ring any bells with you, Shay?

 

[shay]

If you've put FormatCombo in a module, the code won't work because it won't understand what 'Me' is.

Put the FormatCombo sub with the code behind the form and it should work.

If you want to be able to call FormatCombo from more than one form you will need to pass the form name to FormatCombo to replace all the occurrences of 'Me'.

Hope this makes sense

shay

 

[kupe]

Brilliant, Shay. My mistake. (Somehow, I was thinking that all 'Public' subs had to be modules.)

Works very nicely now.

I had it going from both Next and Back, but this is much neater. And the Public Sub makes it much simpler to add to, or reduce.

Many many thanks.

 

[shay]

Actually I'd make the sub Private - I was in a rush when I knocked up the code!

shay

 

[kupe]

I see what you mean. It works fine like this, but there won't be other demands for the sub in this particular db so I might as well. Cheers and thanks for the considerable help.

 

[shay]

'twas a pleasure!

shay