VBA macro help with printing via control buttons and check boxes

[river]

I'm trying to set up a macro for printing, and the printing will be executed by a "Begin Printing" command button.
The command button and the print macro isn't part of the question, just to set the stage for my request from you folks.

In my workbook there are several worksheets. Worksheet1 of the workbook is a summary sheet in which I have two control buttons, one print command button, and seven checkboxes .

What I'm trying to accomplish is help with my macros on two things:
1. The two control buttons-- Each control button will govern one of two print packages.

One is for employee use (ControlButton1) and one for public use (ControlButton2), each specifying different ranges on each of the worksheets. Of course, the ControlButtons are mutually exclusive. Only one is active at the time, which will help the print fuction keep from malfunctioning whhen the "Go" command button is clicked (I hope!)The general logic that I have in mind is that if controlbox1.value = True
then print out the employee package dependent upon:

2. Which of any of the seven checked boxes is checked (checked = value of True). One or more boxes can be checked for the print package
I hope you can follow this!

 

[river]

Oh, btw.. this is all in spreadsheets. If it matters when I go into Designer Mode the ControlButton1 is described as Embed.form.OptionBox1. Something like that.
Thanks!

 

[Norie]

I think the code you have already might be relevant, it is after all doing the printing.

Also what are these 'print packages' and how is it determined which one to print based on the checkboxes?

 

[river]

Actually, I'm a newbie to VBA coding. I'm just sort of outlining the logic. My first dilemma is set up the option box macros to accept the range settings for the checked worksheets. All of this operates on Sheet1 (Summary).

So, if OptionBox1 is clicked (OptionBox.Value = True) the printout package (when the Command Button is activated) will include range:
Sheet2 A1:F60 if CheckBox1.Value = True (checked)
Sheet3 A1:F45 if CheckBox2.Value = False (not checked in this case)
Sheet4 A1:F50 if CheckBox3.Value = True (checked)
and so on.

If OptionBox2 is clicked (leaving OptionBox1 set to False I believe) the printout package (when the CommandButton is activated ) will include range:
Sheet2 A1:F40 if CheckBox1.Value = True
Sheet3 A1:F30 if CheckBox2.Value = False (but it could be set to True with the same range)
Sheet3 A1:F48 if CheckBox3.Value = True
and so on.

I need specific commands. I believe I'm really close but it's a somewhat complicated workbook, fortunately only having 8 worksheets.
After this step runs, I'll work on setting up the macro to specify the printer because it will execute PDFCreator, which normally only prints one sheet at a time.

Thanks for any help you can give me.
Brooke

 

[Norie]

Brooke

What code do you currently have?

What do you mean by 'specific commands'?

You must have more options than the ones you mention if you have 7 checkboxes, 2 option buttons and 2 command buttons.

 

[river]

Right now, the CheckBoxes only reveal or hide the entire worksheet to make it easier for the user to amend in his/her own data, which triggers a lot of formulas in the background. It also revealsor hides the columns on the Summary Sheet that have information taken from the corresponding worksheets. By the way, there is only one CommandButton that kicks off the print process (not yet programmed).

Once the sheets are filled in or amended I'd like the user to be able to print a subset of the cells on the sheets that are now made visible. Some of the existing code for Sheet 1 is as follows:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
'//B14 is the location of the Sheet2 checkbox, which makes the Worksheet2 visible and Column D on Sheet1 (the Summary Sheet) be '//hidden
Range("B14").Value = "Yes"
Worksheets("Sheet1").Visible = xlSheetVisible
Columns("D").Hidden = False
Else: Range("B14").Value = "No"
Worksheets("Sheet1").Visible = xlSheetHidden
Columns("D").Hidden = True
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then
'//Now B15 is the Sheet3 checkbox which reveals/hides the third worksheet & its corresponding column (E) on the Summary Sheet//'
Range("B15").Value = "Yes"
Worksheets("Sheet3").Visible = xlSheetVisible
Columns("E").Hidden = False
Else: Range("B15").Value = "No"
Worksheets("Sheet3").Visible = xlSheetHidden
Columns("E").Hidden = True
End If
End Sub

and so on for the other 6 Checkboxes and 6 Worksheets.
I have to add code so that when OptionBox1 is selected and CheckBox1 is checked, A1:F60 will be the range selected for printing on Sheet2.
If OptionBox2 is selected instead, A1:F40 will be the range selected for printing (once CommandButton1 is activated).

Similarly, for Sheet2, if CheckBox1 is checked, A1:F50 will be the ranged selected, and if CheckBox2 is checked, A1:F48 (a smaller range) will be selected for printing (once CommandButton1 is activated).

I can email you a mockup of the Summary sheet with the controls on it if you want.
Thanks!