Pre-checking checkboxes in a dialog box

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have used some VBA script that I obtained from John Walkenbach's website
(specifically, http://j-walk.com/ss/excel/tips/tip48.htm) to provide users
with a dialog box to choose a selection of worksheets to print, in my case
using the modification under the link show that is suggested to ensure
continuous page numbering. It works great.

I have multiple user groups, however, and have a good idea of what different
'packs' these different user groups might want (that is, depending on their
seniority/role, they may want the "Executive version", the "Full/Standard
version", etc.. and the version choice should drive standardised, different
worksheet selections).

Can I adapt the script to pre-check only certain checkboxes, so that when
the macro for the "Executive version" is run, say, the dialog box shows up
as before but the user is just confirming rather than actively choosing a
selection of worksheets? This way, at least they'll see/appreciate what
they're not getting in choosing the more summary versions of the different
'packs' provided, which they wouldn't get if I wrote a standard macro to
just print the relevant worksheet selection.

Thanks in advance for any help

Mike
 
Mike,

When the checkbox is added, you need to set the value to True. So in John's
example, the checkbox is added with

PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _

you then need to ad the line

PrintDlg.CheckBoxes(SheetCount).Value = True

Clearly you need to adapt to your code, and add the test for Executive etc.,
but that is the basic principle.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob

Many thanks for this. I now have:

PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
PrintDlg.CheckBoxes(SheetCount).Value = True

When run, this now checks all the boxes (as, I think, the SheetCount
function simply returns a value corresponding to all the n non-empty
worksheets)

If, say, only the "Sheet 2" and "Sheet 3" are to be included (there are
actually lots, renamed according to worksheet content) how can I incorporate
this condition?

Apologies for seeming a bit thick, but I'm not great with this stuff.

Thanks again for your help.

Mike
 
Bob

Another reply - sorry - but I guess what I'm asking is can I have some help
on "add the test for Executive etc.,"

Thanks

Mike
 
Mike,

I understood the question, but as I don't have all of the details, all I can
do is give some guidance.

As you correctly said, the code at the moment sets all of the checkboxes
because the loop doesn't do any differential test. Let's assume that you
have a Long variable, let's call it nVersion, that is set as follows (I
assume you already have code to determine what type of role your user is)
0 - Executive
1 - Full
2 - Standard
the code might look something like this

PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
SetCheckBoxes PinrtDlg, nVersion, SheetCount
....

and then create the new SedtCheckBoxes procedure

Sub SetCheckBoxes(PrintDlg As DialogSheet, Version As Long, SheetIndex As
Integer)

Select Case Version
Case 0:
Select Case SheetIndex
Case 1: PrintDlg.CheckBoxes(SheetIndex).Value = False
Case 2: PrintDlg.CheckBoxes(SheetIndex).Value = True
Case 3: PrintDlg.CheckBoxes(SheetIndex).Value = True
End Select
Case 1:
Select Case SheetIndex
Case 1: PrintDlg.CheckBoxes(SheetIndex).Value = False
Case 2: PrintDlg.CheckBoxes(SheetIndex).Value = False
Case 3: PrintDlg.CheckBoxes(SheetIndex).Value = True
End Select
Case 2:
Select Case SheetIndex
Case 1: PrintDlg.CheckBoxes(SheetIndex).Value = False
Case 2: PrintDlg.CheckBoxes(SheetIndex).Value = False
Case 3: PrintDlg.CheckBoxes(SheetIndex).Value = False
End Select
End Select

End Sub


Obviously, you would need to adapt to the number of sheets, the number of
variations, and what the actual boxes to be checked are.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob

Thanks for this and your time - it's much appreciated [I realise that
sometimes it's not possible to provide the end result without knowing all
the facts]. I guess it's time for me to pull my finger out and really sit
down and figure it all out myself.

Will let you know how it goes.

Thanks again

Mike
 
Mike,

Hopefully I will have given you a good start,

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob

I've had a good stab at sorting this out, but ending up tying myself in
knots unfortunately! I guess it's a lesson that pulling code from the
internet isn't always as easy as just copying and pasting (plus, that I have
lots more to learn more to the point). Feel free to give up on this if your
patience is running out!

If you have any further assistance that would be very much appreciated
though: the complete case is as follows:

The model has 3 option buttons on the front worksheet:

Button 1 - "Print executive summary"
Button 2 - "Print executive summary, plus key analysis"
Button 3 - "Print entire model, i.e. executive summary, key analysis, and
underlying data"

(those aren't the actual button texts, but you get the point clearly)

The script that you saw originally from JW's website will be adapted
slightly to form 3 different macros, which will then be assigned to their
respective buttons.

The only difference between the 3 macros, however, other than the button to
which they are assigned, is that each will check a different selection of
checkboxes, but even the 3rd one will not check all the checkboxes, as some
worksheets are not intended for printing at all. So I guess I need to know
the generic amendment that needs to be made to check only certain worksheets
for printing. I realise you fully understand the objective, so apologies for
saying it again, but - just to highlight - rather than working out who the
user is and then adapting a single macro accordingly, there will always be
the 3 buttons allowing any user all 3 choices should they wish.

The key part of the script seems to be:

' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

Is it possible to insert a short piece of additional script that says :
check only those worksheets called "worksheet A", "worksheet B", etc. ?

I'm afraid that I couldn't understand how to work in practice the SheetIndex
0,1,2 / Case 1,2,3 that you describe, but see what the objective was though.
Thanks for your efforts though.

Cheers again for any further help

Mike
 
Mike,

Keep at it. We will resolve this.

I think I have given you all the code you need. All you need to do is to
adapt the 3 button macros to fire the correct version of the dialog. As I
suggested in my last post, the easiest way would be to declare a public
variable and have each button set it to a specific value, and pass that to
the print dialog routine. All you then need to do is to set the appropriate
sheets.

If you are still confused, you could just send me the workbook.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top