hex number conversion.

[CJ_London]

I'm having one of those days, something I thought would be simple has led me into a circle of confusion.. I'm using Access 2010

What I want to be able to do is to assign a back colour to a control from VBA. There are many colours and what I thought would be an easy process was to choose a suitable colour to be assigned using the colour picker for a control, note the hex number then in my vba code would be

if... then me.mycontrol.backcolor=&HF0AD34

This compiles OK, but produces a completely different colour - I'm expecting an orange colour but getting blue and in another example a pale blue is coming out a brown.

research indicates adding an ampersand to the end changes the value from integer to long but if I type

if... then me.mycontrol.backcolor=&HF0AD34&

the second ampersand disappears.

In the immediate window
?&HF0AD34
?&HF0AD34&
?Val("&HF0AD34")
?clng("&HF0AD34")
?Val("&HF0AD34&")
?clng("&HF0AD34&")

All produce the same value of 15772980, which if I copy back to the control backcolour property produces the blue colour and not the required orange - which if i debug.print me.mycontrol.backcolor give me 3452400 - the correct number for orange.

The problem seems to be converting the hex number to a long.

I'm sure I'm missing something really simple, but I can't see it - any suggestions?

Also happy to modify the properties window to show the decimal value rather than the hex value, but can't see anywhere to do this

 

[jdraw]

Don't know if this will help
http://cloford.com/resources/colours/500col.htm

 

[ions]

In VBA dont' use the Hex use the Long equivalent which jdraw gave you.

 

[MarkK]

Here's a function I wrote that returns the Long color from a hex string. The problem is that the hex returned by the windows color picker, and maybe other tools, has red and blue reversed. This function swaps them and parses and converts the hex string to a long. Check out the documentation in the function . . .

Public Function GetHexColor(strHex As String) As Long
[COLOR="Green"]    'converts Hex string to long number, for colors
    'the leading # is optional

    'example usage
    'Me.iSupplier.BackColor = HexColor("FCA951")
    'Me.iSupplier.BackColor = HexColor("#FCA951")

    'the reason for this function is to programmatically use the
    'Hex colors generated by the color picker.
    'The trick is, you need to reverse the first and last hex of the
    'R G B combination and convert to Long
    'so that if the color picker gives you this color #FCA951
    'to set this in code, we need to return CLng(&H51A9FC)
[/COLOR]    
    Dim strColor As String
    Dim strR As String
    Dim strG As String
    Dim strB As String
    
[COLOR="Green"]    'strip the leading # if it exists[/COLOR]
    If Left(strHex, 1) = "#" Then
        strHex = Right(strHex, Len(strHex) - 1)
    End If
    
[COLOR="Green"]    'reverse the first two and last two hex numbers of the R G B values[/COLOR]
    strR = Left(strHex, 2)
    strG = Mid(strHex, 3, 2)
    strB = Right(strHex, 2)
    strColor = strB & strG & strR
    GetHexColor = CLng("&H" & strColor)
End Function

 

[JHB]

Maybe this could be of interest, (it is for Excel but MS-Access act in same way), go down to the paragraph, "Specifying Color in Properties Window", 3/4 down the document:
http://www.globaliconnect.com/excel...t-design-time-or-run-time&catid=79&Itemid=475

 

[vbaInet]

MarkK is spot-on there with his function. And I suppose you could just get their Red and Blue parts without needing the initial swap, i.e. using Right(,2) for Red and Left(,2) for Blue.

By the way CJ, if you have a reference to XL (which I'm sure you already do) you could use the many colour constants it provides:

XlRgbColor.rgbOrange

 

[CJ_London]

Thanks for all the responses - I'll use Markk's solution. I'm just so surprised that this needs a custom function to convert - clearly #F0AD34 returns the correct value/colour when pasted into a control backcolor but not when assigned in VBA.

Although a bit of a vanity project, this is just for me to develop some code rather than being a facility for a user. I was using the 'original' control to trial the look of both back and fore colours against alternative form back colours. The actual code sets size, position, back, border and fore colours plus font size depending on a number of variables determined by which fields have been completed, required to be completed and a couple of settings chosen by the user as part of the setup. In total there are 9 potential outcomes so conditional formatting does not provide sufficient options.

Effectively I am trying to have the form reconfigure itself when viewed via Teamviewer or Jump from a mobile phone

 

[vbaInet]

Did you try RGB()?

?rgb(&HF0, &HAD, &H34)

 

[CJ_London]

Did you try RGB()?

No, although I did consider it - the 'objective' was to just copy and paste once I had an acceptable colour combination, rather than messing around splitting up the hex code. Markk's solution works for me because I can just paste the hex code into the GetHexColor parameter.

I was just expecting there to be a standard function for VBA to recognise the correct hex code value per this MS link

http://support.microsoft.com/kb/161304

However on rereading the example is based on a 4 digit hex code rather than 6 digits which may have an impact?

but from my results
backcolor hexcode=#F0AD34 (as displayed in mycontrol property window)
backcolor decimal=3452400 (debug.print mycontrol.backcolor)
?val(&HF0AS34)=15772980 (from immediate window)
?hex(15772980)=F0AD34 (from immediate window)
?hex(3452400)=34ADF0 (from immediate window)

Which shows the first two and last two character sets have been swapped round. Edit: as advised by Markk

I guess I'm now asking the question - why doesn't the property window show the hex code correctly?

 

[vbaInet]

Where did you get that value from anyway? The true Hex value of 3452400 is 34ADF0.
Checked it on the web and using the Hex() function.

 

[vbaInet]

?hex(15772980)=F0AD34 (from immediate window)
?hex(3452400)=34ADF0 (from immediate window)

So I think the problem is the source of your hex value?

 

[ions]

Effectively I am trying to have the form reconfigure itself when viewed via Teamviewer

So far I have not had any need to reconfigure Access due to Teamviewer but I have had issues with labels being cut off when the user is using DPI Scaling other than 100%.

I would be interested if there is a custom built VBA solution for detecting the user's DPI Scaling settings and adjusting labels etc... accordingly.

The Turn Off DPI scaling feature for a specific program does not work for MS Access in Windows 7. (At least that is my understanding... I tried it on my machine and it didn't take effect) Perhaps I will start a new thread about this topic as it's becoming more of a concern as more and more users get higher resolution monitors and Default Scaling becomes 125% on new machines.

 

[CJ_London]

So I think the problem is the source of your hex value?

Yes - the source is the value in the properties window - #F0AD34. If I was to copy this to another colour property it provides the correct colour.

If I assign the backcolor property with a decimal value of 3452400 (which is hex 34ADF0) I get the right value to display the colour I want - but the backcolor property displays hex F0AD34 and not 34ADF0

 

[vbaInet]

I think this happens with all colour palettes, that's why you can use RGB() to interpret the hex to get the correct value without needing a swap.

 

[CJ_London]

So far I have not had any need to reconfigure Access due to Teamviewer

What I am attempting to do is if the user is using the application via teamviewer or similar from a phone or similar small screen device the form reconfigures itself to be easier to use in that environment.

So controls are made larger to suit touchscreen methods and relocated to make navigation around the form easier for a small screen - in a similar way that many websites reconfigure themselves when viewed from a small device.

So lets say your form is a summary form of a customer, containing, name, address, list of contracts and list of recent transactions.

On a larger screen and using a mouse, you might have the name at the top left, followed by address below and list of contracts below that. The list of transactions appearing to the right of the screen and is refreshed based on which contract has been selected.

On a larger screen this would be a perfectly useable form, but if the form is being viewed on a much smaller screen, the user would be forever scrolling left and right and up and down plus zooming in and out to select a contract and then view the results.

So to make it easier for the user, controls are resized, the transactions list is moved to below the contracts list and once a contract is selected, the contract list is reduced in size so the user can at least see the top of the transactions list and only needs to scroll in one direction rather than two.

I could just create two forms, but that then means two forms to maintain.

This is a vanity project, no one has asked me to do it yet, but a number of my clients use Jump or Teamviewer and have expressed an interest, so I'm trying to put together a demo and see how far I can automate it and determine the limiting parameters so that I can easily implement it into their applications and be confident it will be affordable and I won't underquote!

I would be interested if there is a custom built VBA solution for detecting the user's DPI Scaling settings and adjusting labels etc... accordingly.

There are form resizers about - here is an example

http://www.fmsinc.com/microsoftaccess/controls/components/form-resizer/index.html

But I want the resizing to also incorporate 'switching' between 'best design' for mouse or touchscreen as well as use of mini/micro screens in much the same way that some websites do.

 

[CJ_London]

I think this happens with all colour palettes, that's why you can use RGB() to interpret the hex to get the correct value without needing a swap.

So what is the logic in displaying the wrong code, or perhaps that should be the right code in the wrong order

 

[ions]

Thanks CJ London.

I actually have that FMS product but never used it. I am not sure if it would work for DPI as the user never resizes the form but perhaps it would?

I am going to contact FMS and ask. I will also start a new Thread about this to see what others are doing about DPI settings other than 100%.

 

[vbaInet]

I don't remember the theory behind it but I think it has to do with zero and the padding to always make it a 6 digit hex.

 

[ions]

If anyone is interested I started a new thread about DPI Scaling.

How do you handle DPI Scaling other than 100%?

http://www.access-programmers.co.uk/forums/showthread.php?p=1373177#post1373177