can worksheet names be changed by changing certain cells?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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:
 
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)
 
It's nice, Bob! I would add a line after
Worksheets(Target.Offset(0,1).Value).Name = Target.Value
Click to expand...
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:
 
That strikes me as eminently sensible :-)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top