My First User Form - How to Select Range(s) and Print

[Bruce Roberson]

I have studied the referenced link below and have tried to
adapt that example to my situation.

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
(Creating a custom form in Microsoft Excel)

I have a user form with 5 option buttons on it in a frame.
Then, I have a command button to print with the click
method as shown here:


Private Sub btnprint1_Click()
Dim myOption As Control
Dim Printoption

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PaperSize = xlPaperLegal
.FitToPagesWide = 1
.FitToPagesTall = 1
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

For Each myOption In Frame1.Controls
' Obviously Frame1 is the capition on the Frame
If myOption.Value = True Then
Printoption = myOption.Caption
End If
Next myOption

Unload Me
Range(Printoption).Select
Selection.PrintPreview
Range("a1").Select


End Sub

In the line that says Range(Printoption).Select, I know
this won't be correct as shown. This is where I think I
need some assistance at this point.

Since this part is different from the example I studied, I
am not sure how to tie the option box to a range name or
if I need to go about this all a different way.

If I were to substitute a range name in quotes on the line
that says Range(Printoption).Select, then of course I
could get it to print that one range. But that is not the
point of the option selection process on the user form.

Also, the default option on the User Form is to Print All,
which means that five separate distinct ranges will have
to be printed back to back. That is why I'm not even sure
I'm on the right track in trying to select a range here
and then tell it to print preview.

The bottom line is I pretty much understood the example in
the link above. But what I need to do is different so I
needed some help before I try a bunch of different ways.


Thanks,


Bruce




..

 

[Henry]

Bruce,

What is the Caption on each of your Option buttons?
For your routine to work the captions must be ranges.
E.g. A15, A610, etc.

HTH
Henry

 

[Bruce Roberson]

So, is that all I need to do is change the caption to match the print range
name I have for these items? I was just trying to see if that was it.
Because I thought there might be more to it than that.

 

[Bruce Roberson]

Actually I want another option than to have to name the captions by the
range names. If I do that, then the name on the choice won't be what I want
it to be since it gets the display from the caption. So, is there another
way to print based on the choices of the option buttons?