OnClick
 

Hi,

I have about 15 check boxes on the main sheet in my file. When a box
is checked, I want to add a sheet with a given name:

Sheets.Add.Name = "NAME1

When that box is then unchecked, I want to delete the sheet.

How can I do this through VBA?

Thanks!

Brett

OnClick
 

I'd use checkboxes from the Forms toolbar and assign the same macro to each of
the checkboxes.

In fact, I'd plop the name of each of the sheets to use in the caption for each
of the checkboxes.

If you want to try this...

Option Explicit
Sub AddCBXForSheets_RunThisOnce()
Dim CBX As CheckBox
Dim myCaptions As Variant
Dim wks As Worksheet
Dim myCell As Range
Dim myRng As Range
Dim HowManyCBX As Long
Dim iCtr As Long

Set wks = Worksheets("Sheet1")
myCaptions = Array("Name/1", "Name2", "Name3")
HowManyCBX = UBound(myCaptions) - LBound(myCaptions) + 1
With wks
.CheckBoxes.Delete 'remove any existing checkboxes.
'starts in A2 and goes down the sheet
Set myRng = .Range("A2").Resize(HowManyCBX, 1)

iCtr = LBound(myCaptions)
For Each myCell In myRng.Cells
With myCell
Set CBX = .Parent.CheckBoxes.Add _
(Left:=.Left, Height:=.Height, _
Width:=.Width, Top:=.Top)
End With

With CBX
.Name = "CBX_" & myCell.Address(0, 0)
.Caption = myCaptions(iCtr)
.Placement = xlMoveAndSize
.OnAction = "'" & ThisWorkbook.Name & "'!CBXClick"
End With
iCtr = iCtr + 1
Next myCell
End With

End Sub
Sub CBXClick()
Dim CBX As CheckBox
Set CBX = ActiveSheet.CheckBoxes(Application.Caller)
Dim TestWks As Worksheet

If CBX.Value = xlOn Then
'add sheet
'but check first to see if one exists with that name
Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(CBX.Caption)
On Error GoTo 0

If TestWks Is Nothing Then
Application.ScreenUpdating = False
Set TestWks = Worksheets.Add(after:=Sheets(Sheets.Count))
CBX.Parent.Select
Application.ScreenUpdating = True
On Error Resume Next
TestWks.Name = CBX.Caption
If Err.Number < 0 Then
MsgBox "Design error--invalid name in caption!" & vbLf _
& "Rename failed."
Err.Clear
End If
On Error GoTo 0
Else
MsgBox "A worksheet with that name already exists!"
End If
Else
'just try to delete it
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(CBX.Caption).Delete
Application.DisplayAlerts = True
On Error GoTo 0
End If

End Sub



wrote:

Hi,

I have about 15 check boxes on the main sheet in my file. When a box
is checked, I want to add a sheet with a given name:

Sheets.Add.Name = "NAME1

When that box is then unchecked, I want to delete the sheet.

How can I do this through VBA?

Thanks!

Brett

--

Dave Peterson