FillForegnd colour in hex

[Wilhelm]

(Visio2010)

Scenario:  I've got my shape linked to data. One field denotes colour (which I use to FillForegnd).  When I'm using =RGB(rr,gg,bb) it all works well. 

Problem:  My client has decided that they'd like to store the colour in the source data as Hex (#rrggbb).  When I try to feed that into my shape, it fails (turning it black - from what I understand the default fail operation.)  I'm sure this is something small like syntax or masking, but I just can't get it to work properly.  Any thoughts and pointers would be muchly appreciated!

Thanks.

[wapperdude]

It's hard to tell from your description, but I'm assuming that the hex doesnot need translation, that is, "rr" is identical in both your system and in hex.  If not, then you will have to translate and that will probably require code in Visio...or perhaps a lookup table.

To parse the hex string, you can use the mid and right shapesheet functions:  https://msdn.microsoft.com/en-us/library/ms425508(v=office.12).aspx.  That ought to be straight forward and and easy shapesheet task.  Once parsed, if no translation needed, then the values plug directly into the RGB function.  Otherwise, an intermediate step will be need to convert the hex to decimal.

Wapperdude

[Wilhelm]

Excellent - thank you very much, wapperdude!

Worked like a charm!  You are correct, the hex doesn't need translation.

My next challenge will be to convert the actual web colour name (e.g. Purple) to its Hex colour value (e.g. #800080).  I guess I'll need to put that in a new discussion - suspecting that will need to be done with a bit of vba...?

Thanks again!

[wapperdude]

There are standard definitions for color names and their numeric equivalents on the web.  These are usually in tables ... which suggests using Excel.  If the range of colors isn't too great, you might get away with an indexed list.  I might be wrong, but Visio may have a list built-in.

Wapperdude

[Surrogate]

Try this code please

Code Select Expand

Sub Fillings()
Dim hx$ ' hex color
Dim R% ' R component
Dim G% ' G component
Dim B% ' B component
Dim cs% ' current string
Dim i% ' counter
R = 0
G = 0
B = 0
hx = "#FADE0F" ' example of hex color
For i = Len(hx) To 2 Step -1
cs = htd(Mid(hx, i, 1)) ' get current symbol
Select Case i
Case 6, 7
B = B + cs * (16 ^ (7 - i)) '
Case 4, 5
cs = htd(Mid(hx, i, 1))
G = G + cs * (16 ^ (5 - i))
Case 2, 3
cs = htd(Mid(hx, i, 1))
R = R + cs * (16 ^ (3 - i))
End Select
Next
Dim sh As Shape
Set sh = ActiveWindow.Selection(1)
sh.Cells("FillForegnd").FormulaU = "RGB(" & R & "," & G & "," & B & ")" ' fill selected shape this RBG color
End Sub
Function htd(st As String) As Integer
' convert hex value to decimal
Select Case st
Case "A"
htd = 10
Case "B"
htd = 11
Case "C"
htd = 12
Case "D"
htd = 13
Case "E"
htd = 14
Case "F"
htd = 15
Case 0 To 9
htd = CInt(st)
End Select
End Function

[Wilhelm]

Thank you, Wapperdude and Surrogate

I used your thinking, and some code I found (not sure who's it is, sorry) to come up with the following which worked nicely.  (I also published a new post in 'Programming and Code' addressing the conversion from Web Colour name to Hex this morning.)  Surrogate, I especially liked how you converted the base 16 numbers - it's great for understanding.  Your code went quite a bit further than I needed, so thanks for that.  I only needed to get the value in the data field converted, since I'm using data coming from SSRS to draw my diagram.

Code Select Expand

[/Public Function HEXCOL2RGB(ByVal HexColor As String) As String
    'The colour input to fill milestones (or any other shape) must be in the format RGB(rr,gg,bb).
    'This function will convert a given HexColor value to the relevant RGB value.
    'The input at this point could be HexColor = "#00FF1F" or HexColor = "00FF1F"
    'Modified by Wilhelm van der Merwe on 20150416

Dim Color, Red, Green, Blue As String

Color = Replace(HexColor, "#", "")          '   Check to see if there is a # in front of the number, remove if so. (e.g. Here HexColor = "00FF1F")


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

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

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

HEXCOL2RGB = "RGB(" & Red & "," & Green & "," & Blue & ")"

End Functioncode]

[Surrogate]

Wilhelm, thank you too!

I didn't know conversion hint like

Code Select Expand

Val("&H" & Mid(Color, 1, 2))...

[JohnGoldsmith]

Just to add to this thread that Chris came up with a ShapeSheet solution for this as well:

   
• Get Web Hex Color Values from Visio Shapes

...which I used in this post too:

   
• Creating a color picker in Visio

Best regards

John

[Wilhelm]

No worries, Surrogate - I also picked it up from the code snippet someone else did.

Thanks, JohnGoldsmith - those are very good and handy indeed.