SheetName Function

[EXCEL$B!!(BNEWS]

here is my function

Function SheetName(x) As String '
Application.Volatile
SheetName = ActiveSheet.name
End Function '

what i want to do is to get the sheet name in in which a cell (like
=SheetName(0)) is in.

not the. name of ActiveSheet.

what should i do.

[Haldun Alay]

Function SheetName(x) As String
Application.Volatile True
If x <= 0 Then
SheetName = "Worksheet number must be greater than 0"
Exit Function
End If
With Application.Caller.Parent.Parent
If x > .Worksheets.Count Then
SheetName = "Warning: There is no worksheet"
Else
SheetName = .Worksheets(x).Name
End If
End With
End Function


--
Haldun Alay
"EXCEL$B!!(BNEWS" <youngmanca@hotmail.com>, haber iletisinde ?unlar? yazd?:OJZtb$FYGHA.3532@TK2MSFTNGP05.phx.gbl...
here is my function

Function SheetName(x) As String '
Application.Volatile
SheetName = ActiveSheet.name
End Function '

what i want to do is to get the sheet name in in which a cell (like
=SheetName(0)) is in.

not the. name of ActiveSheet.

what should i do.

[Andrew B]

Hi
Not sure what you want - do you want to find the cell that contains the
sheet name ?


[C3] = activesheet.name will put the sheet name into any cell you
specify, lik C3. More info needed....

Andrew B




EXCEL$B!!(BNEWS wrote:
> here is my function
>
> Function SheetName(x) As String '
> Application.Volatile
> SheetName = ActiveSheet.name
> End Function '
>
> what i want to do is to get the sheet name in in which a cell (like
> =SheetName(0)) is in.
>
> not the. name of ActiveSheet.
>
> what should i do.
>
>
>
>
>

[Edwin Tam]

I'm not sure what the variable "x" is for in your example.
But try this:

Function SHEETNAME() As String
Dim s As Object
Application.Volatile
Set s = Application.Caller
SHEETNAME = s.Parent.Name
End Function

You type in a cell:
=SHEETNAME()

The formula will return the sheetname of the sheet where you typed the
formula.

Regards,
Edwin Tam
edwintam@vonixx.com
http://www.vonixx.com


"EXCEL　NEWS" wrote:

> here is my function
>
> Function SheetName(x) As String '
> Application.Volatile
> SheetName = ActiveSheet.name
> End Function '
>
> what i want to do is to get the sheet name in in which a cell (like
> =SheetName(0)) is in.
>
> not the. name of ActiveSheet.
>
> what should i do.
>
>
>
>
>
>

[EXCEL NEWS]

thanks　to　all of you.
i have thought it out ,like Mr Edwin Tam suggested to me,
i will do better with his advice
thanks


"Edwin Tam" <EdwinTam@discussions.microsoft.com> wrote in message
news:60BA8ACA-9B03-42BA-9D00-D1EA39E073D8@microsoft.com...
> I'm not sure what the variable "x" is for in your example.
> But try this:
>
> Function SHEETNAME() As String
> Dim s As Object
> Application.Volatile
> Set s = Application.Caller
> SHEETNAME = s.Parent.Name
> End Function
>
> You type in a cell:
> =SHEETNAME()
>
> The formula will return the sheetname of the sheet where you typed the
> formula.
>
> Regards,
> Edwin Tam
> edwintam@vonixx.com
> http://www.vonixx.com
>
>
> "EXCEL　NEWS" wrote:
>
> > here is my function
> >
> > Function SheetName(x) As String '
> > Application.Volatile
> > SheetName = ActiveSheet.name
> > End Function '
> >
> > what i want to do is to get the sheet name in in which a cell (like
> > =SheetName(0)) is in.
> >
> > not the. name of ActiveSheet.
> >
> > what should i do.
> >
> >
> >
> >
> >
> >

[Ardus Petus]

Maybe someting like this:

"------------------------------
Function sheetname(rng As Range) As String
sheetname = rng.Worksheet.Name
End Function
'------------------------------

HTH
--
AP

"EXCEL$B!!(BNEWS" <youngmanca@hotmail.com> a ecrit dans le message de
news:OJZtb$FYGHA.3532@TK2MSFTNGP05.phx.gbl...
> here is my function
>
> Function SheetName(x) As String '
> Application.Volatile
> SheetName = ActiveSheet.name
> End Function '
>
> what i want to do is to get the sheet name in in which a cell (like
> =SheetName(0)) is in.
>
> not the. name of ActiveSheet.
>
> what should i do.
>
>
>
>
>

[Randy Harmelink]

Try this formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

However, it will only work in sheets that have been saved, because
"filename" is an attribute that is set when the file is saved.

That came from:

http://www.ozgrid.com/VBA/return-sheet-name.htm