Chewyhairball
Active Member
- Joined
- Nov 30, 2017
- Messages
- 312
- Office Version
- 365
- Platform
- Windows
Hi Guys,
I have this code that creates a new sheet and when run it asks for a new sheet name. It basically copies a hidden sheet called 'blank template' and renames it.
If I input the name it creates the new sheet
If I cancel it just cancels the code
If I use a name I have used before it creates a new tab called "blank template 2, 3, 4 etc)
I don't want it to do this but rather if there is already a sheet with the same name I would like it to put up a message ' This name already exists, please use a unique ID' followed by another input box to put the new name in.
thanks in advance.
Sub NewCheck()
'
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Sheet20.Visible = xlSheetVisible
Sheets("Blank Template").Select
Sheets("Blank Template").Copy After:=Sheets(Sheets.Count)
Range("B5").Select
Dim C As Range
Set C = ActiveCell
val1 = InputBox("Please enter the new System Check or Calibration Check ID", "New System/Calibration Check ID")
If val1 = "" Then
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheet20.Visible = xlSheetHidden
Application.DisplayAlerts = True
Sheets("New check").Select
Exit Sub
Else
C.Value = val1
End If
Sheet20.Visible = xlSheetHidden
ActiveSheet.Protect
End Sub
I have this code that creates a new sheet and when run it asks for a new sheet name. It basically copies a hidden sheet called 'blank template' and renames it.
If I input the name it creates the new sheet
If I cancel it just cancels the code
If I use a name I have used before it creates a new tab called "blank template 2, 3, 4 etc)
I don't want it to do this but rather if there is already a sheet with the same name I would like it to put up a message ' This name already exists, please use a unique ID' followed by another input box to put the new name in.
thanks in advance.
Sub NewCheck()
'
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Sheet20.Visible = xlSheetVisible
Sheets("Blank Template").Select
Sheets("Blank Template").Copy After:=Sheets(Sheets.Count)
Range("B5").Select
Dim C As Range
Set C = ActiveCell
val1 = InputBox("Please enter the new System Check or Calibration Check ID", "New System/Calibration Check ID")
If val1 = "" Then
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheet20.Visible = xlSheetHidden
Application.DisplayAlerts = True
Sheets("New check").Select
Exit Sub
Else
C.Value = val1
End If
Sheet20.Visible = xlSheetHidden
ActiveSheet.Protect
End Sub