Microsoft Office Tutorials and References
In Depth Information
Renaming a Chart
To delete chart sheets, loop through each sheet starting with the last sheet, determine whether the
sheet is a chart sheet, and if so, delete it.
This loop starts from the last sheet and moves backward using the Step -1
statement. It’s a wise practice to loop backwards when deleting sheets, rows, or
columns. Behind the scenes, VBA relies on the counts of objects in collections,
and where the objects are located relative to the others. Deleting objects starting
at the end and working your way to the beginning keeps VBA’s management of
those objects in order.
Sub DeleteChartSheets()
‘Turn off ScreenUpdating and the Alerts feature,
‘so when you delete a sheet VBA does not warn you.
With Application
.ScreenUpdating = False
.DisplayAlerts = False
‘Declare an object variable for the Sheets collection.
Dim objSheet As Object
‘Loop through all sheets, only looking for a chart sheet.
‘This loop starts from the last sheet and moves backward.
For Each objSheet In ActiveWorkbook.Sheets
If TypeOf objSheet Is Excel.Chart Then objSheet.Delete
Next objSheet
‘Turn on ScreenUpdating and DisplayAlerts.
.DisplayAlerts = True
.ScreenUpdating = False
End With
End Sub
rEnAMing A cHArT
As you have surely noticed when creating objects such as charts, pivot tables, or drawing objects,
Excel has a reined knack for giving those objects the blandest default names imaginable. Suppose
you have three embedded charts on your worksheet. The following macro will change those charts’
names to something more meaningful:
Sub RenameCharts()
With ActiveSheet
.ChartObjects(1).Name = “Monthly Income”
.ChartObjects(2).Name = “Monthly Expense”
.ChartObjects(3).Name = “Net Profit”
End With
End Sub
Search JabSto ::

Custom Search