Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delink all charts in worksheet
I've used a macro written by Tushar Mehta to delink charts in my
worksheet. I need to delink all worksheets and have tried adding some code to activate the charts one at a time. It seemed to work fine one time, but it's now giving me problems. Here is the code. Help me out if you can. TIA Gerry Sub DeLinkCharts() ''' Thanks to Tushar Mehta Dim ChartNm As String Dim ChtObj As ChartObject Dim mySeries As Series Dim sChtName As String ''' Make sure a chart is selected For Each ChtObj In ActiveSheet.ChartObjects ChartNm = ChtObj.Name ActiveSheet.ChartObjects(ChartNm).Activate ActiveChart.ChartArea.Select On Error Resume Next sChtName = ActiveChart.Name If Err.Number < 0 Then MsgBox "This functionality is available only for charts " _ & "or chart objects" Exit Sub End If If TypeName(Selection) = "ChartObject" Then ActiveSheet.ChartObjects(ChartNm).Activate End If On Error GoTo 0 ''' Loop through all series in active chart For Each mySeries In ActiveChart.SeriesCollection '''' Convert X and Y Values to arrays of values mySeries.XValues = mySeries.XValues mySeries.Values = mySeries.Values mySeries.Name = mySeries.Name Next mySeries Next Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delink all charts in worksheet
Don't overcomplicate it, and don't bother activating each chart.
Sub DelinkCharts() Dim sh As Object Dim chtOb As ChartObject Dim mySeries As Series ' loop through sheets For Each sh In ActiveWorkbook.Sheets ' loop through chart objects on sheet For Each chtOb In sh.ChartObjects On Error Resume Next ' loop through series in active chart For Each mySeries In ActiveChart.SeriesCollection ' Convert X and Y Values to arrays of values mySeries.XValues = mySeries.XValues mySeries.Values = mySeries.Values mySeries.Name = mySeries.Name Next mySeries Next chtOb Next sh End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Gerry" wrote in message oups.com... I've used a macro written by Tushar Mehta to delink charts in my worksheet. I need to delink all worksheets and have tried adding some code to activate the charts one at a time. It seemed to work fine one time, but it's now giving me problems. Here is the code. Help me out if you can. TIA Gerry Sub DeLinkCharts() ''' Thanks to Tushar Mehta Dim ChartNm As String Dim ChtObj As ChartObject Dim mySeries As Series Dim sChtName As String ''' Make sure a chart is selected For Each ChtObj In ActiveSheet.ChartObjects ChartNm = ChtObj.Name ActiveSheet.ChartObjects(ChartNm).Activate ActiveChart.ChartArea.Select On Error Resume Next sChtName = ActiveChart.Name If Err.Number < 0 Then MsgBox "This functionality is available only for charts " _ & "or chart objects" Exit Sub End If If TypeName(Selection) = "ChartObject" Then ActiveSheet.ChartObjects(ChartNm).Activate End If On Error GoTo 0 ''' Loop through all series in active chart For Each mySeries In ActiveChart.SeriesCollection '''' Convert X and Y Values to arrays of values mySeries.XValues = mySeries.XValues mySeries.Values = mySeries.Values mySeries.Name = mySeries.Name Next mySeries Next Range("A1").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delink all charts in worksheet
On Apr 15, 9:01 pm, "Jon Peltier"
wrote: Don't overcomplicate it, and don't bother activating each chart. Sub DelinkCharts() Dim sh As Object Dim chtOb As ChartObject Dim mySeries As Series ' loop through sheets For Each sh In ActiveWorkbook.Sheets ' loop through chart objects on sheet For Each chtOb In sh.ChartObjects On Error Resume Next ' loop through series in active chart For Each mySeries In ActiveChart.SeriesCollection ' Convert X and Y Values to arrays of values mySeries.XValues = mySeries.XValues mySeries.Values = mySeries.Values mySeries.Name = mySeries.Name Next mySeries Next chtOb Next sh End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutionshttp://PeltierTech.com _______ "Gerry" wrote in message oups.com... I've used a macro written by Tushar Mehta todelinkcharts in my worksheet. I need todelinkall worksheets and have tried adding some code to activate the charts one at a time. It seemed to work fine one time, but it's now giving me problems. Here is the code. Help me out if you can. TIA Gerry Sub DeLinkCharts() ''' Thanks to Tushar Mehta Dim ChartNm As String Dim ChtObj As ChartObject Dim mySeries As Series Dim sChtName As String ''' Make sure a chart is selected For Each ChtObj In ActiveSheet.ChartObjects ChartNm = ChtObj.Name ActiveSheet.ChartObjects(ChartNm).Activate ActiveChart.ChartArea.Select On Error Resume Next sChtName = ActiveChart.Name If Err.Number < 0 Then MsgBox "This functionality is available only for charts " _ & "or chart objects" Exit Sub End If If TypeName(Selection) = "ChartObject" Then ActiveSheet.ChartObjects(ChartNm).Activate End If On Error GoTo 0 ''' Loop through all series in active chart For Each mySeries In ActiveChart.SeriesCollection '''' Convert X and Y Values to arrays of values mySeries.XValues = mySeries.XValues mySeries.Values = mySeries.Values mySeries.Name = mySeries.Name Next mySeries Next Range("A1").Select End Sub Hi Jon Thanks for the response. I tried your macro and nothing seems to work unless I have a chart selected. Then it only delinks that chart only. Gerry |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delink all charts in worksheet
While anything's possible it is highly unlikely I wrote that code. The
style is very unlike mine, and the number of selects and activates is something I haven't done for *at least* a decade. The below is lightly tested. Run delinkChartsActiveSheet to delink all charts in the active sheet and DelinkChartsActiveWorkbook to delink all charts in all sheets in the active workbook. Option Explicit Private Sub delinkOneChart(aChart As Chart) Dim aSeries As Series 'On Error Resume Next For Each aSeries In aChart.SeriesCollection With aSeries .XValues = .XValues .Values = .Values .Name = .Name 'a bubble chart has one more element. BubbleSize? End With Next aSeries End Sub Private Sub delinkChartsOneSheet(aSheet As Object) Dim ChartObj As ChartObject If TypeOf aSheet Is Chart Then delinkOneChart aSheet For Each ChartObj In aSheet.ChartObjects delinkOneChart ChartObj.Chart Next ChartObj End Sub Public Sub delinkChartsActiveSheet() delinkChartsOneSheet ActiveSheet End Sub Public Sub DelinkChartsActiveWorkbook() Dim aSheet As Object For Each aSheet In ActiveWorkbook.Sheets delinkChartsOneSheet aSheet Next aSheet End Sub In article .com, says... I've used a macro written by Tushar Mehta to delink charts in my worksheet. I need to delink all worksheets and have tried adding some code to activate the charts one at a time. It seemed to work fine one time, but it's now giving me problems. Here is the code. Help me out if you can. {snip} |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Bar Charts from 1 Worksheet | Charts and Charting in Excel | |||
Can you tile 8 charts on a worksheet? | Charts and Charting in Excel | |||
How do I place two or more charts on a worksheet? | Charts and Charting in Excel | |||
Delete ALL Charts in a WorkSheet (VB) | Charts and Charting in Excel | |||
List Charts in a Worksheet | Excel Programming |