set up toggle button to set print area

At times I need to print a different section of a spreadsheet.
Is it possible to set up a toggle button to set the area for printing?
For example to print cells a1:a6 you would click on the button once but to
print cells a1:a20 you would click on it again.

Thanks in advance of a favourable reply.

Regards
John D

Yes it's possible... I'm not entirely sure what the VBA code would be. I
would recommend two buttons or at least sufficient information so that the
user knows which range is being printed.

Easiest way I think for you to develop the option is to record a macro that
has you set the print area to area 1. Then record one to set the print area
to area 2. Create a button that tells the user (either by changing the
caption of the button or the information in some cell or some other option)
which selection is current, and how to switch to the other range. That
button would/could check to see which print area was currently selected, if
it is one, then select two. If it is two then select one. If it is neither
then you should determine what should be done. (i.e., force range one, force
range two, and/or prompt user.)

The reason for a second button would be to have it print just say a
selection, vice printing the Print area. But totally up to you on
implementation.


"John Davies" wrote:

> At times I need to print a different section of a spreadsheet.
> Is it possible to set up a toggle button to set the area for printing?
> For example to print cells a1:a6 you would click on the button once but to
> print cells a1:a20 you would click on it again.
>
> Thanks in advance of a favourable reply.
>
> Regards
> John D

Place a togglebutton from the Control Toolbox Toolbar on the worksheet where
you want this behavior. double click on it to get to the click event, then
modify it to look similar to this:

Private Sub ToggleButton1_Click()
sSh = "'" & ActiveSheet.Name & "'!"
If ToggleButton1.Value Then
ToggleButton1.Caption = "A1:A6"
ActiveSheet.PageSetup.PrintArea = _
sSh & "A1:A6"
Else
ToggleButton1.Caption = "A1:A20"
ActiveSheet.PageSetup.PrintArea = _
sSh & "A1:A20"
End If
End Sub


in the dropdowns a the top, select Worksheet and Activate
put in code like this:

Private Sub Worksheet_Activate()
If ToggleButton1.Value Then
ToggleButton1.Caption = "A1:A6"
ActiveSheet.PageSetup.PrintArea = _
sSh & "A1:A6"
Else
ToggleButton1.Caption = "A1:A20"
ActiveSheet.PageSetup.PrintArea = _
sSh & "A1:A20"
End If
End Sub

Depending on what the user can do, you might need to put that code in the
selectionchange event rather than the activate event. As written, the
caption shows the current PrintArea setting. This seems most intutitive to
me. Modify to suite you individual situation.

--
Regards,
Tom Ogilvy

"John Davies" <JohnDavies@discussions.microsoft.com> wrote in message
news:BA9ADD3F-7B42-4762-8A9C-65E39BFE9839@microsoft.com...
> At times I need to print a different section of a spreadsheet.
> Is it possible to set up a toggle button to set the area for printing?
> For example to print cells a1:a6 you would click on the button once but to
> print cells a1:a20 you would click on it again.
>
> Thanks in advance of a favourable reply.
>
> Regards
> John D