Pop-Up Dialogue Box/check boxes/deleting worksheets

[Andrew Schulman]

Ok, here's a doozy from a total novice. Any help/insight/points in the right
direction(s) would be greatly appreciated. Here's what I'm looking to create:

A template workbook that, the first time it is opened, pops-up a dialogue
box with a list of 21 countries with a checkbox next to each (the default for
all checkboxes is to be checked). Each country coresponds to a worksheet that
exists in the template. Also, there's a list of countries and corresponding
infromation from that country listed on a title worksheet. Depending on
which of those checkboxes is unchecked by the user, the worksheet for that
country is deleted AND the row containing that country's information on the
title worksheet is deleted as well.

Is all of that even possible? Also, would there be a way to "re-check" a
country later and add it back in later?

That's a lot, I know, but I would be forever indebted to anyone who can
provide info on even just one piece of this.

Thanks so much,

Andrew

 

[Bob Phillips]

Here is some code to dynamically give a worksheets popup and delete any
selected. Note I have done it the other way to you, deleting unchecked ones
see perverse to me.

Call the macro from the Workbook_Open procedure.


'----------------------------------------------------------------
Sub BrowseSheets()
'----------------------------------------------------------------
Const nPerColumn As Long = 35 'number of items per column
Const nWidth As Long = 7 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select sheet to goto"
'dialog caption
Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim iLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox

Application.ScreenUpdating = False

If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add

With thisDlg

.Name = sID
.Visible = xlSheetHidden

'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
iLeft = 78
TopPos = 40

For i = 1 To ActiveWorkbook.Worksheets.Count

If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
iLeft = iLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If

Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(CurrentSheet.Name)
If cLetters > cMaxLetters Then
cMaxLetters = cLetters
End If

iBooks = iBooks + 1
.CheckBoxes.Add iLeft, TopPos, cLetters * nWidth, 16.5
.CheckBoxes(iBooks).Text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13

Next i

.Buttons.Left = iLeft + (cMaxLetters * nWidth) + 24

CurrentSheet.Activate

With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = iLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With

.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.CheckBoxes
If cb.Value = 1 Then
ActiveWorkbook.Worksheets(cb.Caption).Delete
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Application.DisplayAlerts = False
.Delete

End With

End Sub
'----------------------------------------------------------------


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)