| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can worksheet names be changed by changing certain cells?
I have a model with dummy names presently. I need some way to change the
worksheet names when the user chnges them in some cells in a seperate worksheet. What i need is a link to the worksheet names to a cell in another worksheet. and when this cell is edited the worksheet name also changes. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
can worksheet names be changed by changing certain cells?
Apply these event macros in code of sheet containing sheet names:
Public oldname As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then Worksheets(oldname).Name = Target.Value End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 1 Then oldname = Target.Value End If End Sub Regards, Stefi €žanand€ ezt Ã*rta: I have a model with dummy names presently. I need some way to change the worksheet names when the user chnges them in some cells in a seperate worksheet. What i need is a link to the worksheet names to a cell in another worksheet. and when this cell is edited the worksheet name also changes. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
can worksheet names be changed by changing certain cells?
It would be better to put the worksheet names in say the next column and
pick it up from there rather than code it. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False If Target.Column = 1 Then Worksheets(Target.Offset(0,1).Value).Name = Target.Value End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stefi" wrote in message ... Apply these event macros in code of sheet containing sheet names: Public oldname As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then Worksheets(oldname).Name = Target.Value End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 1 Then oldname = Target.Value End If End Sub Regards, Stefi "anand" ezt írta: I have a model with dummy names presently. I need some way to change the worksheet names when the user chnges them in some cells in a seperate worksheet. What i need is a link to the worksheet names to a cell in another worksheet. and when this cell is edited the worksheet name also changes. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
can worksheet names be changed by changing certain cells?
It's nice, Bob! I would add a line after
Worksheets(Target.Offset(0,1).Value).Name = Target.Value to ensure that the method works next time when the user changes sheet name: Target.Offset(0, 1).Value = Target.Value Regards, Stefi €žBob Phillips€ ezt Ã*rta: It would be better to put the worksheet names in say the next column and pick it up from there rather than code it. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False If Target.Column = 1 Then Worksheets(Target.Offset(0,1).Value).Name = Target.Value End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stefi" wrote in message ... Apply these event macros in code of sheet containing sheet names: Public oldname As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then Worksheets(oldname).Name = Target.Value End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 1 Then oldname = Target.Value End If End Sub Regards, Stefi "anand" ezt Ã*rta: I have a model with dummy names presently. I need some way to change the worksheet names when the user chnges them in some cells in a seperate worksheet. What i need is a link to the worksheet names to a cell in another worksheet. and when this cell is edited the worksheet name also changes. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
can worksheet names be changed by changing certain cells?
That strikes me as eminently sensible :-)
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stefi" wrote in message ... It's nice, Bob! I would add a line after Worksheets(Target.Offset(0,1).Value).Name = Target.Value to ensure that the method works next time when the user changes sheet name: Target.Offset(0, 1).Value = Target.Value Regards, Stefi "Bob Phillips" ezt írta: It would be better to put the worksheet names in say the next column and pick it up from there rather than code it. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False If Target.Column = 1 Then Worksheets(Target.Offset(0,1).Value).Name = Target.Value End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stefi" wrote in message ... Apply these event macros in code of sheet containing sheet names: Public oldname As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then Worksheets(oldname).Name = Target.Value End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 1 Then oldname = Target.Value End If End Sub Regards, Stefi "anand" ezt írta: I have a model with dummy names presently. I need some way to change the worksheet names when the user chnges them in some cells in a seperate worksheet. What i need is a link to the worksheet names to a cell in another worksheet. and when this cell is edited the worksheet name also changes. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing worksheet names | Excel Worksheet Functions | |||
| Worksheet (tab names) in cells | Excel Discussion (Misc queries) | |||
| changing names in Cells of the Sales Pipeline management template? | Excel Worksheet Functions | |||
| ... After I protected my worksheet, some cells can be changed... | Setting up and Configuration of Excel | |||
| I want in one worksheet to relatively link to/reference cells in another without changing the format of the current worksheet. | Excel Discussion (Misc queries) | |||
Linear Mode
