Macro to rename sheets

[nhayden73]

Hi all,

I using a simple VB code that takes the name from a specific sheet cell and changes the tab name to match when that tab is activated.
I want to expand on this macro to have a message pop-up if there is an error with the name structure. Like having the same name as another tab or if the name violates the characters that can be use.

Here is the code that I have so far. (borrowed from a google search)

Private Sub Worksheet_Activate()
newname = Range("C6").Text
On Error Resume Next
If Right(newname, 1) = " " Then newname = Left(newname, (Len(newname) - 1))
ActiveSheet.Name = newname
End Sub

I am not sure how to add in a simple message in case there is an error.
Something like: "Please Revise Room Name. It appears to contain an illegal character."

I have tried using the code from this post. https://excel.tips.net/T002145_Dynamic_Worksheet_Tab_Names.html
The problem I encounter with this code is that when using the "FIND" or "REPLACE" features on the workbook level it triggers a Debug error.

Any assistance would be greatly appreciated.

 

[Fluff]

Hi & welcome to the board
How about

Private Sub Worksheet_Activate()
   Dim NewName As String
   NewName = Range("C6").Text
   On Error GoTo BadName
   ActiveSheet.Name = Trim(NewName)
Exit Sub
BadName:
   MsgBox "Illegal Name"
End Sub

 

[nhayden73]

Thank you for the welcome and quick response.
I'll give this try and see what happens.

 

[nhayden73]

Hi & welcome to the board
How about

Private Sub Worksheet_Activate()
   Dim NewName As String
   NewName = Range("C6").Text
   On Error GoTo BadName
   ActiveSheet.Name = Trim(NewName)
Exit Sub
BadName:
   MsgBox "Illegal Name"
End Sub

@Fluff

I gave this a try and it works fine but for some reason when I use the Find/Replace feature on a workbook level it causes the error message box to pop up and then a debug error.
Any thoughts on what might be causing this or how to resolve?

Thanks

 

[offthelip]

I think the problem is likely to be casued by the fact that your macro runs every time the worksheet is activated. You probably need to put a check to see if the name is already equal to C6

Add these two lines at the top

t = (ActiveSheet.Name)
If t = Range("C6") Then Exit Sub

 

[Fluff]

Are you searching for the value in C6 of that sheet?

 

[nhayden73]

No, I am not searching for the value in C6.
I'll try adding in the "IF" line you have above.

 

[nhayden73]

Hi, I tried adding the IF statement above. Not sure if I have it in the right spot of the code. This has helped with not triggering Debug to open but I still get the BadName message box pop-up when doing a Find/Replace.
Sorry for my ignorance on this I don't any experience with putting together VB code.

Here what I have at this time.

Private Sub Worksheet_Activate()
Dim NewName As String
t = (ActiveSheet.Name)
If t = Range("C6") Then Exit Sub
NewName = Range("C6").Text
On Error GoTo BadName
ActiveSheet.Name = Trim(NewName)
Exit Sub
BadName:
MsgBox "Illegal Name"
End Sub

 

[Fluff]

Try

Private Sub Worksheet_Activate()
   Dim NewName As String
   NewName = Range("C6").Text
   On Error GoTo BadName
   Me.Name = Trim(NewName)
   Exit Sub
BadName:
   MsgBox "Illegal Name"
End Sub

 

[nhayden73]

@Fluff
Thank you for all the help. That works perfectly.