Re: Pop-Up Dialogue Box/check boxes/deleting worksheets

Bob -- Thanks so much. This is great. I appreciate your response.

"Bob Phillips" wrote:

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 *** 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 Dialog***
Dim Current*** As Work***
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 Current*** = Active***
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(Current***.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

Current***.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)

"Andrew Schulman" <Andrew Schulman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:E0F6AF9A-35C8-459D-A385-01333BE1FD0B@xxxxxxxxxxxxxxxx
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 work***
that
exists in the template. Also, there's a list of countries and
corresponding
infromation from that country listed on a title work***. Depending on
which of those checkboxes is unchecked by the user, the work*** for that
country is deleted AND the row containing that country's information on
the
title work*** 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



.