Create new worksheet by ticking checkbox and then delete worksheet by unchecking box

Hi, I have got someway with my limited VBA knowledge (it is probably ugly and bloated!) to making this work but am struggling on the delete aspect.

I have a series of checkboxes which, if checked will create a new worksheet based on a hidden template within the workbook. If the user subsequently decides they don't need the worksheet, I want a message box to appear and for the worksheet to be deleted if the user selects Yes or Left if they select No and for the checkbox to be automatically populated again.

I have posted where I have got to below and would appreciate any help given.

Private Sub chkBROCs_Click()
Application.ScreenUpdating = False
If chkBROCs.Value = True Then
        Sheets("Template").Activate
        Sheets("Template").Copy After:=Sheets(1)
        Sheets("Template (2)").Visible = True
        Sheets("Template (2)").Select
        Sheets("Template (2)").Name = "BROCS"
        Application.WindowState = xlMaximized
        Sheets("BROCS").Range("A5").Select
    Else
I AM STUCK HERE
End If
Application.ScreenUpdating = True
End Sub

Ta

maybe something like this

Private Sub chkBROCs_Click()
    With Application
        .ScreenUpdating = False
        If chkBROCs.Value = True Then
            Sheets("Template").Copy After:=Sheets(1)
            Sheets("Template (2)").Visible = True
            Sheets("Template (2)").Name = "BROCS"
            Application.WindowState = xlMaximized
            Sheets("BROCS").Range("A5").Select
        Else: .DisplayAlerts = False
            Sheets("BROCS").Delete
            .DisplayAlerts = True
        End If
        .ScreenUpdating = True
        End With
 End Sub

Not sure why you have code to make the new sheet visible, is the template hidden?

or

Private Sub chkBROCs_Click()
   if application.displayalerts=false then exit sub
   If chkBROCs Then
      Sheets("Template").Copy , Sheets(Sheets.Count)
      Sheets(Sheets.Count).Name = "BROCS"
      Application.Goto Range("BROCS!A5")
   Else
      DisplayAlerts = False
      if msgBox ("Sure ?",vbYEsNo)=vbYes Then 
          Sheets("BROCS").Delete
      else
         application.enableevents=false
         chkBROCs=true
         application.enableevents=true
      end if
      .DisplayAlerts = True
   End If
 End Sub

Can you explain what you are actually trying to do

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Try this

Option Explicit
Const ShtName As String = "BROCS"

Private Sub chkBROCs_Click()

    With Application

        If chkBROCS Then
            Sheets("Template").Copy , Sheets(Sheets.Count)
           ActiveSheet.Name = ShtName
            'Application.Goto Range("BROCS!A5")
        Else: .DisplayAlerts = False
            If WksExists(ShtName) Then
                If MsgBox("Sure ?", vbYesNo) = vbYes Then
                    Sheets(ShtName).Delete
                End If
            End If
            .DisplayAlerts = True
        End If
    End With
End Sub


Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

If you restore the CheckBox to True then the code will trigger & create the sheet.

The code that I posted works perfectly, removing the sheet if the user chooses Yes

What's the point. If the checkbox is checked I would expect that to mean that the sheet exists

Try this using a button