VBA Modification to Rename sheet

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
411
Office Version
  1. 2019
Platform
  1. Windows
I have a vba code to make an identical worksheet of a template and delete the option button I use to activate it. Is there a way to have a dialogue box or something pop up where I could rename the sheet and then end the vba? It's not really a big deal for me but this is a shared file and sometimes when the other people sharing it use this template in that way they don't rename the copy to the what it should be. The name will always be unique so there's nowhere to put a value to make the name be referenced.

VBA Code:
Sub New_Superpave2()
    Sheets("Superpave Template").Copy After:=ActiveSheet
    Sheets("Superpave Template (2)").Select
    ActiveSheet.Shapes.Range(Array("Option Button 2")).Select
    Selection.Delete
End Sub
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
411
Office Version
  1. 2019
Platform
  1. Windows
NVM I just figured it out.

VBA Code:
Sub New_Superpave2()
    Sheets("Superpave Template").Copy After:=ActiveSheet
    Sheets("Superpave Template (2)").Select
    ActiveSheet.Shapes.Range(Array("Option Button 2")).Select
    Selection.Delete

response = InputBox("Name of the Sheet?", vbOKCancel)
If response = False Or response = "" Then
MsgBox "Invalid Name"
Exit Sub
Else
ActiveSheet.Name = response
End If
response = ""
End Sub
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,362
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
A more compact version:
VBA Code:
Sub New_Superpave2()
    Sheets("Superpave Template").Copy After:=ActiveSheet
    ActiveSheet.Shapes.Range(Array("Option Button 2")).Select
    Selection.Delete
    On Error Resume Next
    ActiveSheet.Name = InputBox("Enter a valid name for the copied sheet")
End Sub
 

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
411
Office Version
  1. 2019
Platform
  1. Windows
A more compact version:
VBA Code:
Sub New_Superpave2()
    Sheets("Superpave Template").Copy After:=ActiveSheet
    ActiveSheet.Shapes.Range(Array("Option Button 2")).Select
    Selection.Delete
    On Error Resume Next
    ActiveSheet.Name = InputBox("Enter a valid name for the copied sheet")
End Sub
That works great and looks much cleaner. Still getting the hang of the VBA.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,009
Messages
5,575,535
Members
412,673
Latest member
KD23
Top