Workbook Property or Method

How do I get the Workbook name associated with a range?

Function whereisit(r As Range) As String
MsgBox (r.Address)
MsgBox (r.Worksheet.Name)
whereisit = r.Address & r.Worksheet.Name
End Function

I need the workbook name as well as the Address and worksheet name.

Thanks in advance
--
Gary''s Student

Hi,

You can make use of the Parent property. The example show it's use on
both the .Worksheet and Range object.

Function whereisit(r As Range) As String
MsgBox (r.Address)
MsgBox (r.Worksheet.Name)
MsgBox (r.Worksheet.Parent.Name)
whereisit = r.Address & r.Worksheet.Name & r.Parent.Parent.Name
End Function

Cheers
Andy

Gary''s Student wrote:
> How do I get the Workbook name associated with a range?
>
> Function whereisit(r As Range) As String
> MsgBox (r.Address)
> MsgBox (r.Worksheet.Name)
> whereisit = r.Address & r.Worksheet.Name
> End Function
>
> I need the workbook name as well as the Address and worksheet name.
>
> Thanks in advance

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Gary,

Try r.Parent.Parent.Name


---
Regards,
Norman



"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> How do I get the Workbook name associated with a range?
>
> Function whereisit(r As Range) As String
> MsgBox (r.Address)
> MsgBox (r.Worksheet.Name)
> whereisit = r.Address & r.Worksheet.Name
> End Function
>
> I need the workbook name as well as the Address and worksheet name.
>
> Thanks in advance
> --
> Gary''s Student

Thank you both very much.
--
Gary''s Student


"Norman Jones" wrote:

> Hi Gary,
>
> Try r.Parent.Parent.Name
>
>
> ---
> Regards,
> Norman
>
>
>
> "Gary''s Student" <[email protected]> wrote in message
> news:[email protected]...
> > How do I get the Workbook name associated with a range?
> >
> > Function whereisit(r As Range) As String
> > MsgBox (r.Address)
> > MsgBox (r.Worksheet.Name)
> > whereisit = r.Address & r.Worksheet.Name
> > End Function
> >
> > I need the workbook name as well as the Address and worksheet name.
> >
> > Thanks in advance
> > --
> > Gary''s Student
>
>
>