A Print Button macro that is based on Checkbox macros
 

I created an Excel spreadsheet (Windows XP) which contains a macro
Print Button from the forms toolbar that prints a hidden worksheet.
However, I was wondering if it's possible to incorporate checkbox
macros in the active window that will dictate which range of cells to
be printed when the Print Button macro pressed. This will allow the
user to print various sections (range of cells) of the hidden
worksheet depending on which checkbox is checked.

Thank you,

Mstr G.

A Print Button macro that is based on Checkbox macros
 

If you're careful with the setup, it should be fairly straight forward.

I created 3 checkboxes (also from the forms toolbar) and called them:
check box 1
check box 2
check box 3
(the naming convention will be important--start at 1 and don't skip any
numbers.)

Then I assigned this code to the button (not checkboxes):

Option Explicit
Option Base 1
Sub testme()

Dim myAddresses As Variant
Dim myCBXCount As Long
Dim iCtr As Long
Dim myCBX As CheckBox
Dim histVisible
Dim RangesPrinted As Long

myAddresses = Array("a1:b9", "c2:d8", "e3:f9")
myCBXCount = ActiveSheet.CheckBoxes.Count

If myCBXCount < UBound(myAddresses) - LBound(myAddresses) + 1 Then
MsgBox "Design error #1!" & vbLf & "Please contact GaryM at xxxx."
Exit Sub
End If

Application.ScreenUpdating = False

RangesPrinted = 0
For iCtr = 1 To myCBXCount
Set myCBX = Nothing
On Error Resume Next
Set myCBX = ActiveSheet.CheckBoxes("check box " & iCtr)
On Error GoTo 0
If myCBX Is Nothing Then
MsgBox "Design error #2 with " & iCtr & " !" & vbLf _
& "Please contact GaryM at xxxx."
Else
If myCBX.Value = xlOn Then
With Worksheets("sheet2")
histVisible = .Visible
.Visible = xlSheetVisible
.Range(myAddresses(iCtr)).PrintOut
.Visible = histVisible
RangesPrinted = RangesPrinted + 1
End With
myCBX.Value = xlOff 'turn it off for next time?
End If
End If
Next iCtr

If RangesPrinted 0 Then
MsgBox "Printed " & RangesPrinted & " ranges!"
Else
MsgBox "Nothing printed--check a box or two!"
End If

Application.ScreenUpdating = True

End Sub

You'll want to have as many addresses in this line:
myAddresses = Array("a1:b9", "c2:d8", "e3:f9")
as you have checkboxes.

And go in that same order: 1, 2, 3,...



Mstr G wrote:

I created an Excel spreadsheet (Windows XP) which contains a macro
Print Button from the forms toolbar that prints a hidden worksheet.
However, I was wondering if it's possible to incorporate checkbox
macros in the active window that will dictate which range of cells to
be printed when the Print Button macro pressed. This will allow the
user to print various sections (range of cells) of the hidden
worksheet depending on which checkbox is checked.

Thank you,

Mstr G.

--

Dave Peterson

A Print Button macro that is based on Checkbox macros
 

Mr. Peterson,

Thank you for your prompt attention to my problem, however after
incorporating your code, a msgbox appears referenceing "design error
#2 with 1 !" , thus requiring me to click the OK button several times
for it to go thru 6 checkboxes, the "Contact GaryM" and "Check a box
or two!" messages before I can return to my original screen. Maybe
with the following info you can identify and resolve my problem.
First of all, the name of the hidden worksheet I want to print from is
"NOV-WKLY". Six checkboxes should reflect the following range of
cells ("a1:k51", "l1:v51", "w1:ag51", "ah1"ar51", "as1:bc51",
"bd1:bm51"). The name of my worksheet where my print button and
checkboxes appear is "NOV04". Should my Checkbox macros contain
additional info rather than only:

Sub CheckBox1_Click()

End Sub

Hopefully you can help me.

Thanks again,

Mstr G.



Dave Peterson wrote in message ...
If you're careful with the setup, it should be fairly straight forward.

I created 3 checkboxes (also from the forms toolbar) and called them:
check box 1
check box 2
check box 3
(the naming convention will be important--start at 1 and don't skip any
numbers.)

Then I assigned this code to the button (not checkboxes):

Option Explicit
Option Base 1
Sub testme()

Dim myAddresses As Variant
Dim myCBXCount As Long
Dim iCtr As Long
Dim myCBX As CheckBox
Dim histVisible
Dim RangesPrinted As Long

myAddresses = Array("a1:b9", "c2:d8", "e3:f9")
myCBXCount = ActiveSheet.CheckBoxes.Count

If myCBXCount < UBound(myAddresses) - LBound(myAddresses) + 1 Then
MsgBox "Design error #1!" & vbLf & "Please contact GaryM at xxxx."
Exit Sub
End If

Application.ScreenUpdating = False

RangesPrinted = 0
For iCtr = 1 To myCBXCount
Set myCBX = Nothing
On Error Resume Next
Set myCBX = ActiveSheet.CheckBoxes("check box " & iCtr)
On Error GoTo 0
If myCBX Is Nothing Then
MsgBox "Design error #2 with " & iCtr & " !" & vbLf _
& "Please contact GaryM at xxxx."
Else
If myCBX.Value = xlOn Then
With Worksheets("sheet2")
histVisible = .Visible
.Visible = xlSheetVisible
.Range(myAddresses(iCtr)).PrintOut
.Visible = histVisible
RangesPrinted = RangesPrinted + 1
End With
myCBX.Value = xlOff 'turn it off for next time?
End If
End If
Next iCtr

If RangesPrinted 0 Then
MsgBox "Printed " & RangesPrinted & " ranges!"
Else
MsgBox "Nothing printed--check a box or two!"
End If

Application.ScreenUpdating = True

End Sub

You'll want to have as many addresses in this line:
myAddresses = Array("a1:b9", "c2:d8", "e3:f9")
as you have checkboxes.

And go in that same order: 1, 2, 3,...



Mstr G wrote:

I created an Excel spreadsheet (Windows XP) which contains a macro
Print Button from the forms toolbar that prints a hidden worksheet.
However, I was wondering if it's possible to incorporate checkbox
macros in the active window that will dictate which range of cells to
be printed when the Print Button macro pressed. This will allow the
user to print various sections (range of cells) of the hidden
worksheet depending on which checkbox is checked.

Thank you,

Mstr G.

A Print Button macro that is based on Checkbox macros
 

Before you do anything more, you'll have to replace the checkboxes from the
control toolbox with checkboxes from the forms toolbar.

And when you add those checkboxes, watch the namebox (to the left of the formula
bar).

If the names don't look like:
check box 1
check box 2
check box 3
(etc)

You can rename them in that same namebox (when each is selected). Remember to
hit enter when you're done fixing the name.

I also used a button from the forms toolbar -- not the control toolbox toolbar
-- and assigned the macro to that button.





Mstr G wrote:

Mr. Peterson,

Thank you for your prompt attention to my problem, however after
incorporating your code, a msgbox appears referenceing "design error
#2 with 1 !" , thus requiring me to click the OK button several times
for it to go thru 6 checkboxes, the "Contact GaryM" and "Check a box
or two!" messages before I can return to my original screen. Maybe
with the following info you can identify and resolve my problem.
First of all, the name of the hidden worksheet I want to print from is
"NOV-WKLY". Six checkboxes should reflect the following range of
cells ("a1:k51", "l1:v51", "w1:ag51", "ah1"ar51", "as1:bc51",
"bd1:bm51"). The name of my worksheet where my print button and
checkboxes appear is "NOV04". Should my Checkbox macros contain
additional info rather than only:

Sub CheckBox1_Click()

End Sub

Hopefully you can help me.

Thanks again,

Mstr G.

Dave Peterson wrote in message ...
If you're careful with the setup, it should be fairly straight forward.

I created 3 checkboxes (also from the forms toolbar) and called them:
check box 1
check box 2
check box 3
(the naming convention will be important--start at 1 and don't skip any
numbers.)

Then I assigned this code to the button (not checkboxes):

Option Explicit
Option Base 1
Sub testme()

Dim myAddresses As Variant
Dim myCBXCount As Long
Dim iCtr As Long
Dim myCBX As CheckBox
Dim histVisible
Dim RangesPrinted As Long

myAddresses = Array("a1:b9", "c2:d8", "e3:f9")
myCBXCount = ActiveSheet.CheckBoxes.Count

If myCBXCount < UBound(myAddresses) - LBound(myAddresses) + 1 Then
MsgBox "Design error #1!" & vbLf & "Please contact GaryM at xxxx."
Exit Sub
End If

Application.ScreenUpdating = False

RangesPrinted = 0
For iCtr = 1 To myCBXCount
Set myCBX = Nothing
On Error Resume Next
Set myCBX = ActiveSheet.CheckBoxes("check box " & iCtr)
On Error GoTo 0
If myCBX Is Nothing Then
MsgBox "Design error #2 with " & iCtr & " !" & vbLf _
& "Please contact GaryM at xxxx."
Else
If myCBX.Value = xlOn Then
With Worksheets("sheet2")
histVisible = .Visible
.Visible = xlSheetVisible
.Range(myAddresses(iCtr)).PrintOut
.Visible = histVisible
RangesPrinted = RangesPrinted + 1
End With
myCBX.Value = xlOff 'turn it off for next time?
End If
End If
Next iCtr

If RangesPrinted 0 Then
MsgBox "Printed " & RangesPrinted & " ranges!"
Else
MsgBox "Nothing printed--check a box or two!"
End If

Application.ScreenUpdating = True

End Sub

You'll want to have as many addresses in this line:
myAddresses = Array("a1:b9", "c2:d8", "e3:f9")
as you have checkboxes.

And go in that same order: 1, 2, 3,...



Mstr G wrote:

I created an Excel spreadsheet (Windows XP) which contains a macro
Print Button from the forms toolbar that prints a hidden worksheet.
However, I was wondering if it's possible to incorporate checkbox
macros in the active window that will dictate which range of cells to
be printed when the Print Button macro pressed. This will allow the
user to print various sections (range of cells) of the hidden
worksheet depending on which checkbox is checked.

Thank you,

Mstr G.

--

Dave Peterson