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.

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.

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.

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.

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.