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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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