Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't record action on a chart
I am recording a macro to remove the fill from a chart (excel 2008).
There are 12 rwos of bars in the chart and so I want to remove the fills from each bar one as below (series 1 is left alone); problem is that the macro does not record it when I remove the fill (set it to no fill) using the format command, does anyone know why the macro is ignoring the action? Thanks and regards, Mark Sub RemoveFills() ' Dim i As Integer ' For i = 2 To 12 ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(i).Select CodeToRemoveTheFillFromThe Selection Next i End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't record action on a chart
Try the following -
Sub test() Dim cht As Chart Dim bSeriesVis As Boolean Set cht = ActiveSheet.ChartObjects("Chart 1").Chart bSeriesVis = False ' True ' toggle SeriesVisible cht, bSeriesVis End Sub Sub SeriesVisible(cht As Chart, bVis As Boolean) Dim nFill As Long, nBdrStyle As Long Dim i As Long Dim sr As Series If bVis Then nFill = xlAutomatic nBdrStyle = xlContinuous Else nFill = xlNone nBdrStyle = xlNone End If For i = 2 To cht.SeriesCollection.Count With cht.SeriesCollection(i) .Interior.ColorIndex = nFill .Border.LineStyle = nBdrStyle ' ? End With Next End Sub Regards, Peter T wrote in message ... I am recording a macro to remove the fill from a chart (excel 2008). There are 12 rwos of bars in the chart and so I want to remove the fills from each bar one as below (series 1 is left alone); problem is that the macro does not record it when I remove the fill (set it to no fill) using the format command, does anyone know why the macro is ignoring the action? Thanks and regards, Mark Sub RemoveFills() ' Dim i As Integer ' For i = 2 To 12 ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(i).Select CodeToRemoveTheFillFromThe Selection Next i End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't record action on a chart
On May 21, 7:35*pm, "Peter T" <peter_t@discussions wrote:
Try the following - Sub test() Dim cht As Chart Dim bSeriesVis As Boolean Set cht = ActiveSheet.ChartObjects("Chart 1").Chart bSeriesVis = False ' True ' toggle SeriesVisible cht, bSeriesVis End Sub Sub SeriesVisible(cht As Chart, bVis As Boolean) Dim nFill As Long, nBdrStyle As Long Dim i As Long Dim sr As Series * * If bVis Then * * * * nFill = xlAutomatic * * * * nBdrStyle = xlContinuous * * Else * * * * nFill = xlNone * * * * nBdrStyle = xlNone * * End If * * For i = 2 To cht.SeriesCollection.Count * * * * With cht.SeriesCollection(i) * * * * * * .Interior.ColorIndex = nFill * * * * * * .Border.LineStyle = nBdrStyle ' ? * * * * End With * * Next End Sub Regards, Peter T wrote in message ... I am recording amacroto remove the fill from a chart (excel 2008). There are 12 rwos of bars in the chart and so I want to remove the fills from each bar one as below (series 1 is left alone); problem is that themacrodoes not record it when I remove the fill (set it to no fill) using the format command, does anyone know why themacrois ignoring the action? Thanks and regards, Mark Sub RemoveFills() ' Dim i As Integer ' For i = 2 To 12 ' * * ActiveSheet.ChartObjects("Chart 1").Activate * * ActiveChart.SeriesCollection(i).Select * * CodeToRemoveTheFillFromThe Selection Next i End Sub- Hide quoted text - - Show quoted text - Thanks for that Peter, unfortunately I 'fell at the first fence' it wouldn't select the chart! I didn't mention thatt the chart is not embedded but in a chart sheet but that shouldn't matter should it? I recorded a macro and selected the chart and it wrote the code: ActiveSheet.ChartObjects("Chart 1").Activate So I was kind of surpirsed when the code came up with the message that the item was not found. There seems to be a difference between the code when it is recorded from a macro and when it is written which seems wrong to me, if the object oroducres the code in the macro when selected why on earth will it not select when it is run back, I am somewhat nonplussed! I was pretty proficient at programming excel 2003 but the latest version seems to be a lot less user friendly am I right? Thanks and regards, Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't record action on a chart
wrote in message ... On May 21, 7:35 pm, "Peter T" <peter_t@discussions wrote: Try the following - Sub test() Dim cht As Chart Dim bSeriesVis As Boolean Set cht = ActiveSheet.ChartObjects("Chart 1").Chart bSeriesVis = False ' True ' toggle SeriesVisible cht, bSeriesVis End Sub Sub SeriesVisible(cht As Chart, bVis As Boolean) Dim nFill As Long, nBdrStyle As Long Dim i As Long Dim sr As Series If bVis Then nFill = xlAutomatic nBdrStyle = xlContinuous Else nFill = xlNone nBdrStyle = xlNone End If For i = 2 To cht.SeriesCollection.Count With cht.SeriesCollection(i) .Interior.ColorIndex = nFill .Border.LineStyle = nBdrStyle ' ? End With Next End Sub Regards, Peter T wrote in message ... I am recording amacroto remove the fill from a chart (excel 2008). There are 12 rwos of bars in the chart and so I want to remove the fills from each bar one as below (series 1 is left alone); problem is that themacrodoes not record it when I remove the fill (set it to no fill) using the format command, does anyone know why themacrois ignoring the action? Thanks and regards, Mark Sub RemoveFills() ' Dim i As Integer ' For i = 2 To 12 ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(i).Select CodeToRemoveTheFillFromThe Selection Next i End Sub- Hide quoted text - - Show quoted text - Thanks for that Peter, unfortunately I 'fell at the first fence' it wouldn't select the chart! I didn't mention thatt the chart is not embedded but in a chart sheet but that shouldn't matter should it? I recorded a macro and selected the chart and it wrote the code: ActiveSheet.ChartObjects("Chart 1").Activate So I was kind of surpirsed when the code came up with the message that the item was not found. There seems to be a difference between the code when it is recorded from a macro and when it is written which seems wrong to me, if the object oroducres the code in the macro when selected why on earth will it not select when it is run back, I am somewhat nonplussed! I was pretty proficient at programming excel 2003 but the latest version seems to be a lot less user friendly am I right? Thanks and regards, Mark ======================================= Hi Mark, It would matter very much indeed whether the chart is embedded on a sheet or a is chart sheet. If it's a chart sheet, as you now say, this cannot possibly work (unless you have an embedded chart on a chart sheet)- ActiveSheet.ChartObjects("Chart 1").Activate Yet you say the above is what you got when you recorded a macro, doesn't make sense. Afraid the information you are giving is highly contradictory. In the first of the two procedures I posted change Set cht = ActiveSheet.ChartObjects("Chart 1").Chart to Set cht = ActiveChart Select the chart you want to process and run the code (ie run Test so that it it turn calls SeriesVisible cht, bSeriesVis). Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excell copy action pauses for 15 second for the smallest action | Excel Discussion (Misc queries) | |||
Record macro doesn't record shape properties | Excel Programming | |||
on particular action run a macro | Excel Discussion (Misc queries) | |||
Record Macro - Record custom user actions | Excel Programming |