Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|