Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet naming with cell info
Can anyone tell me how to link a cell info into the naming of a worksheet.
Normally I just right click on the tab and rename, but we would like a formula there if possible to read a certain cell which renames the worksheet when that cell is used. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet naming with cell info
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value < "" Then Me.Name = .Value End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "HFM - LEE" <HFM - wrote in message ... Can anyone tell me how to link a cell info into the naming of a worksheet. Normally I just right click on the tab and rename, but we would like a formula there if possible to read a certain cell which renames the worksheet when that cell is used. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet naming with cell info
I right clicked then hit view code and then pasted the code from Private
Sub...End Sub. I then changed H1 to D6(the cell I need), but the tab name did not change. If that cell is a Vlookup cell, would that matter? "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value < "" Then Me.Name = .Value End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "HFM - LEE" <HFM - wrote in message ... Can anyone tell me how to link a cell info into the naming of a worksheet. Normally I just right click on the tab and rename, but we would like a formula there if possible to read a certain cell which renames the worksheet when that cell is used. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet naming with cell info
Yes it does. Try this instead
Private Sub Worksheet_Calculate() With Me.Range("H1") If .Value < "" Then Me.Name = .Value End If End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "HFM - LEE" wrote in message ... I right clicked then hit view code and then pasted the code from Private Sub...End Sub. I then changed H1 to D6(the cell I need), but the tab name did not change. If that cell is a Vlookup cell, would that matter? "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value < "" Then Me.Name = .Value End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "HFM - LEE" <HFM - wrote in message ... Can anyone tell me how to link a cell info into the naming of a worksheet. Normally I just right click on the tab and rename, but we would like a formula there if possible to read a certain cell which renames the worksheet when that cell is used. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming same cell in different worksheet with 2 value changing on e | Excel Worksheet Functions | |||
Naming Cell in diffetn worksheet | Excel Worksheet Functions | |||
Naming Cell on differnt worksheet | Excel Worksheet Functions | |||
Naming Worksheet | New Users to Excel | |||
Want info in a specific cell to reflect in another worksheet.. | New Users to Excel |