We hope you enjoy your visit.

You're currently viewing our forum as a guest. This means you are limited to certain areas of the board and there are some features you can't use. If you join our community, you'll be able to access member-only sections, and use many member-only features such as customizing your profile, sending personal messages, and voting in polls. Registration is simple, fast, and completely free.


Join our community!


If you're already a member please log in to your account to access all of our features:

Username:   Password:
Add Reply
VB arrays (excel based)
Topic Started: Jan 23 2005, 04:36 PM (1,518 Views)
kingy Jan 23 2005, 04:36 PM Post #1
1 in 10 people understands binary, the other 1 doesn't
[ * * * * * * ]
Code:
 

Sub colour()
'
' colour Macro
' Macro recorded 23/01/2005 by King
'
   Sheets("Sheet1").Select
   Range("E2").Select
   If Range("E2").Value = "0" Then
   Sheets("Sheet1").Select
   ActiveSheet.Shapes("Rectangle 2").Select
   Selection.ShapeRange.Fill.ForeColor.SchemeColor = 1
   Selection.ShapeRange.Fill.BackColor.RGB = RGB(255, 255, 255)
   End If
   If Range("E2").Value = "1" Then
   Sheets("Sheet1").Select
   ActiveSheet.Shapes("Rectangle 2").Select
   Selection.ShapeRange.Fill.ForeColor.SchemeColor = 2
   Selection.ShapeRange.Fill.BackColor.RGB = RGB(0, 0, 0)
   End If
   If Range("E2").Value = "2" Then
   Sheets("Sheet1").Select
   ActiveSheet.Shapes("Rectangle 2").Select
   Selection.ShapeRange.Fill.ForeColor.SchemeColor = 3
   Selection.ShapeRange.Fill.BackColor.RGB = RGB(25, 25, 25)
   End If
   If Range("E2").Value = "3" Then
   Sheets("Sheet1").Select
   ActiveSheet.Shapes("Rectangle 2").Select
   Selection.ShapeRange.Fill.ForeColor.SchemeColor = 4
   Selection.ShapeRange.Fill.BackColor.RGB = RGB(50, 50, 50)
   End If
   If Range("E2").Value = "4" Then
   Sheets("Sheet1").Select
   ActiveSheet.Shapes("Rectangle 2").Select
   Selection.ShapeRange.Fill.ForeColor.SchemeColor = 5
   Selection.ShapeRange.Fill.BackColor.RGB = RGB(100, 50, 0)
   End If
   If Range("E2").Value = "5" Then
   Sheets("Sheet1").Select
   ActiveSheet.Shapes("Rectangle 2").Select
   Selection.ShapeRange.Fill.ForeColor.SchemeColor = 6
   Selection.ShapeRange.Fill.BackColor.RGB = RGB(100, 100, 0)
   End If
   Range("A1").Select
   
   End Sub


ok this takes the cell E2 and then changes the colour of "Rectangle 2" dependant on what E2 displays.

it is really long and complicated and was wondering if anybody could simplify it for me (perhaps with an array, i dunno)

thanks, i am new to VB so any help is appreciated
Offline Profile Quote Post Goto Top
 
James Jan 23 2005, 07:01 PM Post #2
Live to Dream
[ * * * * * * * ]
 What about something like:

Code:
 

Sub colour()
'
' colour Macro
' Macro recorded 23/01/2005 by King
'
  Sheets("Sheet1").Select
  Range("E2").Select

  Dim iColours(5)
  iColours(0) = RGB(255, 255, 255)
  iColours(1) = RGB(0, 0, 0)
  iColours(2) = RGB(25, 25, 25)
  iColours(3) = RGB(50, 50, 50)
  iColours(4) = RGB(100, 50, 0)
  iColours(5) = RGB(100, 100, 0)

  If Int(Range("E2").Value) <= UBound(RangeVal) Then
   Sheets("Sheet1").Select
   ActiveSheet.Shapes("Rectangle 2").Select

  Selection.ShapeRange.Fill.ForeColor.SchemeColor= Int(Range("E2").Value) + 1
  Selection.ShapeRange.Fill.BackColor.RGB = iColours( Int(Range("E2").Value) )
  End If
  Range("A1").Select
 
End Sub


I wrote it quickly, don't have VB installed so can't test it, but in theory it should do the same thing.
Offline Profile Quote Post Goto Top
 
Seth Jan 23 2005, 07:02 PM Post #3
Member Avatar
I has a pony
[ * * * * * * * * * ]
 Actually, VB is incompatible with the British spelling of color :ph34r:
Offline Profile Quote Post Goto Top
 
James Jan 23 2005, 07:03 PM Post #4
Live to Dream
[ * * * * * * * ]
 Yes....but i'm declaring a variable called iColours, so it should be ok ;) All the rest of the "color"'s are correctly specified.
Offline Profile Quote Post Goto Top
 
Seth Jan 23 2005, 07:24 PM Post #5
Member Avatar
I has a pony
[ * * * * * * * * * ]
 No, any appearance of British words will automatically crash VB :ph34r: really, promise :ph34r:

:rofl:
Offline Profile Quote Post Goto Top
 
kingy Jan 24 2005, 08:08 AM Post #6
1 in 10 people understands binary, the other 1 doesn't
[ * * * * * * ]
Code:
 

Sub colour()
'
' color Macro
' Macro recorded 23/01/2005 by King
'

Dim iColors(9)
 iColors(0) = RGB(255, 255, 255)
 iColors(1) = RGB(128, 0, 0)
 iColors(2) = RGB(255, 153, 0)
 iColors(3) = RGB(102, 102, 153)
 iColors(4) = RGB(153, 51, 0)
 iColors(5) = RGB(204, 255, 255)
 iColors(6) = RGB(255, 204, 0)
 iColors(7) = RGB(128, 0, 128)
 
 Sheets("Input").Select
 ActiveSheet.Shapes("Rectangle 11").Select
 Selection.ShapeRange.Fill.ForeColor.SchemeColor = Int(Range("L6").Value)
 Selection.ShapeRange.Fill.BackColor.RGB = iColors(Int(Range("L6").Value))
 Range("A1").Select

End Sub


thanks, i have modified it a little, but now it isn't changing the colours correctly, eg, i have changed the hex codes but they won't appear the right colour.

any help appreciated!

kingy

(sorry for being a pain)
Offline Profile Quote Post Goto Top
 
Stefan Jan 24 2005, 09:34 AM Post #7
Member Avatar
Mew?
[ * * * * * * * ]
 Maybe changing the order in the hex colours might help.
In CSS, for instance, it's like this:

#RRGGBB

But I know that when you set a color for GUI elements (text input box, buttons, etc.) in Visual Basic, the hex code is like this:

&HBBGGRR
Offline Profile Quote Post Goto Top
 
SkItZo Jan 24 2005, 10:14 AM Post #8
Support Volunteer
[ * * * ]
 color hexes in VB are of this type: &H0000FF00&

Thats the color lime if you're wondering. Of, if you're using C++ use this... 0x00FF00 same color, but different hex code.
Offline Profile Quote Post Goto Top
 
James Jan 24 2005, 07:58 PM Post #9
Live to Dream
[ * * * * * * * ]
 Yup, the reason i added the IF statement was because if you wrote in 7 by accident there wouldn't be an iColours for it, so the IF statement checked whether the number was below or equal to the maximum array count (UBOUND) before doing anything. but your choice. I thought the RGB might cause you problems, have you tried removing the .RGB and seeing if it makes any difference? I really need to install my VB copy...
Offline Profile Quote Post Goto Top
 
kingy Jan 25 2005, 01:56 PM Post #10
1 in 10 people understands binary, the other 1 doesn't
[ * * * * * * ]
 well it doesn't have to be RGB,

colorindex (or indexcolor , whatever they call it) would be alright as well, but i don't know VB very well so i can't edit right
Offline Profile Quote Post Goto Top
 
1 user reading this topic (1 Guest and 0 Anonymous)
« Previous Topic · Technology Chat · Next Topic »
Add Reply