Setting Cell to Worksheet Name

  • Thread starter Thread starter javaeboy
  • Start date Start date
J

javaeboy

I found this neat function that allows you to set a cell to th
worksheet name:

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

I am using it to have the name of the person whose worksheet I a
altering show up on the top of the worksheet in BOLD letters. Th
users will be using the sheet in Shared mode, and never bother to loo
to make sure they are on the right sheet ( I have been having them us
a workbook with 31 worksheets representing the 31 possible days of th
month, and they are always entering their data on the wrong page
because it goes to the worksheet that was saved last.(

Anyways, for some reason the only problem I am having, is that if yo
go from one sheet to another that has the same function in it, the cel
using the function above stays set to whatever worksheet was opened wit
the spreadsheet. I can get it to recalculate by hitting F9, but I don'
want the users to have to do that. Is there some way I can get it t
recalculate when someone opens their worksheet? I also need it to wor
when in SHARED mode. (I believe MACROS don't work in Shared Mode) Am
correct, or have I been setting something wrong when I set th
spreadsheet out on the network? I am just setting it to Shar
Workbook, Clicking on the Allow changes by .... and setting the radi
button for The changes being saved win on the Advanced Tab.
Thank you again for all your hel
 
Hi Javaeboy!

You need to use:

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

You must use the reference to a cell in the sheet because, as you've
found, the CELL function is volatile.

255 is OK but 32 covers the maximum length of a worksheet name.
 
If you want the user to only use one specific sheet, you could use VBA to
restrict them to that sheet.

Post back if you want an example.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Norman, that worked just fine. Thank you.

Bob,

That would be great if you can give me an example of protecting th
sheets except for the person that is supposed to use it. I've neve
really used VBA, but as a previous Java developer, I'm pretty sure
could grasp the concepts.

If you can give me an example, (and where exactly to put the code) an
restrict I am guessing by name access to the sheet(s), that would work
I am also assuming that there would be some form of Administrato
rights to all the sheets. Is that part of the code also? I can liv
without it, but that would make the supervisors jobs much easier, i
they didn't have to decipher their employees mistakes.

By the way, is there an Access related version of this forum anywhere?
Sometimes I need to develop in Access for a more complex situation, an
I'm always writing some convuluted workaround.

Once again, Thank you all for your help...
 
Back
Top