Hi

I need to return the active sheet name in a cell on another sheet. I guess
the best way of doing this would be to use a UDF, but I don't know how to set
this up.

For example on a sheet called Names in cell A1, I would like to always
return the name of the currently active sheet. The Names sheet is hidden,
when in general use.

Thanks in advance
Richard

Put this in the hidden sheet cell

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

and just make sure that you force a book calculation before reading it in
VBA.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Richard" <(E-Mail Removed)> wrote in message
news:2DD878F1-81B4-48BD-B738-(E-Mail Removed)...
> Hi
>
> I need to return the active sheet name in a cell on another sheet. I
> guess
> the best way of doing this would be to use a UDF, but I don't know how to
> set
> this up.
>
> For example on a sheet called Names in cell A1, I would like to always
> return the name of the currently active sheet. The Names sheet is hidden,
> when in general use.
>
> Thanks in advance
> Richard

Hi,

You can't confirm this works by looking because as soon as you do then names
become the active sheet and A1 containes that name so an additional line is
included in this code to confirm it which you can delete.

Alt +F11 to open VB editor double click 'This workbook' and paste this in:-

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sheets("Names").Range("A1").Value = ActiveSheet.Name
MsgBox Sheets("Names").Range("A1").Value
End Sub

Mike

"Richard" wrote:

> Hi
>
> I need to return the active sheet name in a cell on another sheet. I guess
> the best way of doing this would be to use a UDF, but I don't know how to set
> this up.
>
> For example on a sheet called Names in cell A1, I would like to always
> return the name of the currently active sheet. The Names sheet is hidden,
> when in general use.
>
> Thanks in advance
> Richard

BTW, why can't you just pick up Activesheet.Name?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Put this in the hidden sheet cell
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)
>
> and just make sure that you force a book calculation before reading it in
> VBA.
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
>
>
> "Richard" <(E-Mail Removed)> wrote in message
> news:2DD878F1-81B4-48BD-B738-(E-Mail Removed)...
>> Hi
>>
>> I need to return the active sheet name in a cell on another sheet. I
>> guess
>> the best way of doing this would be to use a UDF, but I don't know how to
>> set
>> this up.
>>
>> For example on a sheet called Names in cell A1, I would like to always
>> return the name of the currently active sheet. The Names sheet is
>> hidden,
>> when in general use.
>>
>> Thanks in advance
>> Richard
>
>

I am using the active sheet name in conjuction with the indirect function as
part of a vlookup to create a list of email addresses, so that the code can
populate the To: field in outlook. I therefore needed something in the
worksheet and not the code, as this is I think easier to manage as the steps
are clearer in the spreadsheet and also makes the code easier for a begginer
in VBA.

It might well be a "round the houses" approach, but it works

Thanks for all the help

"Bob Phillips" wrote:

> BTW, why can't you just pick up Activesheet.Name?
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
>
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Put this in the hidden sheet cell
> >
> > =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)
> >
> > and just make sure that you force a book calculation before reading it in
> > VBA.
> >
> > --
> > ---
> > HTH
> >
> > Bob
> >
> >
> > (there's no email, no snail mail, but somewhere should be gmail in my
> > addy)
> >
> >
> >
> > "Richard" <(E-Mail Removed)> wrote in message
> > news:2DD878F1-81B4-48BD-B738-(E-Mail Removed)...
> >> Hi
> >>
> >> I need to return the active sheet name in a cell on another sheet. I
> >> guess
> >> the best way of doing this would be to use a UDF, but I don't know how to
> >> set
> >> this up.
> >>
> >> For example on a sheet called Names in cell A1, I would like to always
> >> return the name of the currently active sheet. The Names sheet is
> >> hidden,
> >> when in general use.
> >>
> >> Thanks in advance
> >> Richard
> >
> >
>
>
>

I think what I gave you earlier will work okay in Excel, the recalculate
should get forced by the function. But won't the value keep switching as you
switch sheets>

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Richard" <(E-Mail Removed)> wrote in message
news:CB6777F7-2E1F-4C85-A8CF-(E-Mail Removed)...
>I am using the active sheet name in conjuction with the indirect function
>as
> part of a vlookup to create a list of email addresses, so that the code
> can
> populate the To: field in outlook. I therefore needed something in the
> worksheet and not the code, as this is I think easier to manage as the
> steps
> are clearer in the spreadsheet and also makes the code easier for a
> begginer
> in VBA.
>
> It might well be a "round the houses" approach, but it works
>
> Thanks for all the help
>
> "Bob Phillips" wrote:
>
>> BTW, why can't you just pick up Activesheet.Name?
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>>
>>
>> "Bob Phillips" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Put this in the hidden sheet cell
>> >
>> > =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)
>> >
>> > and just make sure that you force a book calculation before reading it
>> > in
>> > VBA.
>> >
>> > --
>> > ---
>> > HTH
>> >
>> > Bob
>> >
>> >
>> > (there's no email, no snail mail, but somewhere should be gmail in my
>> > addy)
>> >
>> >
>> >
>> > "Richard" <(E-Mail Removed)> wrote in message
>> > news:2DD878F1-81B4-48BD-B738-(E-Mail Removed)...
>> >> Hi
>> >>
>> >> I need to return the active sheet name in a cell on another sheet. I
>> >> guess
>> >> the best way of doing this would be to use a UDF, but I don't know how
>> >> to
>> >> set
>> >> this up.
>> >>
>> >> For example on a sheet called Names in cell A1, I would like to always
>> >> return the name of the currently active sheet. The Names sheet is
>> >> hidden,
>> >> when in general use.
>> >>
>> >> Thanks in advance
>> >> Richard
>> >
>> >
>>
>>
>>

The beauty of your suggestion is that it needs be forced to recalculate. So
that by selecting a different sheet hitting F9 and then selecting the Names
sheet you can see the other sheet name. Then select F9 again and it updates
to display Names.

When in use I want the value to change as it depends on which sheet is
active as to who the email is sent to.

Apologies for not explaining clearly, but it works a treat.

"Bob Phillips" wrote:

> I think what I gave you earlier will work okay in Excel, the recalculate
> should get forced by the function. But won't the value keep switching as you
> switch sheets>
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
>
>
> "Richard" <(E-Mail Removed)> wrote in message
> news:CB6777F7-2E1F-4C85-A8CF-(E-Mail Removed)...
> >I am using the active sheet name in conjuction with the indirect function
> >as
> > part of a vlookup to create a list of email addresses, so that the code
> > can
> > populate the To: field in outlook. I therefore needed something in the
> > worksheet and not the code, as this is I think easier to manage as the
> > steps
> > are clearer in the spreadsheet and also makes the code easier for a
> > begginer
> > in VBA.
> >
> > It might well be a "round the houses" approach, but it works
> >
> > Thanks for all the help
> >
> > "Bob Phillips" wrote:
> >
> >> BTW, why can't you just pick up Activesheet.Name?
> >>
> >> --
> >> ---
> >> HTH
> >>
> >> Bob
> >>
> >>
> >> (there's no email, no snail mail, but somewhere should be gmail in my
> >> addy)
> >>
> >>
> >>
> >> "Bob Phillips" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > Put this in the hidden sheet cell
> >> >
> >> > =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)
> >> >
> >> > and just make sure that you force a book calculation before reading it
> >> > in
> >> > VBA.
> >> >
> >> > --
> >> > ---
> >> > HTH
> >> >
> >> > Bob
> >> >
> >> >
> >> > (there's no email, no snail mail, but somewhere should be gmail in my
> >> > addy)
> >> >
> >> >
> >> >
> >> > "Richard" <(E-Mail Removed)> wrote in message
> >> > news:2DD878F1-81B4-48BD-B738-(E-Mail Removed)...
> >> >> Hi
> >> >>
> >> >> I need to return the active sheet name in a cell on another sheet. I
> >> >> guess
> >> >> the best way of doing this would be to use a UDF, but I don't know how
> >> >> to
> >> >> set
> >> >> this up.
> >> >>
> >> >> For example on a sheet called Names in cell A1, I would like to always
> >> >> return the name of the currently active sheet. The Names sheet is
> >> >> hidden,
> >> >> when in general use.
> >> >>
> >> >> Thanks in advance
> >> >> Richard
> >> >
> >> >
> >>
> >>
> >>
>
>
>