In my last question I was looking for a way to check or uncheck a tick box to insert or remove a new sheet in a workbook.
I know I can create a new workbook based on an excel template file, can I combine these two steps and set the macro below to create a new worksheet, based on a template, in the existing workbook?
Previous macro was

Public Sub CheckBox1_Click()
Const sSHEETNAME As String = "My New Sheet"
On Error Resume Next
With ActiveSheet
If .CheckBoxes(Application.Caller).Value = xlOn Then
With Worksheets.Add(After:=Sheets(Sheets.Count))
.Name = sSHEETNAME
.Range("A1").Value = "Related Text"
End With
.Select 'Restore selection to calling sheet
Else
Application.DisplayAlerts = False
Worksheets(sSHEETNAME).Delete
Application.DisplayAlerts = True
End If
End With
On Error GoTo 0
End Sub

--
Roger W

If you use sheets.add, you can specify your own template:

With Sheets.Add(After:=Sheets(Sheets.Count), _
Type:="C:\WINDOWS\Application Data\Microsoft\Excel\XLSTART\sheet.xlt")

.....

RogerNZ wrote:

In my last question I was looking for a way to check or uncheck a tick box to insert or remove a new sheet in a workbook.
I know I can create a new workbook based on an excel template file, can I combine these two steps and set the macro below to create a new worksheet, based on a template, in the existing workbook?
Previous macro was

Public Sub CheckBox1_Click()
Const sSHEETNAME As String = "My New Sheet"
On Error Resume Next
With ActiveSheet
If .CheckBoxes(Application.Caller).Value = xlOn Then
With Worksheets.Add(After:=Sheets(Sheets.Count))
.Name = sSHEETNAME
.Range("A1").Value = "Related Text"
End With
.Select 'Restore selection to calling sheet
Else
Application.DisplayAlerts = False
Worksheets(sSHEETNAME).Delete
Application.DisplayAlerts = True
End If
End With
On Error GoTo 0
End Sub

--
Roger W

--

Dave Peterson