Obtain the numerical value of a color

[GraeagleBill]

Is there a way to obtain the numerical value of a color chosen for conditional formatting? I tried to capture the BackColor property of a control during a display using the OnClick event, but all I get is the nominal value assigned when the control was created, not the current color.

[June7]

Which numeric code - HEX, RGB, Access?
https://www.endprod.com/colors/#:~:t...more%20rows%20
https://www.thespreadsheetguru.com/t...%2C125%2C39%29

I don't think VBA can capture the color code of the conditionally displayed color.

[GraeagleBill]

I don't think VBA can capture the color code of the conditionally displayed color.

I don't think so either. I'll mark the OP as solved and use the chart you linked to take care of the current issue.

Thanks,
Bill

[Micron]

?forms!formName.ControlName.formatconditions(0).backcolor

6593305
EDIT - zero based collection, btw. I realize that's not exactly what you asked for, but in your code you could test the same condition you applied in the format?

[GraeagleBill]

I haven't verified the values returned, but my sense given the numerical differences, is that your solution is correct. I suspect June7 will be happy to see this as well.
Thanks,
Bill

Private Sub tbPeriod_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
MsgBox Forms!frmSchedule.tbPeriod.FormatConditions(0).BackColor
MsgBox Forms!frmSchedule.tbPeriod.FormatConditions(1).BackColor
MsgBox Forms!frmSchedule.tbPeriod.FormatConditions(2).BackColor
MsgBox Forms!frmSchedule.tbPeriod.FormatConditions(3).BackColor

(snip)

End Sub

[Micron]

I don't think I explained myself very well or I didn't understand the requirement. I thought that you want to know what color is showing at any time but that was based on June7's comment

I don't think VBA can capture the color code of the conditionally displayed color.

but upon review, perhaps you didn't want that. Instead you wanted to know the color that you set per each condition.

obtain the numerical value of a color chosen for conditional formatting?

in which case I guess what you posted is pretty much what you need.

[GraeagleBill]

Yes, I need those values to be used elsewhere. Additionally, now that you've enlightened me to the existence of the "formatconditions" collection, if such a collection is known to have been created, can VBA code "change" the value? (I know I can just test it, but for general benefit) E.g.,

Forms!frmSchedule.tbPeriod.FormatConditions(3).BackColor = 6593305

EDIT: YES, one can in fact update the "formatconditions" collection. (Very helpful if one needs to replicate a color scheme throughout an app.)

[Micron]

Yes you can change the property. Not sure if you can use RGB or must use the number format. I think either would be acceptable but I can't recall if I ever tried RGB.
You can test if collection contains any members by testing its count property. Technically speaking, I don't believe you create the collection - it exists by default. At least I can get a count of zero on a field that has no CF applied.

[GraeagleBill]

At least I can get a count of zero on a field that has no CF applied.

Oh good! While it's unlikely one would code an assignment in code where no such collections exist, it's good to know one can test the collection before stepping off into the abyss.

[isladogs]

See if you can make use of this utility: https://www.isladogs.co.uk/colour-converter/

[Micron]

where no such collections exist

Well, I'm pretty sure it exists (technically speaking) because it's a property of a combo and textbox (and likely any other control that is CF capable - if there are any). Not sure I grasp how the collections object can be a property of a control, but that's what I get from this:

TextBox.FormatConditions property (Access)

Use the FormatConditions property to return a read-only reference to the FormatConditions collection and its related properties.

that and the fact that you don't get an error if you test the collection count where no CF has been applied.

[GraeagleBill]

Thanks Colin, I just have an app where I want "established" conditional colors to be used consistently elsewhere. So, the requirement was to obtain the Access value that is stipulated by way of the conditional formatting of a major control.

[June7]

Yes, can use code to obtain the settings of CF rules. Now that I read your original question again, I can see that is what you really wanted (not "grab" code of color currently displaying as result of rules). Glad you found solution.

Once you determine what those color codes are, establish some global constants you can use anywhere.

[Micron]

I still think you could know by reference (or to put it another way, by field value). Assuming you set cf in form design view, you'd have to replicate the 'test' that your cf expression performs and if all goes well you can assume what the color is. However, it would not make a whole lot of sense to me when you can just look at it. So I only see any sense in equating colors you might want to standardize by getting their numbers. However, I think I would prefer to use a color picker app and maybe just put the values in a table somehow. I used to have a nice one that you could click on any part of the screen and know what the color number is for that particular spot.

[GraeagleBill]

I think from what I've learned, beginning with your post #4, that I would start with a color picker and a table of defining values for the app together with some "picker" functionality in the app's "Settings" form if or when changes might be desired. As you can imagine, with the belief that CF colors could ONLY be determined with the CF UI one could easily find themselves digging a hole to jump into as I did with this caper.