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]

Glad we could help & thanks for the feedback