Can I disable a macro in "Thisworkbook" with true or false?

Here is my challenge… Someone helped me right the following code
that automatically selects the number of pages to print. But...

I would like to use a check box in the workbook to put a “True” or “False”
in a cell, for example “S1” that would disable the above code. End goal:
Some users could ignore the check box and the worksheet would print as many
pages as are indicated in “S2” and the more advanced users could check the
box, thus returning the print parameters to them… enabling them to select any
combination of pages to print.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rng As Range

Set rng = ActiveSheet.Range("S2")
On Error GoTo XIT

Application.EnableEvents = False
Cancel = True
ActiveSheet.PrintOut from:=1, to:=rng.Value

XIT:
Application.EnableEvents = True
End Sub


Any ideas? Thanks for your time....

Add a checkbox from the control toolbox (not the forms toolbar) to the sheet
you want. Right click on the checkbox and select properties. Change the
caption to "Whatever" and change the print object to false (if you want). now
chage your code similar to this...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rng As Range

if Check_Box1.value = true then
Set rng = ActiveSheet.Range("S2")
On Error GoTo XIT

Application.EnableEvents = False
Cancel = True
ActiveSheet.PrintOut from:=1, to:=rng.Value
end if
XIT:
Application.EnableEvents = True
End Sub


--
HTH...

Jim Thomlinson


"Husker87" wrote:

> Here is my challenge… Someone helped me right the following code
> that automatically selects the number of pages to print. But...
>
> I would like to use a check box in the workbook to put a “True” or “False”
> in a cell, for example “S1” that would disable the above code. End goal:
> Some users could ignore the check box and the worksheet would print as many
> pages as are indicated in “S2” and the more advanced users could check the
> box, thus returning the print parameters to them… enabling them to select any
> combination of pages to print.
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> Dim rng As Range
>
> Set rng = ActiveSheet.Range("S2")
> On Error GoTo XIT
>
> Application.EnableEvents = False
> Cancel = True
> ActiveSheet.PrintOut from:=1, to:=rng.Value
>
> XIT:
> Application.EnableEvents = True
> End Sub
>
>
> Any ideas? Thanks for your time....
>
>

Jim,
I'm getting hung up in the naming of the checkbox... (from the control
toolbox)

in the code is says...
if Check_Box1.value = true then
but
when I add a checkbox it is named, "CheckBox1" I have been trying to change
one or the other to make them the same but with no succes. Any ideas on what
I'm missing? (other than VBA skill)

"Jim Thomlinson" wrote:

> Add a checkbox from the control toolbox (not the forms toolbar) to the sheet
> you want. Right click on the checkbox and select properties. Change the
> caption to "Whatever" and change the print object to false (if you want). now
> chage your code similar to this...
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> Dim rng As Range
>
> if Check_Box1.value = true then
> Set rng = ActiveSheet.Range("S2")
> On Error GoTo XIT
>
> Application.EnableEvents = False
> Cancel = True
> ActiveSheet.PrintOut from:=1, to:=rng.Value
> end if
> XIT:
> Application.EnableEvents = True
> End Sub
>
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Husker87" wrote:
>
> > Here is my challenge… Someone helped me right the following code
> > that automatically selects the number of pages to print. But...
> >
> > I would like to use a check box in the workbook to put a “True” or “False”
> > in a cell, for example “S1” that would disable the above code. End goal:
> > Some users could ignore the check box and the worksheet would print as many
> > pages as are indicated in “S2” and the more advanced users could check the
> > box, thus returning the print parameters to them… enabling them to select any
> > combination of pages to print.
> >
> > Private Sub Workbook_BeforePrint(Cancel As Boolean)
> > Dim rng As Range
> >
> > Set rng = ActiveSheet.Range("S2")
> > On Error GoTo XIT
> >
> > Application.EnableEvents = False
> > Cancel = True
> > ActiveSheet.PrintOut from:=1, to:=rng.Value
> >
> > XIT:
> > Application.EnableEvents = True
> > End Sub
> >
> >
> > Any ideas? Thanks for your time....
> >
> >

Go into design mode (another icon on that control toolbox toolbar).

Select the checkbox
You can change the name of that checkbox in the namebox (to the left of the
formula bar).
(and exit design mode)

But if you workbook has more than one sheet, I would think you'd want to be more
specific.

There's lots of ways to print a workbook. I think I'd use a dedicated macro to
print what I want--instead of using workbook_beforeprint....

But...

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myVal As Long
Cancel = True
With Me.Worksheets("sheet1")
If .CheckBox1.Value = True Then
myVal = .Range("S2").Value
If IsNumeric(myVal) Then
'keep going
If myVal < 1 Then
myVal = 1
End If
If myVal > 10 Then
myVal = 10 'do you have a maximum
End If
Application.EnableEvents = False
.PrintOut from:=1, to:=myVal
Application.EnableEvents = True
End If
End If
End With
End Sub


_might_ be one way to do what you want.

Husker87 wrote:
>
> Jim,
> I'm getting hung up in the naming of the checkbox... (from the control
> toolbox)
>
> in the code is says...
> if Check_Box1.value = true then
> but
> when I add a checkbox it is named, "CheckBox1" I have been trying to change
> one or the other to make them the same but with no succes. Any ideas on what
> I'm missing? (other than VBA skill)
>
> "Jim Thomlinson" wrote:
>
> > Add a checkbox from the control toolbox (not the forms toolbar) to the sheet
> > you want. Right click on the checkbox and select properties. Change the
> > caption to "Whatever" and change the print object to false (if you want). now
> > chage your code similar to this...
> >
> > Private Sub Workbook_BeforePrint(Cancel As Boolean)
> > Dim rng As Range
> >
> > if Check_Box1.value = true then
> > Set rng = ActiveSheet.Range("S2")
> > On Error GoTo XIT
> >
> > Application.EnableEvents = False
> > Cancel = True
> > ActiveSheet.PrintOut from:=1, to:=rng.Value
> > end if
> > XIT:
> > Application.EnableEvents = True
> > End Sub
> >
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Husker87" wrote:
> >
> > > Here is my challenge… Someone helped me right the following code
> > > that automatically selects the number of pages to print. But...
> > >
> > > I would like to use a check box in the workbook to put a “True” or “False”
> > > in a cell, for example “S1” that would disable the above code. End goal:
> > > Some users could ignore the check box and the worksheet would print as many
> > > pages as are indicated in “S2” and the more advanced users could check the
> > > box, thus returning the print parameters to them… enabling them to select any
> > > combination of pages to print.
> > >
> > > Private Sub Workbook_BeforePrint(Cancel As Boolean)
> > > Dim rng As Range
> > >
> > > Set rng = ActiveSheet.Range("S2")
> > > On Error GoTo XIT
> > >
> > > Application.EnableEvents = False
> > > Cancel = True
> > > ActiveSheet.PrintOut from:=1, to:=rng.Value
> > >
> > > XIT:
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > >
> > > Any ideas? Thanks for your time....
> > >
> > >

--

Dave Peterson

I got your macro to work… but when the box isn’t check they can’t print.
Maybe this can’t be done but here is the concept. Workbook has 5 worksheets.
There is a macro in Thisworkbook that looks at the number in cell “S2” on
each worksheet when that worksheet is selected for printing and only prints
that many pages. I wanted a check box on one of the worksheets that, when
checked, the number of pages allowed to print would come from cell “S2” and
when it was not checked the user could select the number(s) of pages to print
just like normal.

Is that even possible? And thanks for time already spent….


"Dave Peterson" wrote:

> Go into design mode (another icon on that control toolbox toolbar).
>
> Select the checkbox
> You can change the name of that checkbox in the namebox (to the left of the
> formula bar).
> (and exit design mode)
>
> But if you workbook has more than one sheet, I would think you'd want to be more
> specific.
>
> There's lots of ways to print a workbook. I think I'd use a dedicated macro to
> print what I want--instead of using workbook_beforeprint....
>
> But...
>
> Option Explicit
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> Dim myVal As Long
> Cancel = True
> With Me.Worksheets("sheet1")
> If .CheckBox1.Value = True Then
> myVal = .Range("S2").Value
> If IsNumeric(myVal) Then
> 'keep going
> If myVal < 1 Then
> myVal = 1
> End If
> If myVal > 10 Then
> myVal = 10 'do you have a maximum
> End If
> Application.EnableEvents = False
> .PrintOut from:=1, to:=myVal
> Application.EnableEvents = True
> End If
> End If
> End With
> End Sub
>
>
> _might_ be one way to do what you want.
>
> Husker87 wrote:
> >
> > Jim,
> > I'm getting hung up in the naming of the checkbox... (from the control
> > toolbox)
> >
> > in the code is says...
> > if Check_Box1.value = true then
> > but
> > when I add a checkbox it is named, "CheckBox1" I have been trying to change
> > one or the other to make them the same but with no succes. Any ideas on what
> > I'm missing? (other than VBA skill)
> >
> > "Jim Thomlinson" wrote:
> >
> > > Add a checkbox from the control toolbox (not the forms toolbar) to the sheet
> > > you want. Right click on the checkbox and select properties. Change the
> > > caption to "Whatever" and change the print object to false (if you want). now
> > > chage your code similar to this...
> > >
> > > Private Sub Workbook_BeforePrint(Cancel As Boolean)
> > > Dim rng As Range
> > >
> > > if Check_Box1.value = true then
> > > Set rng = ActiveSheet.Range("S2")
> > > On Error GoTo XIT
> > >
> > > Application.EnableEvents = False
> > > Cancel = True
> > > ActiveSheet.PrintOut from:=1, to:=rng.Value
> > > end if
> > > XIT:
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > >
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Husker87" wrote:
> > >
> > > > Here is my challenge… Someone helped me right the following code
> > > > that automatically selects the number of pages to print. But...
> > > >
> > > > I would like to use a check box in the workbook to put a “True” or “False”
> > > > in a cell, for example “S1” that would disable the above code. End goal:
> > > > Some users could ignore the check box and the worksheet would print as many
> > > > pages as are indicated in “S2” and the more advanced users could check the
> > > > box, thus returning the print parameters to them… enabling them to select any
> > > > combination of pages to print.
> > > >
> > > > Private Sub Workbook_BeforePrint(Cancel As Boolean)
> > > > Dim rng As Range
> > > >
> > > > Set rng = ActiveSheet.Range("S2")
> > > > On Error GoTo XIT
> > > >
> > > > Application.EnableEvents = False
> > > > Cancel = True
> > > > ActiveSheet.PrintOut from:=1, to:=rng.Value
> > > >
> > > > XIT:
> > > > Application.EnableEvents = True
> > > > End Sub
> > > >
> > > >
> > > > Any ideas? Thanks for your time....
> > > >
> > > >
>
> --
>
> Dave Peterson
>

First, I'd turn off normal printing.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
MsgBox "Please use the special print button to print your sheet"
Cancel = True
End Sub


And only allow them to print via a button on each worksheet.

I'd put a button from the forms toolbar on each worksheet. And drop the
checkbox completely. Just let them print as many copies as they have in S2 of
that sheet.

Option Explicit
Sub testme()
Dim myVal As Variant
Dim Msg As String
Msg = ""
With ActiveSheet
myVal = .Range("S2").Value
If IsNumeric(myVal) Then
'keep going
If myVal < 1 Then
myVal = 1
Msg = "Copies changed to 1"
End If
If myVal > 10 Then
myVal = 10 'do you have a maximum
Msg = "Copies changed to 10"
End If
Application.EnableEvents = False
.PrintOut from:=1, to:=myVal
Application.EnableEvents = True
Else
Msg = "Invalid entry in S2"
End If

If Msg = "" Then
'do nothing, everything ok
Else
MsgBox Msg
End If

End With
End Sub



Husker87 wrote:
>
> I got your macro to work… but when the box isn’t check they can’t print.
> Maybe this can’t be done but here is the concept. Workbook has 5 worksheets.
> There is a macro in Thisworkbook that looks at the number in cell “S2” on
> each worksheet when that worksheet is selected for printing and only prints
> that many pages. I wanted a check box on one of the worksheets that, when
> checked, the number of pages allowed to print would come from cell “S2” and
> when it was not checked the user could select the number(s) of pages to print
> just like normal.
>
> Is that even possible? And thanks for time already spent….
>
> "Dave Peterson" wrote:
>
> > Go into design mode (another icon on that control toolbox toolbar).
> >
> > Select the checkbox
> > You can change the name of that checkbox in the namebox (to the left of the
> > formula bar).
> > (and exit design mode)
> >
> > But if you workbook has more than one sheet, I would think you'd want to be more
> > specific.
> >
> > There's lots of ways to print a workbook. I think I'd use a dedicated macro to
> > print what I want--instead of using workbook_beforeprint....
> >
> > But...
> >
> > Option Explicit
> > Private Sub Workbook_BeforePrint(Cancel As Boolean)
> > Dim myVal As Long
> > Cancel = True
> > With Me.Worksheets("sheet1")
> > If .CheckBox1.Value = True Then
> > myVal = .Range("S2").Value
> > If IsNumeric(myVal) Then
> > 'keep going
> > If myVal < 1 Then
> > myVal = 1
> > End If
> > If myVal > 10 Then
> > myVal = 10 'do you have a maximum
> > End If
> > Application.EnableEvents = False
> > .PrintOut from:=1, to:=myVal
> > Application.EnableEvents = True
> > End If
> > End If
> > End With
> > End Sub
> >
> >
> > _might_ be one way to do what you want.
> >
> > Husker87 wrote:
> > >
> > > Jim,
> > > I'm getting hung up in the naming of the checkbox... (from the control
> > > toolbox)
> > >
> > > in the code is says...
> > > if Check_Box1.value = true then
> > > but
> > > when I add a checkbox it is named, "CheckBox1" I have been trying to change
> > > one or the other to make them the same but with no succes. Any ideas on what
> > > I'm missing? (other than VBA skill)
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > Add a checkbox from the control toolbox (not the forms toolbar) to the sheet
> > > > you want. Right click on the checkbox and select properties. Change the
> > > > caption to "Whatever" and change the print object to false (if you want). now
> > > > chage your code similar to this...
> > > >
> > > > Private Sub Workbook_BeforePrint(Cancel As Boolean)
> > > > Dim rng As Range
> > > >
> > > > if Check_Box1.value = true then
> > > > Set rng = ActiveSheet.Range("S2")
> > > > On Error GoTo XIT
> > > >
> > > > Application.EnableEvents = False
> > > > Cancel = True
> > > > ActiveSheet.PrintOut from:=1, to:=rng.Value
> > > > end if
> > > > XIT:
> > > > Application.EnableEvents = True
> > > > End Sub
> > > >
> > > >
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "Husker87" wrote:
> > > >
> > > > > Here is my challenge… Someone helped me right the following code
> > > > > that automatically selects the number of pages to print. But...
> > > > >
> > > > > I would like to use a check box in the workbook to put a “True” or “False”
> > > > > in a cell, for example “S1” that would disable the above code. End goal:
> > > > > Some users could ignore the check box and the worksheet would print as many
> > > > > pages as are indicated in “S2” and the more advanced users could check the
> > > > > box, thus returning the print parameters to them… enabling them to select any
> > > > > combination of pages to print.
> > > > >
> > > > > Private Sub Workbook_BeforePrint(Cancel As Boolean)
> > > > > Dim rng As Range
> > > > >
> > > > > Set rng = ActiveSheet.Range("S2")
> > > > > On Error GoTo XIT
> > > > >
> > > > > Application.EnableEvents = False
> > > > > Cancel = True
> > > > > ActiveSheet.PrintOut from:=1, to:=rng.Value
> > > > >
> > > > > XIT:
> > > > > Application.EnableEvents = True
> > > > > End Sub
> > > > >
> > > > >
> > > > > Any ideas? Thanks for your time....
> > > > >
> > > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

I'm with you... good suggestions. However soemtimes they need (want) to just
print the second or just the third page.

"Husker87" wrote:

> Here is my challenge… Someone helped me right the following code
> that automatically selects the number of pages to print. But...
>
> I would like to use a check box in the workbook to put a “True” or “False”
> in a cell, for example “S1” that would disable the above code. End goal:
> Some users could ignore the check box and the worksheet would print as many
> pages as are indicated in “S2” and the more advanced users could check the
> box, thus returning the print parameters to them… enabling them to select any
> combination of pages to print.
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> Dim rng As Range
>
> Set rng = ActiveSheet.Range("S2")
> On Error GoTo XIT
>
> Application.EnableEvents = False
> Cancel = True
> ActiveSheet.PrintOut from:=1, to:=rng.Value
>
> XIT:
> Application.EnableEvents = True
> End Sub
>
>
> Any ideas? Thanks for your time....
>
>

Then maybe just make it a training issue. Make sure everyone knows how to do
what they need to do.

Husker87 wrote:
>
> I'm with you... good suggestions. However soemtimes they need (want) to just
> print the second or just the third page.
>
> "Husker87" wrote:
>
> > Here is my challenge… Someone helped me right the following code
> > that automatically selects the number of pages to print. But...
> >
> > I would like to use a check box in the workbook to put a “True” or “False”
> > in a cell, for example “S1” that would disable the above code. End goal:
> > Some users could ignore the check box and the worksheet would print as many
> > pages as are indicated in “S2” and the more advanced users could check the
> > box, thus returning the print parameters to them… enabling them to select any
> > combination of pages to print.
> >
> > Private Sub Workbook_BeforePrint(Cancel As Boolean)
> > Dim rng As Range
> >
> > Set rng = ActiveSheet.Range("S2")
> > On Error GoTo XIT
> >
> > Application.EnableEvents = False
> > Cancel = True
> > ActiveSheet.PrintOut from:=1, to:=rng.Value
> >
> > XIT:
> > Application.EnableEvents = True
> > End Sub
> >
> >
> > Any ideas? Thanks for your time....
> >
> >

--

Dave Peterson