Error is thrown when I enter a name in the input box with an apostrophe in it e.g. Son's

[Balrajss0121]

Hi, I am having an issue with the code below. Every time it runs and I enter a company name with an apostrophe an error is thrown that reads 'type mismatch run time error 13'. I am fairly new to vba, I have however tried to double up on the quotation marks *If Evaluate("isref('"" & ActNm & ""'!A1)") Then*. This did stop the issue but then it stops the line underneath from running and excel runs its own error for duplicated names. Any help would be appreciated on how to get rid of this. Thank you

Sub CopySheetAndRename()
Dim ActNm As String

ActNm = InputBox("Please Enter the Clients Company Name.")
If ActNm = "" Then Exit Sub
If Evaluate("isref('" & ActNm & "'!A1)") Then
MsgBox "This Company Backup Sheet already exists!"
Exit Sub
End If
Application.ScreenUpdating = False
With ActiveWorkbook
.Sheets("MASTER SHEET").Visible = True
.Sheets("MASTER SHEET").Copy , .Sheets("MASTER SHEET")
ActiveSheet.Name = ActNm
.Sheets("MASTER SHEET").Visible = False
End With
Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :
And neither apostrophe '

You can put a validation to not allow the entry of those characters.

Edit:
Check this

Check for invalid Worksheet name

Hi All I am using an InputBox to collect a name from the user to create a new Worksheet. I know the Name "History" or certain characters like ": / \ * ? [ ]" are not allowed. Does anyone have a small VBA rountine that will loop through the InputBox answer and check for the presence of these...

www.mrexcel.com

 

[Balrajss0121]

Okay thank you for that insightful information. I will make use of the validation instead