I would like to set up a tick box to work like a toggle function. Specifically so that when it is ticked it activates a macro to insert a new sheet and related text, when it is then unticked it deletes the sheet from the workbook. I have been dabling in Excel Macros for a while now, but I haven't had any serious training so anything beyond what I can pick up out of seeing what the macro recorder does is a little mistifying. Mainly what I was hoping for is a sample of a Macro that checks a cell value for true or false value to determine which section of the macro to run, so that I can assign a single macro to the tick box and cell link the tick box to give a true or false value which will be used as above to determine which section of the macro to run. Of course if there is an easier way....
--
Roger W

Assuming you're talking about a forms toolbar checkbox, one way:

Assign this macro to the checkbox (right-click the checkbox and choose
Assign Macro):

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


No need to have a linked cell unless it serves some other purpose.

In article ,
"RogerNZ" wrote:

I would like to set up a tick box to work like a toggle function.
Specifically so that when it is ticked it activates a macro to insert a new
sheet and related text, when it is then unticked it deletes the sheet from
the workbook. I have been dabling in Excel Macros for a while now, but I
haven't had any serious training so anything beyond what I can pick up out of
seeing what the macro recorder does is a little mistifying. Mainly what I was
hoping for is a sample of a Macro that checks a cell value for true or false
value to determine which section of the macro to run, so that I can assign a
single macro to the tick box and cell link the tick box to give a true or
false value which will be used as above to determine which section of the
macro to run. Of course if there is an easier way....

Thank you very much, I knew it was possible but just not how to make it execute.
--
Roger W


"RogerNZ" wrote:

I would like to set up a tick box to work like a toggle function. Specifically so that when it is ticked it activates a macro to insert a new sheet and related text, when it is then unticked it deletes the sheet from the workbook. I have been dabling in Excel Macros for a while now, but I haven't had any serious training so anything beyond what I can pick up out of seeing what the macro recorder does is a little mistifying. Mainly what I was hoping for is a sample of a Macro that checks a cell value for true or false value to determine which section of the macro to run, so that I can assign a single macro to the tick box and cell link the tick box to give a true or false value which will be used as above to determine which section of the macro to run. Of course if there is an easier way....
--
Roger W

OK, further to this, if I want to break down this macro to toggle other functions, how does it actually work.
The creation and deletion of the new sheet is very quick, however when I open my other spreadsheet to copy it across into the new sheet it becomes very slow.
My main aim was to insert a new sheet as per the Macro, but then to open my data sheet and copy it across and input customer details etc into the corresponding cells. The end result sheet has several of these tick boxes, when I tick them I wanted the program to open the worksheets required and copy them across into the current worksheet and copy the common data onto the new sheet, thus meaning that when I close the Header sheet I can save as required file name with only the relevant sheets attached. This would mean that I could update the copied worksheets separately and that my main header sheet file size would not be so large.
My other option would be to create a header sheet with all possible sheets and delete unrequired sheets according to the tick boxes (un)selected ie only keep sheets related to checked tick boxes.
--
Roger W


"JE McGimpsey" wrote:

Assuming you're talking about a forms toolbar checkbox, one way:

Assign this macro to the checkbox (right-click the checkbox and choose
Assign Macro):

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


No need to have a linked cell unless it serves some other purpose.

In article ,
"RogerNZ" wrote:

I would like to set up a tick box to work like a toggle function.
Specifically so that when it is ticked it activates a macro to insert a new
sheet and related text, when it is then unticked it deletes the sheet from
the workbook. I have been dabling in Excel Macros for a while now, but I
haven't had any serious training so anything beyond what I can pick up out of
seeing what the macro recorder does is a little mistifying. Mainly what I was
hoping for is a sample of a Macro that checks a cell value for true or false
value to determine which section of the macro to run, so that I can assign a
single macro to the tick box and cell link the tick box to give a true or
false value which will be used as above to determine which section of the
macro to run. Of course if there is an easier way....