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 o
formatting requirements I cannot use a formula. Can I do this usin
VBA? Then I could have the macro run each time the workbook is close
to keep the cell up to date.

Thanks
Rober

--
Hammer_75
-----------------------------------------------------------------------
Hammer_757's Profile: http://www.excelforum.com/member.php...nfo&userid=741
View this thread: http://www.excelforum.com/showthread.php?threadid=40003

VBA to place sheet name in cell
 

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

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

VBA to place sheet name in cell
 

What formatting requirements would stop a formula?

--

HTH

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


"Hammer_757" wrote
in message ...

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

VBA to place sheet name in cell
 

Thanks tom, that was too simple to be true


--
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

VBA to place sheet name in cell
 

Multiple fonts, check this post
'Multiple fonts in one cell
(http://www.excelforum.com/showthread.php?t=399469

--
Hammer_75
-----------------------------------------------------------------------
Hammer_757's Profile: http://www.excelforum.com/member.php...nfo&userid=741
View this thread: http://www.excelforum.com/showthread.php?threadid=40003

VBA to place sheet name in cell
 

I spoke too soon :(

Starting with Tom's sugestion, I came up with the following which work
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 o
6, the cell is updated with the sheet name from the first worksheet.
thought by using Activesheet. Range and Activesheet.name and making sur
the desired sheet is active this would work. what am I missing

--
Hammer_75
-----------------------------------------------------------------------
Hammer_757's Profile: http://www.excelforum.com/member.php...nfo&userid=741
View this thread: http://www.excelforum.com/showthread.php?threadid=40003

VBA to place sheet name in cell
 

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

Produced the correct name.

--
Regards,
Tom Ogilvy

"Hammer_757" wrote
in message ...

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