Posted to microsoft.public.excel.programming
|
|
Pre-checking checkboxes in a dialog box
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)
"Mike" wrote in message
...
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
"Bob Phillips" wrote in message
...
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)
"Mike" wrote in message
...
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
"Bob Phillips" wrote in message
...
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)
"Mike" wrote in message
...
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
|