Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Chart Sheets in workbook
I am trying to loop through the sheets in a workbook and test to see if the
which sheets are chart sheets. The code is failing is this because of the way I have set up the wrksht variable (i.e. dim as worksheet)? Function IsChartSheet() As Boolean Dim wrksht As Worksheet Dim chrtsht As Chart On Error Resume Next For Each wrksht In ThisWorkbook Set chrtsht = ActiveWorkbook.Charts(wrksht.Name) If Err = 0 Then IsChartSheet = True Debug.Print ActiveSheet.Name Debug.Print "This is a chart sheet!" Else IsChartSheet = False Debug.Print ActiveSheet.Name Debug.Print "This is not a chart sheet!" End If On Error GoTo 0 Set chrtsht = Nothing Next End Function Thanks EM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Chart Sheets in workbook
Worksheets can't be chart sheets.
maybe you could use: Dim sht as Object for each sht in thisworkbook.sheets msgbox typename(sht) next sht So you can test it for: if typename(sht) = "Chart" then 'it's a chart sheet. end if Be careful. There are other types of sheets besides worksheets and chart sheets. ExcelMonkey wrote: I am trying to loop through the sheets in a workbook and test to see if the which sheets are chart sheets. The code is failing is this because of the way I have set up the wrksht variable (i.e. dim as worksheet)? Function IsChartSheet() As Boolean Dim wrksht As Worksheet Dim chrtsht As Chart On Error Resume Next For Each wrksht In ThisWorkbook Set chrtsht = ActiveWorkbook.Charts(wrksht.Name) If Err = 0 Then IsChartSheet = True Debug.Print ActiveSheet.Name Debug.Print "This is a chart sheet!" Else IsChartSheet = False Debug.Print ActiveSheet.Name Debug.Print "This is not a chart sheet!" End If On Error GoTo 0 Set chrtsht = Nothing Next End Function Thanks EM -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Chart Sheets in workbook
If you wanted to get the chart sheets, you could use this:
Dim Cht as Chart For Each Cht In ActiveWorkbook.Charts ' Cht is a chart sheet Next - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Dave Peterson" wrote in message ... Worksheets can't be chart sheets. maybe you could use: Dim sht as Object for each sht in thisworkbook.sheets msgbox typename(sht) next sht So you can test it for: if typename(sht) = "Chart" then 'it's a chart sheet. end if Be careful. There are other types of sheets besides worksheets and chart sheets. ExcelMonkey wrote: I am trying to loop through the sheets in a workbook and test to see if the which sheets are chart sheets. The code is failing is this because of the way I have set up the wrksht variable (i.e. dim as worksheet)? Function IsChartSheet() As Boolean Dim wrksht As Worksheet Dim chrtsht As Chart On Error Resume Next For Each wrksht In ThisWorkbook Set chrtsht = ActiveWorkbook.Charts(wrksht.Name) If Err = 0 Then IsChartSheet = True Debug.Print ActiveSheet.Name Debug.Print "This is a chart sheet!" Else IsChartSheet = False Debug.Print ActiveSheet.Name Debug.Print "This is not a chart sheet!" End If On Error GoTo 0 Set chrtsht = Nothing Next End Function Thanks EM -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy chart between multiple sheets in workbook | Charts and Charting in Excel | |||
Find exact match, same workbook, different sheets | Excel Discussion (Misc queries) | |||
Find from just certain sheets in a workbook | Excel Programming | |||
Find / Replace sheets vs workbook in VB | Excel Worksheet Functions | |||
How to find the type of Sheet in Excel.Workbook.sheets collection | Excel Programming |