Change color using VBA

[Pat Hartman]

I want to change the color of the border of a button if the form the button opens has data to show. Using the theme name of the color doesn't work. Am I going to have to figure out what the numeric value is and hard code that????? That makes the themes pretty useless.

    If DCount("*", "tblGifts", "ContID = " & Me.ContID) > 0 Then
        Me.cmdGifts.BorderColor = "Accent 1, Darker 25%"
    Else
        Me.cmdGifts.BorderColor = "Accent 6"
    End If

 

[Josef P.]

see: https://learn.microsoft.com/en-us/office/vba/api/access.commandbutton.borderthemecolorindex
and https://learn.microsoft.com/en-us/office/vba/api/access.commandbutton.bordertint

 

[LarryE]

Hey @Pat Hartman
Here's a little color code file I've had for years using ACCESS color codes. You can select a color, see the color and then lookup the color code in the color selected query. Maybe it will at least make it easier for you.

 

[The_Doc_Man]

You can get simple color constants by opening a database to the VB page, then opening project browser and looking for "acColorIndex" - which enumerates 16 colors. If you find "SystemColorConstants" you get some other options that are named according to system-theme object choices. Note that these are used differently.

The color index goes into a color index property and is a list of non-negative numbers. The system color constants, however, are LONG constants which, in hex, have the general form 0x8000nnnn, and you load these to the actual .xxxxColor slot. They are treated as negative numbers because of the 0x80... starting sequence.

Basically, for the system-themed colors, you load a negative index to the color. For RGB, you load a positive RGB mask to the color. For the index, you load a positive number to the color index property, which is separate from the raw color property.

 

[Pat Hartman]

Thanks everybody. The links Josef provided got me to the right place. I forgot about all the "new" properties. If you want to use themes, you need to set those properties. This is what I ended up with. I had a date field that if it fell within a week I wanted to highlight. I ended up coloring the background of the label and then toggling the BackStyle between normal and transparent. Then I had three buttons that I wanted to highlight if there was data to show and for those, I used Index and Tint and rather than hard code both sides of the color, I used the properties of the close button which doesn't change so has the defaults for the buttons.

Private Sub Form_Current()
    bPreventClose = False
    If Me.NewRecord Then
    Else
        Me.txtGift = DLookup("SumOfCost", "qContactGiftTotal", "ContID = " & [ContID])
    End If
    If IsDate(Me.NextContactDT) Then
        If DateAdd("d", 7, Date) > Me.NextContactDT And Me.NextContactDT >= Date Then
            Me.NextContactDT_Label.BackStyle = 1        '' normal -- show color
        Else
            Me.NextContactDT_Label.BackStyle = 0        '' transparent -- hide color
        End If
    Else
        Me.NextContactDT_Label.BackStyle = 0        '' transparent -- hide color
    End If
    If DCount("*", "tblGifts", "ContID = " & Me.ContID) > 0 Then
        Me.cmdGifts.BorderThemeColorIndex = 4  '''' "Accent 1, Darker 25%"
        Me.cmdGifts.BorderTint = 75
    Else
        Me.cmdGifts.BorderThemeColorIndex = Me.cmdClose.BorderThemeColorIndex
        Me.cmdGifts.BorderTint = Me.cmdClose.BorderTint
    End If
    If DCount("*", "tblDependents", "ContID = " & Me.ContID) > 0 Then
        Me.cmdDependents.BorderThemeColorIndex = 4  '''' "Accent 1, Darker 25%"
        Me.cmdDependents.BorderTint = 75
    Else
        Me.cmdDependents.BorderThemeColorIndex = Me.cmdClose.BorderThemeColorIndex
        Me.cmdDependents.BorderTint = Me.cmdClose.BorderTint
    End If
    If DCount("*", "tblCommunicationLog", "ContID = " & Me.ContID) > 0 Then
        Me.cmdComm.BorderThemeColorIndex = 4  '''' "Accent 1, Darker 25%"
        Me.cmdComm.BorderTint = 75
    Else
        Me.cmdComm.BorderThemeColorIndex = Me.cmdClose.BorderThemeColorIndex
        Me.cmdComm.BorderTint = Me.cmdClose.BorderTint
    End If
End Sub

 

[Pat Hartman]

@LarryE Thanks for that form. I think you overthought the process. I removed ALL the code from the form and replaced it with ONE LINE of code in the Current event. As you click into a row to make it current, it just changes the color of the box at the top of the form.

Private Sub Form_Current()
    Me.txtColorBox.BackColor = Me.ColorCode
End Sub