Chart manipulation in VBA

[Wahine]

I have a complicated problem.

I have 4 charts July, August, Sept, Oct displayed on the same worksheet,
which show sales performance. The x-axis is $, and the Y axis is the stage
that sales have reached, eg. stage 1, stage 2, stage 3, stage 4 and stage 5.
Example - If in July there are no sales at stage 1, I want the lines on the
chart to begin at Stage 2.

I have successfully managed this with the following ugly sub (I am new to
VBA). [PS: the values the charts are calculated on are Sheet1 K2:K24.
The subs called in the body of the sub below, simply refer to points from
the lines on graphs, and make them invisible, Or vice versa]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error Resume Next
Set rng = Intersect(Target, Range("K2:K24").Precedents)
On Error GoTo 0
If Not rng Is Nothing Then

If Worksheets("Sheet 1").Range("K2").Value = 0 Then
Call HideStage1LinesFromJulyChart
ElseIf Worksheets("Sheet 1").Range("K2").Value > 0 Then
Call ShowStage1LinesJulyChart
End If

If Worksheets("Sheet 1").Range("K9").Value = 0 Then
Call HideStage1LinesFromAugustChart
ElseIf Worksheets("Sheet 1").Range("K9").Value > 0 Then
Call ShowStage1LinesAugustChart
End If

If Worksheets("Sheet 1").Range("K16").Value = 0 Then
Call HideStage1LinesFromSeptemberChart
ElseIf Worksheets("Sheet 1").Range("K16").Value > 0 Then
Call ShowStage1LinesSeptemberChart
End If

If Worksheets("Sheet 1").Range("K23").Value = 0 Then
Call HideStage1LinesFromOctoberChart
ElseIf Worksheets("Sheet1").Range("K23").Value > 0 Then
Call ShowStage1LinesFromOctoberChart
End If
End If

End Sub

This works well, however the problem I now have is. Sometimes, there are no
sales in Stage 1, Stage 2 or Stage 3. In which case I need the lines on the
chart to start at Stage 4.

The algorithim is as follows:
For July
If
Stage1 value =0 AND Stage 2 value >0 AND Stage 3 value > 0
then
make stage 1 invisible
Else IF
Stage1 value =0 AND Stage 2 value =0 AND Stage 3 value > 0
then
make stage 1 and stage 2 invisible
Else IF
Stage1 value =0 AND Stage 2 value =0 AND Stage 3 value = 0
then
make stage 1, 2 and 3 invisible
Else make all stages visible.
and same for August, September and October...

I am sure this must be possible. Trouble is that I am trying to work with
lots of IF statements, because that is about the limit of my ability.

Thank you in advance for any help.

 

[Don Guillett]

It might be simpler to show all columns at the first and then use this idea

Sub hidecol()
Range("a1:d1").EntireColumn.Hidden = False 'show all
If Range("g1") = 0 Then Columns(1).Hidden = True
If Range("g2") = 0 Then Columns(2).Hidden = True
End Sub

 

[Wahine]

Hi, thanks,

I have tried that way too, which also works. But still faced with the
problem if July is not 0, but August is 0, then the line on the chart has a
gap in it. I only want the zero's hidden, if the preceeding values are also
zero.

It might be simpler to show all columns at the first and then use this idea

Sub hidecol()
Range("a1:d1").EntireColumn.Hidden = False 'show all
If Range("g1") = 0 Then Columns(1).Hidden = True
If Range("g2") = 0 Then Columns(2).Hidden = True
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

I have a complicated problem.

I have 4 charts July, August, Sept, Oct displayed on the same worksheet,
which show sales performance. The x-axis is $, and the Y axis is the
stage
that sales have reached, eg. stage 1, stage 2, stage 3, stage 4 and stage
5.
Example - If in July there are no sales at stage 1, I want the lines on
the
chart to begin at Stage 2.

I have successfully managed this with the following ugly sub (I am new to
VBA). [PS: the values the charts are calculated on are Sheet1 K2:K24.
The subs called in the body of the sub below, simply refer to points from
the lines on graphs, and make them invisible, Or vice versa]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error Resume Next
Set rng = Intersect(Target, Range("K2:K24").Precedents)
On Error GoTo 0
If Not rng Is Nothing Then

If Worksheets("Sheet 1").Range("K2").Value = 0 Then
Call HideStage1LinesFromJulyChart
ElseIf Worksheets("Sheet 1").Range("K2").Value > 0 Then
Call ShowStage1LinesJulyChart
End If

If Worksheets("Sheet 1").Range("K9").Value = 0 Then
Call HideStage1LinesFromAugustChart
ElseIf Worksheets("Sheet 1").Range("K9").Value > 0 Then
Call ShowStage1LinesAugustChart
End If

If Worksheets("Sheet 1").Range("K16").Value = 0 Then
Call HideStage1LinesFromSeptemberChart
ElseIf Worksheets("Sheet 1").Range("K16").Value > 0 Then
Call ShowStage1LinesSeptemberChart
End If

If Worksheets("Sheet 1").Range("K23").Value = 0 Then
Call HideStage1LinesFromOctoberChart
ElseIf Worksheets("Sheet1").Range("K23").Value > 0 Then
Call ShowStage1LinesFromOctoberChart
End If
End If

End Sub

This works well, however the problem I now have is. Sometimes, there are
no
sales in Stage 1, Stage 2 or Stage 3. In which case I need the lines on
the
chart to start at Stage 4.

The algorithim is as follows:
For July
If
Stage1 value =0 AND Stage 2 value >0 AND Stage 3 value > 0
then
make stage 1 invisible
Else IF
Stage1 value =0 AND Stage 2 value =0 AND Stage 3 value > 0
then
make stage 1 and stage 2 invisible
Else IF
Stage1 value =0 AND Stage 2 value =0 AND Stage 3 value = 0
then
make stage 1, 2 and 3 invisible
Else make all stages visible.
and same for August, September and October...

I am sure this must be possible. Trouble is that I am trying to work with
lots of IF statements, because that is about the limit of my ability.

Thank you in advance for any help.