Tuesday, September 29, 2020

VBA code to show list of chart in a workbook

Chart is very powerful object in excel to represent numbers in graphical view. while creating bussiness we deals with various charts. Create multiple charts in the report. when report is very huge then it's quite difficult to locate the charts in the report. and it will be very time cousuming when we need to look charts manually sheet of sheet. However, We can make this task very easy using a short piece of VBA code which will help us to locate chart in each worksheet in fraction of minute.

Lets see how can we locate each chart in workboos using VBA code in your reporting project. Using  macor to list chart will certainly save a huge amount of time while locating the chart object in report.

Here is the code to list eacht charts in Workbook:

Sub ListChart()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

'Define variables
Dim i As Integer
Dim j As Integer
Dim nChart As String

nChart = ""

'Loop through each worksheet
For i = 1 To ThisWorkbook.Sheets.Count
    'Loop through each chart in active worksheet
    For j = 1 To ActiveSheet.ChartObjects.Count
        nChart = nChart & "," & vbNewLine & ActiveSheet.ChartObjects(j).Name & " is exist on worksheet " & ActiveSheet.Name
    Next j
Next i
    'Message box to show name of charts
    MsgBox nChart, vbInformation, "List of chart"

End Sub

No comments: