yesterday, I posted a similar routine where I was trying
to pass along a control option to a print range, so that I
could print selected reports based on selecting a print
range that was passed along from this procedure.

I was seeking help with that, but I think I've figured out
another route that I still need help with.

I have five options on a frame with the default option
being the print all ranges. Someone suggested before I did
the following cases that the caption in this script had to
be a valid range name, but I didn't want to name them that
way. So, I devised this attempt at using cases to name the
print range based on the caption I listed in these cases.

Where it appears to hang now when I step through it is in
the line: Range(Printthisrange).Select

As far as I could tell, if "Printhisrange" were defined as
a range, then shouldn't I be able to pass along a name to
that variable based on my cases here. And then when the
cases selected the value of "printthisrange", shouldn't I
be able to pass that along to the line listed above?

And then once I've solved that mystery, really what I need
to do is provide for the "Print All OBA Pages" case in
which I need to select all four previous ranges for the
print.

I'm still very much a newbie at Visual Basic, but I think
I'm learning quickly.

Private Sub btnprint1_Click()
Dim myOption As Control
Dim Printoption
Dim Printthisrange As Range
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PaperSize = xlPaperLegal
.FitToPagesWide = 1
.FitToPagesTall = 1
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

For Each myOption In Frame1.Controls
If myOption.Value = True Then
Printoption = myOption.Caption
End If
Next myOption

Select Case Selectprnrange
Case 1
Printoption = "Cantera Natural Gas"
Printthisrange = "OBACGG"
Case 2
Printoption = "DEFS"
Printthisrange = "OBADEFS"
Case 3
Printoption = "Agave"
Printthisrange = "OBAAgave"
Case 4
Printoption = "TWML"
Printthisrange = "OBATWML"
Case 5
Printoption = "Print All OBA Pages"


End Select



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


End Sub

Bruce,

Since PrintThisRange is dimed as Range
than
Printthisrange = "OBACGG"
should be
Printthisrange = Range("OBACGG")
you may also want to qualify the sheet
Printthisrange = Sheets("Sheet1").Range("OBACGG")

steve

"Bruce Roberson" wrote in message
...
yesterday, I posted a similar routine where I was trying
to pass along a control option to a print range, so that I
could print selected reports based on selecting a print
range that was passed along from this procedure.

I was seeking help with that, but I think I've figured out
another route that I still need help with.

I have five options on a frame with the default option
being the print all ranges. Someone suggested before I did
the following cases that the caption in this script had to
be a valid range name, but I didn't want to name them that
way. So, I devised this attempt at using cases to name the
print range based on the caption I listed in these cases.

Where it appears to hang now when I step through it is in
the line: Range(Printthisrange).Select

As far as I could tell, if "Printhisrange" were defined as
a range, then shouldn't I be able to pass along a name to
that variable based on my cases here. And then when the
cases selected the value of "printthisrange", shouldn't I
be able to pass that along to the line listed above?

And then once I've solved that mystery, really what I need
to do is provide for the "Print All OBA Pages" case in
which I need to select all four previous ranges for the
print.

I'm still very much a newbie at Visual Basic, but I think
I'm learning quickly.

Private Sub btnprint1_Click()
Dim myOption As Control
Dim Printoption
Dim Printthisrange As Range
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PaperSize = xlPaperLegal
.FitToPagesWide = 1
.FitToPagesTall = 1
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

For Each myOption In Frame1.Controls
If myOption.Value = True Then
Printoption = myOption.Caption
End If
Next myOption

Select Case Selectprnrange
Case 1
Printoption = "Cantera Natural Gas"
Printthisrange = "OBACGG"
Case 2
Printoption = "DEFS"
Printthisrange = "OBADEFS"
Case 3
Printoption = "Agave"
Printthisrange = "OBAAgave"
Case 4
Printoption = "TWML"
Printthisrange = "OBATWML"
Case 5
Printoption = "Print All OBA Pages"


End Select



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


End Sub

Steve:

I made the change you suggested to put quotes around the
line "Printthisrange = "OBACGG" including the sheet
selection part as well, but it still stops with the same
line "Range(Printthisrange).Select", and it didn't help if
I put quotes around the printrange in the parenthesis.
Also, I still don't know how I'm going to pass something
out of case 5 to select all 4 print ranges to have them
printed at the same time. So I need ideas on that also.

-----Original Message-----
Bruce,

Since PrintThisRange is dimed as Range
than
Printthisrange = "OBACGG"
should be
Printthisrange = Range("OBACGG")
you may also want to qualify the sheet
Printthisrange = Sheets("Sheet1").Range("OBACGG")

steve

"Bruce Roberson" wrote in
message
...
yesterday, I posted a similar routine where I was trying
to pass along a control option to a print range, so
that I
could print selected reports based on selecting a print
range that was passed along from this procedure.

I was seeking help with that, but I think I've figured
out
another route that I still need help with.

I have five options on a frame with the default option
being the print all ranges. Someone suggested before I
did
the following cases that the caption in this script had
to
be a valid range name, but I didn't want to name them
that
way. So, I devised this attempt at using cases to name
the
print range based on the caption I listed in these
cases.

Where it appears to hang now when I step through it is
in
the line: Range(Printthisrange).Select

As far as I could tell, if "Printhisrange" were defined
as
a range, then shouldn't I be able to pass along a name
to
that variable based on my cases here. And then when the
cases selected the value of "printthisrange", shouldn't
I
be able to pass that along to the line listed above?

And then once I've solved that mystery, really what I
need
to do is provide for the "Print All OBA Pages" case in
which I need to select all four previous ranges for the
print.

I'm still very much a newbie at Visual Basic, but I
think
I'm learning quickly.

Private Sub btnprint1_Click()
Dim myOption As Control
Dim Printoption
Dim Printthisrange As Range
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PaperSize = xlPaperLegal
.FitToPagesWide = 1
.FitToPagesTall = 1
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

For Each myOption In Frame1.Controls
If myOption.Value = True Then
Printoption = myOption.Caption
End If
Next myOption

Select Case Selectprnrange
Case 1
Printoption = "Cantera Natural Gas"
Printthisrange = "OBACGG"
Case 2
Printoption = "DEFS"
Printthisrange = "OBADEFS"
Case 3
Printoption = "Agave"
Printthisrange = "OBAAgave"
Case 4
Printoption = "TWML"
Printthisrange = "OBATWML"
Case 5
Printoption = "Print All OBA Pages"


End Select



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


End Sub


.

Bruce,

Let's see if we have this right -
Name = MyRange refers to Sheets1!A1:G5
(made up the range)
than Range("MyRange").Select will select MyRange
Since Printthisrange is dimmed as Range, you need
Set Printthisrange = Range("MyRange")
note: "Set" since this is an object
than
Printthisrange.Select will select MyRange

Now you might want to incorporate
Selection.PrintOut Copies:=1, Collate:=True
to print out a selected range.

-------------------------

Now your Select Case doesn't look quite right
(but I am on shakey ground here)

I think you want to change this
Select Case Selectprnrange
Case 1
Printoption = "Cantera Natural Gas"
Printthisrange = "OBACGG"

to this
Select Case Printoption
Case "Cantera Natural Gas"
Printoption = "Cantera Natural Gas"
Set Printthisrange = Range("OBACGG")

and after everything is done
Set Printthisrange = Nothing

--------------------------------------------------

For Case 5, if you are trying to print the entire sheet
and if you don't have a PrintArea defined
ActiveWindow.SelectedSheets.PrintOut Copies:=1
will print everything

hope some of this helps...

steve


"Bruce Roberson" wrote in message
...
Steve:

I made the change you suggested to put quotes around the
line "Printthisrange = "OBACGG" including the sheet
selection part as well, but it still stops with the same
line "Range(Printthisrange).Select", and it didn't help if
I put quotes around the printrange in the parenthesis.
Also, I still don't know how I'm going to pass something
out of case 5 to select all 4 print ranges to have them
printed at the same time. So I need ideas on that also.

-----Original Message-----
Bruce,

Since PrintThisRange is dimed as Range
than
Printthisrange = "OBACGG"
should be
Printthisrange = Range("OBACGG")
you may also want to qualify the sheet
Printthisrange = Sheets("Sheet1").Range("OBACGG")

steve

"Bruce Roberson" wrote in
message
...
yesterday, I posted a similar routine where I was trying
to pass along a control option to a print range, so
that I
could print selected reports based on selecting a print
range that was passed along from this procedure.

I was seeking help with that, but I think I've figured
out
another route that I still need help with.

I have five options on a frame with the default option
being the print all ranges. Someone suggested before I
did
the following cases that the caption in this script had
to
be a valid range name, but I didn't want to name them
that
way. So, I devised this attempt at using cases to name
the
print range based on the caption I listed in these
cases.

Where it appears to hang now when I step through it is
in
the line: Range(Printthisrange).Select

As far as I could tell, if "Printhisrange" were defined
as
a range, then shouldn't I be able to pass along a name
to
that variable based on my cases here. And then when the
cases selected the value of "printthisrange", shouldn't
I
be able to pass that along to the line listed above?

And then once I've solved that mystery, really what I
need
to do is provide for the "Print All OBA Pages" case in
which I need to select all four previous ranges for the
print.

I'm still very much a newbie at Visual Basic, but I
think
I'm learning quickly.

Private Sub btnprint1_Click()
Dim myOption As Control
Dim Printoption
Dim Printthisrange As Range
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PaperSize = xlPaperLegal
.FitToPagesWide = 1
.FitToPagesTall = 1
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

For Each myOption In Frame1.Controls
If myOption.Value = True Then
Printoption = myOption.Caption
End If
Next myOption

Select Case Selectprnrange
Case 1
Printoption = "Cantera Natural Gas"
Printthisrange = "OBACGG"
Case 2
Printoption = "DEFS"
Printthisrange = "OBADEFS"
Case 3
Printoption = "Agave"
Printthisrange = "OBAAgave"
Case 4
Printoption = "TWML"
Printthisrange = "OBATWML"
Case 5
Printoption = "Print All OBA Pages"


End Select



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


End Sub


.

Steve:

I'm sorry if I appear to be dense today, but I think I
followed your last post. Maybe I missed part of your
instruction. However, the thing still stalls out at the
same point each time. I put in your change which was:
SET Printthisrange = Range("OBACGG").select

The stalling line again is:
Range("Printthisrange").Select

Here reposted below is the entire Sub as it appears at
this point.

With regards to your Case 5 suggestion, I can't Print all
4 ranges by printing the entire sheet because these 4
reports are all on separate sheets. So, is there a way to
select multiple ranges with the Set statement, something
like this?:

Set Printthisrange = Range("OBACGG"), Range("OBADEFS"),
Range("OBAAgave"), Range("OBATWML")
And then, would the line:

Selection.printpreview

work with all them at the same time?
__________________________________________________ _________

Private Sub btnprint1_Click()
Dim myOption As Control
Dim Printoption
Dim Printthisrange As Range


For Each myOption In Frame1.Controls
If myOption.Value = True Then
Printoption = myOption.Caption
End If
Next myOption

Select Case Selectprnrange
Case 1
Printoption = "Cantera Natural Gas"
Set Printthisrange = Range("OBACGG")
Case 2
Printoption = "DEFS"
Set Printthisrange = Range("OBADEFS")
Case 3
Printoption = "Agave"
Set Printthisrange = Range("OBAAgave")
Case 4
Printoption = "TWML"
Set Printthisrange = Range("OBATWML")
Case 5
Printoption = "Print All OBA Pages"


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

Unload Me
Range("Printthisrange").Select
Selection.PrintPreview
Set Printthisrange = Nothing
Range("a1").Select


End Sub

Bruce,

I hope this doesn't sound harsh, it isn't meant to be.
Just straight forward.

Hope the below gets the crinkles out.
If not, post back...

steve

First
not SET Printthisrange = Range("OBACGG").select
but SET Printthisrange = Range("OBACGG")

remember when using Set
Set objectname = object
in your case object is a range
---------------------------------

Select Case Value
Case Value1
whatever you want
Case Value2
whatever you want

not
Select Case Selectprnrange
Case 1
Printoption = "Cantera Natural Gas"
Set Printthisrange = Range("OBACGG")
Case 2
Printoption = "DEFS"
Set Printthisrange = Range("OBADEFS")
Case 3
Printoption = "Agave"
Set Printthisrange = Range("OBAAgave")
Case 4
Printoption = "TWML"
Set Printthisrange = Range("OBATWML")
Case 5
Printoption = "Print All OBA Pages"

but
Select Case Printoption
Case "Cantera Natural Gas"
Set Printthisrange = Range("OBACGG")
Case "DEFS"
Set Printthisrange = Range("OBADEFS")
Case "Agave"
Set Printthisrange = Range("OBAAgave")
Case "TWML"
Set Printthisrange = Range("OBATWML")
Case "Print All OBA Pages"
Set Printthisrange = ?????? ' (i don't know what you need
here)



"Bruce Roberson" wrote in message
...
Steve:

I'm sorry if I appear to be dense today, but I think I
followed your last post. Maybe I missed part of your
instruction. However, the thing still stalls out at the
same point each time. I put in your change which was:
SET Printthisrange = Range("OBACGG").select

The stalling line again is:
Range("Printthisrange").Select

Here reposted below is the entire Sub as it appears at
this point.

With regards to your Case 5 suggestion, I can't Print all
4 ranges by printing the entire sheet because these 4
reports are all on separate sheets. So, is there a way to
select multiple ranges with the Set statement, something
like this?:

Set Printthisrange = Range("OBACGG"), Range("OBADEFS"),
Range("OBAAgave"), Range("OBATWML")
And then, would the line:

Selection.printpreview

work with all them at the same time?
__________________________________________________ _________

Private Sub btnprint1_Click()
Dim myOption As Control
Dim Printoption
Dim Printthisrange As Range


For Each myOption In Frame1.Controls
If myOption.Value = True Then
Printoption = myOption.Caption
End If
Next myOption

Select Case Selectprnrange
Case 1
Printoption = "Cantera Natural Gas"
Set Printthisrange = Range("OBACGG")
Case 2
Printoption = "DEFS"
Set Printthisrange = Range("OBADEFS")
Case 3
Printoption = "Agave"
Set Printthisrange = Range("OBAAgave")
Case 4
Printoption = "TWML"
Set Printthisrange = Range("OBATWML")
Case 5
Printoption = "Print All OBA Pages"


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

Unload Me
Range("Printthisrange").Select
Selection.PrintPreview
Set Printthisrange = Nothing
Range("a1").Select


End Sub

Bruce,

Almost forgot.

Since Printthisrange is declared as a range you can use
Printthisrange.Select

steve

"Bruce Roberson" wrote in message
...
Steve:

I'm sorry if I appear to be dense today, but I think I
followed your last post. Maybe I missed part of your
instruction. However, the thing still stalls out at the
same point each time. I put in your change which was:
SET Printthisrange = Range("OBACGG").select

The stalling line again is:
Range("Printthisrange").Select

Here reposted below is the entire Sub as it appears at
this point.

With regards to your Case 5 suggestion, I can't Print all
4 ranges by printing the entire sheet because these 4
reports are all on separate sheets. So, is there a way to
select multiple ranges with the Set statement, something
like this?:

Set Printthisrange = Range("OBACGG"), Range("OBADEFS"),
Range("OBAAgave"), Range("OBATWML")
And then, would the line:

Selection.printpreview

work with all them at the same time?
__________________________________________________ _________

Private Sub btnprint1_Click()
Dim myOption As Control
Dim Printoption
Dim Printthisrange As Range


For Each myOption In Frame1.Controls
If myOption.Value = True Then
Printoption = myOption.Caption
End If
Next myOption

Select Case Selectprnrange
Case 1
Printoption = "Cantera Natural Gas"
Set Printthisrange = Range("OBACGG")
Case 2
Printoption = "DEFS"
Set Printthisrange = Range("OBADEFS")
Case 3
Printoption = "Agave"
Set Printthisrange = Range("OBAAgave")
Case 4
Printoption = "TWML"
Set Printthisrange = Range("OBATWML")
Case 5
Printoption = "Print All OBA Pages"


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

Unload Me
Range("Printthisrange").Select
Selection.PrintPreview
Set Printthisrange = Nothing
Range("a1").Select


End Sub