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

 

[agihcam]

try;

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim NameRange As Range 
Set NameRange = Range("A1") 
If Not Intersect(Target, NameRange) Is Nothing Then 
    sheets(2).name = CStr(NameRange) 
End If 
End Sub

 

[damian_r]

Agihcam,

I've given this ago but with no luck.

It is just a case of replacing the old code with your code isn't it?

Would it be easier for sheet 2 to take it's name directly from Sheet 1 cell A1?

Damian

 

[agihcam]

that was exactly what the code will do.

right click the sheet1 > view code then paste the code there.

 

[jindon]

Paste the code onto Sheet1 module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewName As string, msg As String, m As Object, ws As Worksheet
With Target.Cells.(1,1)
   If .Address(0,0) <> "A1" Then Exit Sub
   If .Value = "" Then Exit Sub
   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
   On Error Resume Next
   Set ws = Sheets(NewName)
   If Not ws Is Nothing Then
      MsgBox "Sheets already exists"
   Else
      Sheet2.Name = NewName
   End If
End Sub

 

[damian_r]

Jindon,

THansk for this but i think i might have left an important factor out about what i was trying to do which might well mean that what you have suplpied me might not work.

I note that you state that the code has to be put in sheet 1, in order to rename sheet 2, however what i failed to mention is that in total there will be 25 sheets following sheet 1, all of which will take their name from cell A1 on downwards (i.e sheet 6 will take it's name from sheet 1 cell A5).

I think that this would mean that your code would have to be approx 25 times longer than it is to cover all teh sheets.

Am i right?

Damian

 

[jindon]

try

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 > 1 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 + 1
   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

edit : code on 17:34

 

[damian_r]

Agihcam,

Your code is working but only if i click in cell C1 on sheet 2 and thin hit enter (basically re-applying the formula already in there).

Is it possible to add some code which will always automatically do this?

Damian

 

[agihcam]

Damian -

Have you tried latest code form Jindon yet? it covers all your requirement.

 

[damian_r]

I must admit i have not tried Jindons code as teh examples i gave were not the actual sheets or cells i needed to do the 'magic' in and i caouldn't see where i can change that in his code.

I'm really not that good at coding

D

 

[jindon]

You should tell us exactly what you want to do.