Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can a worksheet (tab) be renamed via a cell's value?
I would like to be able to rename a worksheet according to the
returned value of a cell. For example, if Sheet1 A1=1, then Sheet2 is named "Red." When the same Sheet1 A1=2, then Sheet2 is renamed "Green." Is this possible? Thanks for your help. Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can a worksheet (tab) be renamed via a cell's value?
Right-click your Sheet1 tab and select "View Code". Then paste in the code
below. Add more "cases" for more number-color combinations. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo No2 If Target.Address = "$A$1" Then Select Case Target.Value Case 1 Sheets(2).Name = "Red" Case 2 Sheets(2).Name = "Green" End Select End If No2: On Error GoTo 0 End Sub "Michael Lanier" wrote: I would like to be able to rename a worksheet according to the returned value of a cell. For example, if Sheet1 A1=1, then Sheet2 is named "Red." When the same Sheet1 A1=2, then Sheet2 is renamed "Green." Is this possible? Thanks for your help. Michael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can a worksheet (tab) be renamed via a cell's value?
Another way to use a worksheet change event:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Sheet1.Range("A1").Value = 1 Then Sheet2.Name = "Red" ElseIf Sheet1.Range("A1").Value = 2 Then Sheet2.Name = "Green" Else MsgBox ("no name for that value") End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can a worksheet (tab) be renamed via a cell's value?
That works too - but it does seem like a good idea to account for the
possibility there is no Sheet2, thus the error handler I included... Another thought might be to put some validation on the a1 input cell... "arjen van..." wrote: Another way to use a worksheet change event: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Sheet1.Range("A1").Value = 1 Then Sheet2.Name = "Red" ElseIf Sheet1.Range("A1").Value = 2 Then Sheet2.Name = "Green" Else MsgBox ("no name for that value") End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting an Excel worksheet from being renamed | Excel Programming | |||
Hyperlink to renamed Worksheet fails | Excel Discussion (Misc queries) | |||
In Office 2003 program worksheet can't be renamed due to .xls add. | Excel Discussion (Misc queries) | |||
How can I include a cell's value in a worksheet header? | Excel Programming | |||
toolbar macros that dont change when worksheet is renamed | New Users to Excel |