0

I want a button to copy a template worksheet and ask for the new sheet's name (if the name already exists, it should show an error message).
I'm a beginner so I searched the internet and found the following code from an older post, (VBA - Copy a template worksheet and rename with user entered text):

Sub Test()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Sheets("Template")
    Dim newws As Worksheet, sh As Worksheet, newname
    Dim query As Long, xst As Boolean, info As String

retry:
    xst = False
    newname = Application.InputBox("Enter a new sheet name.", info, , , , , , 2)
    If newname = "False" Then Exit Sub
    For Each sh In wb.Sheets
        If sh.Name = newname Then
            xst = True: Exit For
        End If
    Next
    If Len(newname) = 0 Or xst = True Then
        info = "Sheet name is invalid. Please retry."
        GoTo retry
    End If
    ws.Copy after:=ws: Set newws = ActiveSheet: newws.Name = newname
End Sub

I tried this code on my home PC (Win10, Excel 2019) and it works perfectly, But for some reason, on my work PC (Win7, Excel 2007) it does not work properly,
Here's the problem: when user closes the input box, it creates a worksheet called "False",
The next time user opens the input box, it cannot be closed, no matter how many times he clicks on the "cancel" button, and then excel stops working.

Can someone please help me fix this problem, or give me another code to do the same thing?

Sahand Faghihi
  • 1
  • 1
  • 1
    https://stackoverflow.com/questions/55453256/vba-inputbox-and-cancel-button is a more robust way to check if cancel was selected. – BigBen Oct 01 '20 at 16:13
  • To explain why your code fails, that code you copied from declares `newname As String`, so when the userpresses Cancel the returned Boolean is type cast to string. Your code declares it as `Variant` so the returned value is a Boolean. And False != "False" – chris neilsen Oct 01 '20 at 21:23
  • @chrisneilsen can you please explain more? What exactly should I do to fix the problem? (As I said before, I'm an absolute beginner) – Sahand Faghihi Oct 03 '20 at 14:33

0 Answers0