Changing Sheet Name

  • Thread starter Thread starter Khalil Handal
  • Start date Start date
K

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
 
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)
 
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
 
This worked as a macro. How can it be done so that it runs automatically.
Bob's Suggestion did't work out!
 
Khalil said:
This worked as a macro. How can it be done so that it runs automatically.
Bob's Suggestion did't work out!
Click to expand...

Worked for me first time, cut a paste. Excel 2003
 
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
 
Thanks to all of you it worked well.

Gord Dibben said:
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
Click to expand...
 
Back
Top