Copy sheet with popup message asking to name the new copied sheet

[polska2180]

Hi, I have a master sheet I want to be able to copy by pressing a button then getting a popup asking what name I the user wants to name the sheet and then use that to name the sheet.

Thanks.

 

[polska2180]

Hi, I have a master sheet I want to be able to copy by pressing a button then getting a popup asking what name I the user wants to name the sheet and then use that to name the sheet.

Thanks.

Specifically what I want is to copy a master sheet that includes...

1. a pop up asking for a new name
2. if duplicate provide a pop up stating so and a new pop up shows up to enter a name again.

 

[My Aswer Is This]

This is beyond my knowledgebase.

Your asking for the script to completely start over again with no interaction from you.
I will continue to monitor this thread to see what I can learn.

 

[My Aswer Is This]

Well maybe I do know how:

Try this:

Sub Copy_Master()
'Modified  6/12/2019  1:45:38 AM  EDT
Application.ScreenUpdating = False
G:
On Error GoTo M
Dim ans As String
ans = InputBox("The New sheet you want named", "Enter new sheet name")
Sheets("Master").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = ans
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox ans & "  Is not a proper sheet name or has already been used"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
GoTo G
End Sub

 

[polska2180]

This is beyond my knowledgebase.

Your asking for the script to completely start over again with no interaction from you.
I will continue to monitor this thread to see what I can learn.

I appreciate your help and quick responses it is not my intent to frustrate you not sure what exactly what you are looking for since I cannot foresee what the code will do and look like until i can actually try it out.

after I get a popup if I leave the box blank i get

MsgBox ans & " Is not a proper sheet name or has already been used"

and all dialog boxes go away so I have to press the button again...if I press cancel again I get the message

MsgBox ans & " Is not a proper sheet name or has already been used"

and after pressing ok all dialog boxes go away.

Typically when you leave a box blank you either get the dialog box flash or you get another massage like cannot be empty you press okay and it takes you back to the same box that where you enter the name. On the flip side if you press cancel you shouldn't get the error message but just simply have the process stopped.

 

[My Aswer Is This]

Try code in Post 13

 

[polska2180]

Well maybe I do know how:

Try this:

Sub Copy_Master()
'Modified  6/12/2019  1:45:38 AM  EDT
Application.ScreenUpdating = False
G:
On Error GoTo M
Dim ans As String
ans = InputBox("The New sheet you want named", "Enter new sheet name")
Sheets("Master").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = ans
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox ans & "  Is not a proper sheet name or has already been used"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
GoTo G
End Sub

This creates issues...as after okay on blank or cancel it tries to delete activesheet. Also still gives a popup when you press cancel or okay on blank with this MsgBox ans & " Is not a proper sheet name or has already been used"

 

[My Aswer Is This]

That is because if you do not enter anything or click cancel you have not entered a proper sheet name.

The warning says you have entered a improper name or have already used that name.

So what's the problem?

 

[polska2180]

That is because if you do not enter anything or click cancel you have not entered a proper sheet name.

The warning says you have entered a improper name or have already used that name.

So what's the problem?

Little hard to comment as I am not knowledgeable enough to write the code but the problem is that if you press cancel you wouldn't typically expect to get an error similarly if you press ok without entering a name and get a debug error that will not work well when taking the end user into consideration.

 

[My Aswer Is This]

Try this:
The user will get no warning.

He will now know nothing happened when he entered a Improper name or a previously entered name.
All he will know is nothing happened:

Sub Copy_Master()
'Modified  6/12/2019  2:25:42 PM  EDT
Application.ScreenUpdating = False
G:
On Error GoTo M
Dim ans As String
ans = InputBox("The New sheet you want named", "Enter new sheet name")
Sheets("Master").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = ans
Application.ScreenUpdating = True
Exit Sub
M:
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
GoTo G
End Sub

 

[My Aswer Is This]

After testing my last script I find it will not work.

I really have no answer for you.

Your wanting if the user enters a improper name or no value or clicks Cancel button to get no warning but just try again. If I were a user I would wonder why is this script just running over and over. I'm not sure what I'm doing wrong.