If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Tab referenced to a cell
I am using Excel 2007. I would like to use the content of a cell (A1) to
also be the name of my tab at the bottom of the sheet. Example: In cell A1, it contains the name: BOB. I would like the name of the tab at the bottom of the sheet to reflect what is in cell A1as BOB. Can this be done? Thanks |
#2
|
|||
|
|||
Tab referenced to a cell
All tabs or just one tab?
Event code for all tabs.................... Place in Thisworkbook module. Private Sub Workbook_SheetCalculate(ByVal sh As Object) 'for just one sheet change the event type to 'Private Sub Worksheet_Calculate() 'and place in the sheet module On Error GoTo endit Application.EnableEvents = False With ActiveSheet.Range("A1") If .Value "" Then ActiveSheet.Name = .Value End If End With endit: Application.EnableEvents = True End Sub When a calculation takes place on any sheet, the sheet name will reflect what is in A1 of that sheet. If same name as other sheet or contains invalid characters the event will simply end...............no change in sheet name. Gord Dibben MS Excel MVP On Wed, 28 Apr 2010 17:52:44 -0400, "Charles Eaves" wrote: I am using Excel 2007. I would like to use the content of a cell (A1) to also be the name of my tab at the bottom of the sheet. Example: In cell A1, it contains the name: BOB. I would like the name of the tab at the bottom of the sheet to reflect what is in cell A1as BOB. Can this be done? Thanks |
#3
|
|||
|
|||
Tab referenced to a cell
One tab.
As always, thank you very much!!!!!!!! "Gord Dibben" gorddibbATshawDOTca wrote in message ... All tabs or just one tab? Event code for all tabs.................... Place in Thisworkbook module. Private Sub Workbook_SheetCalculate(ByVal sh As Object) 'for just one sheet change the event type to 'Private Sub Worksheet_Calculate() 'and place in the sheet module On Error GoTo endit Application.EnableEvents = False With ActiveSheet.Range("A1") If .Value "" Then ActiveSheet.Name = .Value End If End With endit: Application.EnableEvents = True End Sub When a calculation takes place on any sheet, the sheet name will reflect what is in A1 of that sheet. If same name as other sheet or contains invalid characters the event will simply end...............no change in sheet name. Gord Dibben MS Excel MVP On Wed, 28 Apr 2010 17:52:44 -0400, "Charles Eaves" wrote: I am using Excel 2007. I would like to use the content of a cell (A1) to also be the name of my tab at the bottom of the sheet. Example: In cell A1, it contains the name: BOB. I would like the name of the tab at the bottom of the sheet to reflect what is in cell A1as BOB. Can this be done? Thanks |
Thread Tools | |
Display Modes | |
|
|