Code review - pretty please :)

General
1

Howdy,
I am trying to set a row’s background colour, depending on the text content of that particular row.

Example:
If row 1 contains the text RGB(10,20,30), I would like that particular row to have that background colour.

I have the following code, but it is slightly wrong somewhere?
Could anyone tell me where I have gone wrong?

Thank you.

Dim v As String Dim v2 As Variant if column = 0 then v = me.cell(row,0) v2 = "&c" + Hex(Val(v)) g.ForeColor = v2.ColorValue g.fillrect(0,0, g.Width, g.Height)

2

v2 will always be &C0 because Val(v) in this case isn’t translating the RGB code into anything useful. You’ll need to use something like

Dim v() As String = Split(Mid(me.cell(row, 0), 5, Len(s) - 1), ",") Dim v2 As Variant = "&c" + Right("0" + Hex(v(0)), 2) + Right("0" + Hex(v(1)), 2) + Right("0" + Hex(v(2)), 2)

v2 will then have a valid color string.

HTH

Wayne

3

Wayne - what does this refer to: Len(s)? It causes a this item does not exist error?

Thanks.

4

Sorry the s should be me.cell(row, 0) I was testing using a string - s.

5

Ok - my code now looks like this:

[code]Dim v() As String = Split(Mid(me.cell(row, 0), 5, Len(me.cell(row, 0) - 1), “,”)
Dim v2 As Variant = “&c” + Right(“0” + Hex(v(0)), 2) + Right(“0” + Hex(v(1)), 2) + Right(“0” + Hex(v(2)), 2)

if column = 0 then

g.ForeColor = v2.ColorValue
g.fillrect(0,0, g.Width, g.Height)[/code]

But I get the 2 following errors - which seems to make no sense as v IS a string and DOES exist???

I then removed the -1 from the first line, but then received these errors:

[code]Dim v() As String = Split(Mid(me.cell(row, 0), 5, Len(me.cell(row, 0)), “,”)
Dim v2 As Variant = “&c” + Right(“0” + Hex(v(0)), 2) + Right(“0” + Hex(v(1)), 2) + Right(“0” + Hex(v(2)), 2)

if column = 0 then

g.ForeColor = v2.ColorValue
g.fillrect(0,0, g.Width, g.Height)[/code]

I am totally perplexed, as I have never used the Split or Mid functions before, so trying to combine them both has totally lost me :frowning:

6

Dim v() As String = Split(Mid(me.cell(row, 0), 5, Len(me.cell(row, 0)) - 1), ",") Dim v2 As Variant = "&c" + Right("0" + Hex(v(0)), 2) + Right("0" + Hex(v(1)), 2) + Right("0" + Hex(v(2)), 2)

You’re missing a ) to close the Len statement.

This might be easier to understand

[code] Dim v() As String
Dim s As String
Dim v2 As Variant

s = Mid(me.cell(row, 0), 5, Len(me.cell(row, 0)) - 1) ’ s = 10,20,30
v = Split(s, “,”) ’ v(0) = “10”, v(1) = “20”, v(2) = “30”
v2 = “&c”
For i As Integer = 0 To 2
v2 = v2 + Right(“0” + Hex(v(i)), 2)
Next i
[/code]

I’m using Right(“0” + Hex(), 2) to force a leading “0”.

7

Hi Richard,

Here is a quick program I put together which adds two rows to a listbox with the RGB(10,20,30) format, then reads the first cell and separates the RGB values into three variables to be viewed in a message box. This uses the Mid function and the first number (5) starts getting data 5 characters in, and the 2 means to only retrieve 2 characters.

Download Example

Here is the example code:

[code] 'Format the listbox
Listbox1.HasHeading = true
Listbox1.ColumnCount = 1
listbox1.ColumnWidths = “100%”
Listbox1.InitialValue = “Colour”

'Add data to the listbox
Listbox1.AddRow
Listbox1.Cell(Listbox1.LastIndex, 0) = “RGB(10,20,30)”
Listbox1.AddRow
Listbox1.Cell(Listbox1.LastIndex, 0) = “RGB(70,80,90)”

'Get colours from column1, row 1 (zero based)
Dim CRed, CGreen, CBlue as String
CRed = (Mid(Listbox1.cell(0,0), 5,2))
CBlue = (Mid(Listbox1.cell(0,0), 8,2))
CGreen = (Mid(Listbox1.cell(0,0), 11,2))

MsgBox "Red is " + CRed + chr(13) +_
"Blue is " + CBlue + Chr(13) +_
"Green is " + CGreen [/code]

Does this help?

8

Thanks guys - but I will have to forget this idea.

Wayne - my code now looks like this:

[code]Dim v() As String
Dim s As String
Dim v2 As Variant

s = Mid(me.cell(row, 0), 5, Len(me.cell(row, 0)) - 1)
v = Split(s, “,”)
v2 = “&c”
For i As Integer = 0 To 2
v2 = v2 + Right(“0” + Hex(v(i)), 2)
Next i

g.ForeColor = v2.ColorValue
g.fillrect(0,0, g.Width, g.Height)[/code]

This simply turns all the rows black.

Eugene - thanks for the project file, but that only works of the RGB value is 2 digits in length, when in reality, it could be 3 RGB(123, 255, 34)

I really appreciate both your help, but I just cannot get this to work :frowning:

9

Set a break point here and see what each of the values are.

v2 = v2 + Right("0" + Hex(v(i)), 2)
10

v2 = &c

11

And each point in the array? That is where your parsing issue is going south.

12

i
0 = (0
1 = 0
2 = 0)

13

Based on those values you should end up with black.

14

Hi Richard,

I modified the code to Waynes, and it seems to work well. Here is the code.

[code] 'Format the listbox
Listbox1.HasHeading = true
Listbox1.ColumnCount = 1
listbox1.ColumnWidths = “100%”
Listbox1.InitialValue = “Colour”

'Add data to the listbox
Listbox1.AddRow
Listbox1.Cell(Listbox1.LastIndex, 0) = “RGB(250,20,30)”
Listbox1.AddRow
Listbox1.Cell(Listbox1.LastIndex, 0) = “RGB(70,80,90)”

'Get colours from column1, row 1 (zero based)
Dim CRed, CGreen, CBlue as String

Dim s As String
Dim v(-1) as String
Dim v2 As Variant
s = Mid(Listbox1.cell(0, 0), 5, Len(Listbox1.cell(0, 0)) - 1)
v = Split(s, “,”)
v2 = “&c”
For i As Integer = 0 To 2
v2 = v2 + Right(“0” + Hex(v(i)), 2)
Next i

MsgBox "Red is " + Cstr(v2.ColorValue.Red) + chr(13) +_
"Green is " + CStr(v2.ColorValue.Green) + chr(13) + _
"Blue is " + CStr(v2.ColorValue.Blue)
[/code]

15

Here is the link to the project file.

Updated Test File

16

Bob - the rows background colours were supposed to change to the RGB colour value in each row (as opposed to all black) :slight_smile:

Eugene - thank you for doing that for me - I will study that for future reference.

I eventually got it working by converting each of the R, G, and B values into the Hexadecimal equivalent when I added each row - and then simply prefixed &c to the value, before setting it to the row’s background colour.

Thank you everyone for a lll the help and patience - it is much appreciated.