Name of sheet from cell?

[damian_r_Home]

Hi

Is it possible for the name of the sheet to be automatically taken from a a cell within that sheet?

If it is and its coding that does it, can you please give me exacting step by step guide on how you do it.

Hope you understand what i'm on about.


Damian

 

[fmkjr]

thanks for the code

I wanted to pass on my gratitude for this code. I opened up the site today and found this on the main page. Ironic as it might seem... I needed this today for a WB I was completing!



frank

 

[damian_r]

OK, here goes.

the first sheet in the workbook is actually called EO Register. Cell B6 down to cell B30 will have numbers typed in them.

Cell J3 in the 3rd sheet in the workbook will take the number in cell B6 on sheet EO register by using ='EO Register'!$B$6
i would like it so that the 3rd sheet then renames itself to the value that appears in this cell.
The 4th sheet is to behave in teh same why but take it's name from Cell J3 which would actually get it's value by ='EO Register'!$B$7.
This then repeats iteself down to Cell B30 and the 26th Sheet in the workbook.

The reason i kept it basic at the start was that normally i can swap the target cells etc as i implement the code.

Hope this makes sense.

Damian

 

[jindon]

Frank:
Glad that I could help at least one person....

damian:
is this waht you wanted?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewName As string, msg As String, m As Object, ws As Worksheet
Dim myIndex As Integer
With Target
   If .Column <> 2 Then Exit Sub
   If .Row > 6 Then Exit Sub
   If .Value = "" Then Exit Sub
   If .Count > 1 Then Exit Sub
   If Len(.Text) > 31 then
      MsgBox "Exceeded, number of characters limited to 31"
      Exit Sub
   End If
   myIndex = .Row -3
   If myIndex > Sheets.Count Then
      MsgBox "Not enough number of sheets"
      Exit Sub
   End If
   NewName = .Text
End With
With CreateObject("VBScript.RegExp")
   .Pattern = "[:/\\\?\[\]\*]"
   .Global = True
   If .test(NewName) Then
      For Each m In .execute(NewName)
         msg = msg & m.Value & vbLf
      Next
      MsgBox "Invalid character for the sheet name" & vbLf & msg
      Exit Sub
   End If
End With
If Sheets(myIndex).Name <> NewName Then
   On Error Resume Next
   Set ws = Sheets(NewName)
   If Not ws Is Nothing Then
      MsgBox "Sheets already exists"
   Else
      Sheets(myIndex).Name = NewName
   End If
End If
End Sub

 

[damian_r]

Jindon,

Apologies for not getting back to you but i've been out of the office for a few days and just got back in.

I'll give this ago later today and let you know what happens.

Thanks
Damian

 

[damian_r]

Jindon,

I've given this a quick go and when i change the value in EO register cell B6, sheet 3 does indeed rename to the value put in that cell.

However, when i change the value in cell B7 in sheet EO register, sheet 4 does not rename to the value put in that cell.

I'm not sure what i'm doing wrong here.

Any thoughts?


Damian

 

[jindon]

Damian

Found bug in the line of

If .Row > 6 Then Exit Sub

Should be

If .Row < 6 Then Exit Sub

 

[damian_r]

Thankyou Jindon, it works fine now.

I don't know where I'd be without people like you to help out.

Damian

 

[t.ray]

I just wanted to thank jindon for the code. I am working on modifiying it a little bit.. I have to do timestudies and this has been a LIFE SAVER! In the past I had to rename each tab for the date of the time study in the template and then re-propagate into staff names on the network.. you have no idea how much time this has saved me


domo arigato gozaimas'!

 

[jindon]

domo arigato gozaimas'!

dou itashima****e

 

[CA_User]

I tried using the following code from an earlier post in this thread, but nothing worked for me.... help?


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NameRange As Range
Set NameRange = Range("A1")
If Not Intersect(Target, NameRange) Is Nothing Then
On Error Resume Next
ActiveSheet.Name = NameRange
If ActiveSheet.Name <> NameRange Then
MsgBox "Unable to change name"
End If
End If
End Sub