Form button colour change while macro is running

[peskywinnets]

I have a button on a form, which once pressed, it take 5-10 seconds for access to finish 'processing' all the data. During this time, I would like the button to change colour (to give the user some visual representation that 'processing is still happening')

The form button simply triggers a set of chained macros, so I could conceivably top & tail the button colour change within the macros (Run Code?)....i.e. change colour at the start, then change back at the end of the macro

I'm quite new to forms...so just wondering what VBA would be need to trigger the form button colour change?

 

[sneuberg]

You can change the colors of the button with code like for example:

Me.TheButtonName.ForeColor = vbBlue
Me.TheButtonName.BackColor = vbRed
Me.TheButtonName.ForeColor = RGB(100, 100, 100)

 

[peskywinnets]

Thank you, don't laugh, I did this...

Public Function ChangeButtonRunColour()
Me.Test.BackColor = vbRed
End Function

& I put it in the top of the macro...but when I clicked the the button on my form (which & ran the macro), it barfed with the me. bit (I've never understood what the me. prefix meant?!)

I'm figuring it needs a full path to the button (vs. just .me?)

My form is called HomePage, my button is called Test, therefore what command should I be using in my vba code in place of the me. prefix?

 

[sneuberg]

Try

Forms!HomePage!Test.BackColor = vbRed

"Me" would mean the same as "Forms!HomePage" if the code were in the form's module.

 

[peskywinnets]

Thanks that worked a treat

Problem is reverting the colour back! I was using a button colour that had a BackColor value of #ADC0D9 ...does anyone have any idea of the syntax to use that value format instead of vbRed, vbBlue etc?


Edit:
Hmm, I found this post #6 here http://www.access-programmers.co.uk/forums/showthread.php?t=217833

which suggests...

     Me!Control.Backcolor = Val("&H" & "FFFFFF")

while the command is accepted/actioned in VBA, I end up with a different value for the backcolor for the button than specified?!

 

[peskywinnets]

How bizarre, I have come up with a workaround to get the correct colour to be set - I have to reverse the characters required!

Therefore if I want the BackColor to be#ADC0D9 I have to enter like this.....

Forms!HomePage!Test = Val("&H" & "D9C0AD")

....but it works!!!

 

[gemma-the-husky]

the #D9COAD correspond to RGB codes.

each is a value from 0 to 255. In hex that is #00 to #FF

In some cases the order of RGB is this
red #D9 green #CO blue #AD

in others
blue #D9 green #CO red #AD

I think you have found a vice-versa example.

 

[The_Doc_Man]

In Excel and Access the order of the R, G, and B components is reversed. Also, remember that on a "little-endian" machine like any Intel-based PC, when you read 4 hex digits from a long, they are presented as 4th, 3rd, 2nd, 1st - because formatting aggregates the bytes in ascending byte-address order even if the that isn't the "natural" order we normally expect for the contents of a LONG.

 

[The_Doc_Man]

2nd note: The reason you had problems with "ME" is that macros are globals; "ME" only works from a routine inside the context of the class module; ie. it is a local shortcut. This is the same reason that in a general module, you can't make a "ME" reference. It has to do with SCOPE and in anything general/global, the localized shortcuts are not in scope.

 

[sneuberg]

?Val("&H" & "00FF00")

Produces -256 in the immediate window and the color black so this doesn't look like a good way of converting hex to long.

RGB(&HAD, &HC0, &HD9)

is another way of getting the color #ADC0D9

 

[moke123]

had this in my utility module but haven't used in quite a while. it converts the hex to rgb. (or a long that access reads as a hex)

Public Function HEXCOL2RGB(ByVal HexColor As String) As String

'The input at this point could be HexColor = "#00FF1F"

    Dim Red As String
    Dim Green As String
    Dim Blue As String

    HexColor = Replace(HexColor, "#", "")
    'Here HexColor = "00FF1F"

    Red = Val("&H" & Mid(HexColor, 1, 2))
    'The red value is now the long version of "00"

    Green = Val("&H" & Mid(HexColor, 3, 2))
    'The red value is now the long version of "FF"

    Blue = Val("&H" & Mid(HexColor, 5, 2))
    'The red value is now the long version of "1F"


    HEXCOL2RGB = RGB(Red, Green, Blue)
    'The output is an RGB value
    Debug.Print HEXCOL2RGB
    
End Function

 

[peskywinnets]

had this in my utility module but haven't used in quite a while. it converts the hex to rgb. (or a long that access reads as a hex)

I've not had a chance to deploy/use this yet, but it looks very useful - thanks for sharing

 

[MarkK]

Problem is reverting the colour back! I was using a button colour that had a BackColor value of #ADC0D9

You can save the design view color of the control before you change it, then change it back to the saved value at the end...

private sub YourRoutine
   dim oldColor as long

   oldColor = me.cmdControl.BackColor
   me.cmdControl.BackColor = vbRed
[COLOR="Green"]   ' long running routine executes here[/COLOR]
   me.cmdControl.BakcColor = oldColor
end sub

...so you don't have to hard-code a color.

 

[The_Doc_Man]

One last word of warning: DON'T try to "compute" a color other than through diddling with the RGB function because if you happen to compute something that becomes a negative number, the "system" colors come into play. That is, you start using "windows-themed" controls to match the colors of your chosen desktop theme because there are some color values to map theme colors - and all such values are negative. (Actually, they are numbers for which the high-order bit is set - &H80000000 plus a code number.)