VBA to place sheet name in cell

I am using a formula:
“Sheet "& MID(CELL("filename",B11),FIND("]",CELL("filename",B11))+2, LEN(CELL("filename",B11))-FIND("]",CELL("filename",B11))) “

to place part of the worksheet name in a cell. However because of formatting requirements I cannot use a formula. Can I do this using VBA? Then I could have the macro run each time the workbook is closed to keep the cell up to date.

Thanks
Robert

You could use the BeforeSave event

See Chip Pearson's page on events
http://www.cpearson.com/excel/events.htm

worksheets(1).Range("B2').Value = "Sheet " & worksheets(1).Name

--
Regards,
Tom Ogilvy


"Hammer_757" <Hammer_757.1uj1ed_1125320723.963@excelforum-nospam.com> wrote
in message news:Hammer_757.1uj1ed_1125320723.963@excelforum-nospam.com...
>
> I am using a formula:
> "Sheet "& MID(CELL("filename",B11),FIND("]",CELL("filename",B11))+2,
> LEN(CELL("filename",B11))-FIND("]",CELL("filename",B11))) "
>
> to place part of the worksheet name in a cell. However because of
> formatting requirements I cannot use a formula. Can I do this using
> VBA? Then I could have the macro run each time the workbook is closed
> to keep the cell up to date.
>
> Thanks
> Robert
>
>
> --
> Hammer_757
> ------------------------------------------------------------------------
> Hammer_757's Profile:
http://www.excelforum.com/member.php...fo&userid=7413
> View this thread: http://www.excelforum.com/showthread...hreadid=400037
>

Thanks tom, that was too simple to be true

What formatting requirements would stop a formula?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hammer_757" <Hammer_757.1uj1ed_1125320723.963@excelforum-nospam.com> wrote
in message news:Hammer_757.1uj1ed_1125320723.963@excelforum-nospam.com...
>
> I am using a formula:
> "Sheet "& MID(CELL("filename",B11),FIND("]",CELL("filename",B11))+2,
> LEN(CELL("filename",B11))-FIND("]",CELL("filename",B11))) "
>
> to place part of the worksheet name in a cell. However because of
> formatting requirements I cannot use a formula. Can I do this using
> VBA? Then I could have the macro run each time the workbook is closed
> to keep the cell up to date.
>
> Thanks
> Robert
>
>
> --
> Hammer_757
> ------------------------------------------------------------------------
> Hammer_757's Profile:
http://www.excelforum.com/member.php...fo&userid=7413
> View this thread: http://www.excelforum.com/showthread...hreadid=400037
>

Multiple fonts, check this post
Multiple fonts in one cell

the code worked fine for me when used on any sheet that was active.

Produced the correct name.

--
Regards,
Tom Ogilvy

"Hammer_757" <Hammer_757.1ukyuj_1125410752.817@excelforum-nospam.com> wrote
in message news:Hammer_757.1ukyuj_1125410752.817@excelforum-nospam.com...
>
> I spoke too soon
>
> Starting with Tom's sugestion, I came up with the following which works
> fine as long as I am on the first worksheet:
>
> Sub SheetNumberUpdate()
>
> 'Updates sheet number cell to match sheet (tab) name
>
> ActiveSheet.Range("E54").Font.Bold = False
> 'clears cell formating
>
> ActiveSheet.Range("E54").Value = "Sheet " & ActiveSheet.Name
> 'copies worksheetname to cell "Sheet #####"
>
> ActiveSheet.Range("E54").Characters(1, 5).Font.Bold = True
> 'sets the word "Sheet" to bold font
>
> End Sub
>
> However, when I use it on one of the other worksheets, say sheet 5 or
> 6, the cell is updated with the sheet name from the first worksheet. I
> thought by using Activesheet. Range and Activesheet.name and making sure
> the desired sheet is active this would work. what am I missing?
>
>
> --
> Hammer_757
> ------------------------------------------------------------------------
> Hammer_757's Profile:
http://www.excelforum.com/member.php...fo&userid=7413
> View this thread: http://www.excelforum.com/showthread...hreadid=400037
>