Find if a worksheet or chart is active

[callbr549]

I wanted to write a macro that would put a certain custom footer on either a
worksheet or a chart. I discovered that what I recorded for one wouldn't work
for the other. I thought I would go into VBA and edit the sub to do one thing
if a chart was active and another if a worksheet was active, but I can't
figure out how to use VBA to determine what type of object is active. Its
probably simple, but I haven't found it yet.

 

[jaf]

Hi,

Sub myTest()
Dim mySheet As Object

Set mySheet = ThisWorkbook.ActiveSheet
If TypeOf mySheet Is Chart Then SheetType = "CH"
'If TypeOf mySheet Is Chart Then ChartFooter

If TypeOf mySheet Is Worksheet Then SheetType = "WS"
''If TypeOf mySheet Is Chart Then SheetFooter

Debug.Print SheetType, mySheet.Name

End Sub


John

 

[callbr549]

I recorded 2 macros, one for Worksheet footers, one for Chart footers and
then called them from this:

Sub MyFooter()
' Keyboard Shortcut: Ctrl+Shift+F

Dim MySheet As Object
Set MySheet = ThisWorkbook.ActiveSheet
If TypeOf MySheet Is Worksheet Then SheetType = "WS"
If TypeOf MySheet Is Chart Then SheetType = "CH"

If SheetType = "WS" Then
MyWorksheetFooter
Else: MyChartFooter

End If
End Sub

It runs fine when I step through or if I run the macro by doing
Tools|Macro|Macros|(select)|Run. The keyboard shortcut I use works for
worksheets but not for charts. When I use the keyboard shortcut on a chart,
it ends up getting sent to the MyWorksheetFooter sub instead of MyChartFooter
and then crashes. Any idea why the keyboard shortcut wouldn't work for charts?

 

[jaf]

Hi,
No Idea. Excel may be getting confused because it has a native Ctrl+Shift+F function which apparently does nothing on a chart sheet.
http://www.cpearson.com/excel/keyboardShortcuts.htm

You may want to try a combination not native.


John