Revised btnprint1_click()

[Bruce Roberson]

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]

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]

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.

 

[steve]

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]

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

 

[steve]

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)

 

[steve]

Bruce,

Almost forgot.

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

steve

 

[Bruce Roberson]

-----Original Message----- 1) >First
not > SET Printthisrange = Range("OBACGG").select
but > SET Printthisrange = Range("OBACGG")

I had already done this inside my case selections so I
don't see any other places that you were referring to here
that need correcting.

___________________________________________________________
Not

Select Case Selectprnrange
but
Select Case Printoption

I did this correction. I didn't understand that the name
of the case had to be the same as the object you are
testing. I guess that is what you are saying here.

Bottom line:

I did the correction with Case name, and the other Set
Printhistrange without the .select was already correct in
the last post. But, where it hangs is still on the same
line which now reads per your last post:

printthisrange.select

 

[steve]

Bruce,

OK! Now I am stumped.

One last thing to try. Go into the code and step thru it using the F8 key.
This will highlight each line in yellow before processing it.
Place the mouse over each of your variable (after processing each line) and
see what pops up. If the is a problem - the pop up will show = ""
I would be most concerned with Printoption and Printthisrange.

Also - do you have Option Explicity at the top of the module? This is a big
help in finding problems with variables and code.

steve