Changing Sheet Name

[Khalil Handal]

Hi,
A cell (say B6) has a formula that reads the text value from another cell
(say D10).
so:
B6 has the formula =D10
Is it possible to have VBA code so that the sheet name will be equal to the
value in that cell (in my example) cell B6

 

[Bob Phillips]

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

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

Me.Name = .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 on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob


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

 

[Rick Rothstein \(MVP - VB\)]

Something like this maybe...

Sub ChangeSheetName()
With ActiveSheet
.Name = .Range("B6").Value
End With
End Sub

Change the ActiveSheet reference if you are not running this code from the
sheet you want to affect.

Rick

 

[Khalil Handal]

This worked as a macro. How can it be done so that it runs automatically.
Bob's Suggestion did't work out!

 

[Nick]

This worked as a macro. How can it be done so that it runs automatically.
Bob's Suggestion did't work out!

Worked for me first time, cut a paste. Excel 2003

 

[Gord Dibben]

Bob's code requires you to manually change the value in B6

Try this calculate event.

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("B6")
If .Value <> "" Then
Me.Name = .Value
End If
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Khalil Handal]

Thanks to all of you it worked well.

Bob's code requires you to manually change the value in B6

Try this calculate event.

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("B6")
If .Value <> "" Then
Me.Name = .Value
End If
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP