Userform and Checkbox - will this work?

[Norm Lundquist]

I'd like your opinion if this is possible and any hints on how to code it:

A long-time Quattro user is converting to Excel. One of the features that
Quattro had that Excel doesn't have (as far as I know) is to be able to save
different "Print" settings - range, orientation, etc. With Excel you must
select the range one at a time and then set orientation if you have multiple
print settings on the same worksheet.

My idea to help print multiple print settings on the same worksheet is to
create a macro in the personal library. When executed it would show a
UserForm and read all range names. For each range name that starts with
'PRT-' the macro would add a Checkbox to the UserForm. The range would be
the range to print and a suffix of '-L' would stand for landscape and a
suffix of '-P' would mean portrait. The user would click any number of
checkboxes and then press a 'Print' button to print all the marked ranges.

Questions:
- Can a macro from the personal macro library do this?
- How would it avoid having multiple UserForms on the same sheet?
- Would the UserForms be saved with each worksheet? (I would not want that.)
- Should I use the Checkbox from 'Controls' or 'Form'?
- Any sample code to show how to dynamically make Checkboxes?
- Any hints on what to watch out for?

Thank You for any help!!

 

[Chip Pearson]

Your basic plan is solid, how I would suggest a few changes.

Instead of creating CheckBoxes on the fly on the userform, I would use a
single ListBox control with MultiSelect set to 2 - multiSelectExtended. Then
when the user click the Print button, loop throught the list and test the
Selected property of each list element and print the selected ranges. E.g.,

Dim Ndx As Long
Dim Rng As Range

With Me.ListBox1
For Ndx = 0 To .ListCount - 1
If .Selected(Ndx) = True Then
Set Rng = Range(.Item(Ndx))
Rng.PrintOut
End If
Next Ndx
End With

- Can a macro from the personal macro library do this?

You could use your personal.xls workbook to do this, but it would be easier
to maintain and distribute if you created an add-in with the form and the
code.

- How would it avoid having multiple UserForms on the same sheet?

There is no such thing as a "UserForm on a sheet". Userforms exist
independently of the sheet.

- Would the UserForms be saved with each worksheet? (I would not want
that.)

No. Once the userform is unloaded, any information in the controls is lost.
Thus, when the form is loaded, you would need to load the named ranges into
the listbox. Once dismissed, the contents of the listbox are destroyed.

- Should I use the Checkbox from 'Controls' or 'Form'?

On a UserForm, you don't have a choice. You use the controls from the VBA
Toolbox, which are controls in the MSFORMS library.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

 

[Norm Lundquist]

Thanks, Chip!! I appreciate your great ideas! I've never done an Add-In,
but it sounds like the way to go.

Norm