Add to finally successful dialog box

  • Thread starter Thread starter Bruce Roberson
  • Start date Start date
B

Bruce Roberson

I must say that learning how to do my first dialog box and
getting the code to work was QUITE a bit larger task than
I thought it would be. But with help from you guys,
especially Steve who stayed with me all the way, I got it
to work, except for some fine tuning.

I have 4 cases completed with the 5th to come. If the user
wants to print all 4 ranges, then I have to figure out how
to go about setting it up to select 4 separate ranges on 4
separate sheets of the workbook. Can that be done in the
Cases as shown in the completed procedure to date?

In other words, is there a way to specify 4 ranges in a
set printthisrange statement? For example, I tried this
statement below with no success:

Set Printthisrange = Range
("OBACGG","OBADEFS","OBAAgave","OBATWML")

From locking up the machine, it seems I have to do all my
selecting and then I have to close the dialog box before I
try and do a print preview obviusly. So, I am trying to
not have to loop back into the dialog box.

__________________________________________________________
Private Sub btnprint1_Click()
Dim Printthisrange
Dim mysheet As Worksheet

Select Case True
Case OptCNGOBA.Value
Set Printthisrange = Range("OBACGG")
Set mysheet = Worksheets("OBA CGG")
Case OptDEFSOBA.Value
Set Printthisrange = Range("OBADEFS")
Set mysheet = Worksheets("OBA DEFS")
Case OptAgaveOBA.Value
Set Printthisrange = Range("OBAAgave")
Set mysheet = Worksheets("OBA Agave")
Case OptTWMLOBA.Value
Set Printthisrange = Range("OBATWML")
Set mysheet = Worksheets("OBA TWML")
Case Optalloba

End Select

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

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


End Sub
 
Bruce,

Do you have Excel 2000 or XP? If so you could set the form to be modeless,
and then it wouldn't lock up, so you would not need to unload the form, and
you could print each range one at a time.
 
OK, how do I make it modeless in Excel 2000 then. Is it a property, or is it
additional code?
 
Bruce,

First - thanks for the kind words!

Congratulations on getting it to work!

Now - just did some playing and learned something new!!!
The record function came in real handy in leading the way
to the code...
You can easily specify multiple ranges and have them print out
on separate sheets.

Dim Printthisrange As Range
'
Set Printthisrange = Range("one,two,three,four")
Printthisrange.PrintOut Copies:=1, Collate:=True
Set Printthisrange = Nothing

replace one, two, three, four with your names...

steve
 
Bob,

You're right. I made the "assumption" that they were all on the same sheet.

My oops!

My guess would be to make a loop and print each range from there.

steve
 
Steve,

That was my suggestion to Bruce (offline), but he needed to access some of
the variables set up, which meant he couldn 't unload the form, which caused
a lock-out when he issued the print command. That is why I suggested
modeless forms.
 
Bob,

Since I am using Excel97, I don't go modeless. But I would have thought he
could still loop, if the loop also selected the correct worksheet.

I know that with Excel, if you can think it, you can usually find a way to
do it!

steve
 
Steve,

Don't remember the exact details, but Bruce sent me the code but I seem to
recall there was a problem in launching some other code to print whilst the
form was up, and if you unload it, the code fails. It seemed to me that the
whole approach needed a re-design to setup the ranges to print, store them
away from the form, unload the form, and then pick and print those ranges.
But I didn't have the energy that night.

Could have tried to hide the form I suppose!
 
Oh, then I read on down, and saw that my different sheets thing is a problem
for the multiple print ranges. Oh well, we'll keep plugging. I'm still on
that project but also on a new project simulataenouosly, which is where I've
spent most of my energy today. And you've no doubt seen some of my posts
outside of this form design and print issue today.
 
Back
Top