|
Chart manipulation in VBA
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. |
Chart manipulation in VBA
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. "Don Guillett" wrote: 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 "Wahine" wrote in message ... 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. |
| All times are GMT +1. The time now is 10:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com