can worksheet names be changed by changing certain cells?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

anand - 05 Oct 2007 07:12 GMT

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 to this Message

Stefi - 05 Oct 2007 10:52 GMT

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 to this Message

Bob Phillips - 05 Oct 2007 13:03 GMT

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

Signature

HTH

Bob

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

> Apply these event macros in code of sheet containing sheet names:
> Public oldname As String
[quoted text clipped - 21 lines]
>> another
>> worksheet. and when this cell is edited the worksheet name also changes.

Reply to this Message

Stefi - 05 Oct 2007 13:41 GMT

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.
[quoted text clipped - 37 lines]
> >> another
> >> worksheet. and when this cell is edited the worksheet name also changes.

Reply to this Message

Bob Phillips - 05 Oct 2007 14:27 GMT

That strikes me as eminently sensible :-)

Signature

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
[quoted text clipped - 50 lines]
>> >> worksheet. and when this cell is edited the worksheet name also
>> >> changes.

Reply to this Message