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