tommy bak
08-23-2004, 08:28 AM
HI
I've been playing a little with the possibility to format a chart conditionally and I have got it working now.
My problem is that I would like it to be more flexible, but right now I seem to be a bit braindead and need some help :help
It should be more flexible on the numbers of conditions and corresponding colors.
Right now it's locked to 3 (bad, good, exelent)
I have made it as a classmodule, becaurse I want to include it in a couple of workbooks. As it is my first classmodule, please give some feedback.
[Edit] : Complete file is attached
Class module:
Option Explicit
'repaints charts within 2 limits in 3 different colors
Public WithEvents Graph As Chart
'Limits for coloring
Dim L2 As Long, L3 As Long
'Colors for each section
Dim C1 As Long, C2 As Long, C3 As Long
'holder for series-number
Dim S As Long
Private Sub Graph_Calculate()
Dim y As Long
Dim c As Range
Dim rngChart As Range
Dim vaGetRange
'Split the chartformula at ,
vaGetRange = Split(Graph.SeriesCollection(S).Formula, ",")
'choose the text between 2 and 3 comma;
Set rngChart = Range(vaGetRange(2))
y = 0
'for each cell in the area rngChart find the value and color the chart's
'point with the corresponding color
For Each c In rngChart
y = y + 1
With Graph.SeriesCollection(S).Points(y).Interior
'this is where I would like to be able to insert multible conditions
Select Case c.Value
Case Is < L2: .ColorIndex = C1
Case L2 To L3: .ColorIndex = C2
Case Is > L3: .ColorIndex = C3
Case Else: Exit Sub
End Select
End With
Next
End Sub
Sub Limits(Low, Hi)
L2 = Low
L3 = Hi
End Sub
Sub Colors(Color_Low, Color_Med, Color_Hi)
C1 = Color_Low
C2 = Color_Med
C3 = Color_Hi
End Sub
Property Let Serie(Number As Long)
S = Number
End Property
Property Get Serie() As Long
Serie = S
End Property
Standard module:
Option Explicit
'create new GraphClass object
Dim clsGr1 As New clsGraph '1. chart
Dim clsGr2 As New clsGraph '2. chart
Sub InitializeChart()
'choose the chart to be colored
'call this sub from Workbook_Open or manually
With clsGr1
Set .Graph = shUnd.ChartObjects(1).Chart
.Serie = 1
.Limits 3, 4
.Colors 3, 6, 4
End With
With clsGr2
Set .Graph = shUnd.ChartObjects(2).Chart
.Serie = 1
.Limits 3, 4
.Colors 3, 6, 4
End With
End Sub
TIA
Tommy Bak
I've been playing a little with the possibility to format a chart conditionally and I have got it working now.
My problem is that I would like it to be more flexible, but right now I seem to be a bit braindead and need some help :help
It should be more flexible on the numbers of conditions and corresponding colors.
Right now it's locked to 3 (bad, good, exelent)
I have made it as a classmodule, becaurse I want to include it in a couple of workbooks. As it is my first classmodule, please give some feedback.
[Edit] : Complete file is attached
Class module:
Option Explicit
'repaints charts within 2 limits in 3 different colors
Public WithEvents Graph As Chart
'Limits for coloring
Dim L2 As Long, L3 As Long
'Colors for each section
Dim C1 As Long, C2 As Long, C3 As Long
'holder for series-number
Dim S As Long
Private Sub Graph_Calculate()
Dim y As Long
Dim c As Range
Dim rngChart As Range
Dim vaGetRange
'Split the chartformula at ,
vaGetRange = Split(Graph.SeriesCollection(S).Formula, ",")
'choose the text between 2 and 3 comma;
Set rngChart = Range(vaGetRange(2))
y = 0
'for each cell in the area rngChart find the value and color the chart's
'point with the corresponding color
For Each c In rngChart
y = y + 1
With Graph.SeriesCollection(S).Points(y).Interior
'this is where I would like to be able to insert multible conditions
Select Case c.Value
Case Is < L2: .ColorIndex = C1
Case L2 To L3: .ColorIndex = C2
Case Is > L3: .ColorIndex = C3
Case Else: Exit Sub
End Select
End With
Next
End Sub
Sub Limits(Low, Hi)
L2 = Low
L3 = Hi
End Sub
Sub Colors(Color_Low, Color_Med, Color_Hi)
C1 = Color_Low
C2 = Color_Med
C3 = Color_Hi
End Sub
Property Let Serie(Number As Long)
S = Number
End Property
Property Get Serie() As Long
Serie = S
End Property
Standard module:
Option Explicit
'create new GraphClass object
Dim clsGr1 As New clsGraph '1. chart
Dim clsGr2 As New clsGraph '2. chart
Sub InitializeChart()
'choose the chart to be colored
'call this sub from Workbook_Open or manually
With clsGr1
Set .Graph = shUnd.ChartObjects(1).Chart
.Serie = 1
.Limits 3, 4
.Colors 3, 6, 4
End With
With clsGr2
Set .Graph = shUnd.ChartObjects(2).Chart
.Serie = 1
.Limits 3, 4
.Colors 3, 6, 4
End With
End Sub
TIA
Tommy Bak