name sheet

[Gert]

Hi all,

is it possible to use a private sub to change the name of a worksheet
as soon as a specific cell is changed (cell value must be part of the
sheet name)

thanks in advance
Gert

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
sh.Name = "Sheet" & .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Gert]

Thanks Bob,

with a small adaption it works perfect

I have replaced: sh.Name = Sheet" &.Value
by: Activesheet.Name = Sheet" &.Value

regards
Gert

 

[Bob Phillips]

Oops. Sorry about that, don't know what I was thinking there. Rather than
Activesheet, it would be better to refer to Me, they should be the same
object, but it is the 'proper' way of doing it.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)