How to get the sheet name from Application.Caller?

Question

It is possible to get the calling workbook name Application.Caller.Worksheet.Parent.Name and cell address using Application.Caller.Address, for a function called in Excel.

Is there a way to get the sheet name, e.g. Sheet1, using Application.Caller?

Mudfish

Answer 1

you pretty much had it already

Application.Caller.Worksheet.Name

Vespertilionine

Not working in VBA 7.0 not evening returning as a variable. The caller is a checkbox. – Advantageous

Answer 2

Dim called_by As Variant
Dim x as string
Set called_by = Application.Caller
x = called_by.Parent.Application.Activesheet.Name

works fine in my application when calling this function within a cell.

Mayfair

Answer 3

I know this is old, but application.caller represents what is happening in the active sheet. So why not simply Dim Mysheet as string and then use the code as follows...

Mysheet = activesheet.name

or if you want both the item that called the macro (Application.Caller) and the sheet name, then as follows:

Dim myStr As String, myShtNm As String
myShtNm = ActiveSheet.Name
myStr = Application.Caller

Conlan