Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform and Checkbox - will this work?
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform and Checkbox - will this work?
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" wrote in message ... 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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform and Checkbox - will this work?
Thanks, Chip!! I appreciate your great ideas! I've never done an Add-In,
but it sounds like the way to go. Norm "Chip Pearson" wrote: 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" wrote in message ... 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
link a checkbox in a sheet to a checkbox on a userform? | Excel Programming | |||
Userform Checkbox | Excel Programming | |||
CheckBox in userform | Excel Programming | |||
CheckBox on UserForm | Excel Programming | |||
Userform and checkbox.... | Excel Programming |