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

polska2180

Active Member
Joined
Oct 1, 2004
Messages
345
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,278
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-known Member
Joined
Jul 5, 2014
Messages
16,278
Well maybe I do know how:

Try this:
Code:
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

Active Member
Joined
Oct 1, 2004
Messages
345
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.
 

polska2180

Active Member
Joined
Oct 1, 2004
Messages
345
Well maybe I do know how:

Try this:
Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,278
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

Active Member
Joined
Oct 1, 2004
Messages
345
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,278
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:

Code:
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
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,278
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.
 

Forum statistics

Threads
1,082,099
Messages
5,363,129
Members
400,720
Latest member
Pettel

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top