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.
--

HTH

Bob Phillips

"Bruce Roberson" wrote in message
...
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

Its Excel 2000, and I ocassionally use Excel 97 at my house.
"Bob Phillips" wrote in message
...
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.
--

HTH

Bob Phillips

"Bruce Roberson" wrote in message
...
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

It won't work with 97. New in 2000.

--

HTH

Bob Phillips

"Bruce Roberson" wrote in message
...
Its Excel 2000, and I ocassionally use Excel 97 at my house.
"Bob Phillips" wrote in message
...
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.
--

HTH

Bob Phillips

"Bruce Roberson" wrote in message
...
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

OK, how do I make it modeless in Excel 2000 then. Is it a property, or is it
additional code?
"Bob Phillips" wrote in message
...
It won't work with 97. New in 2000.

--

HTH

Bob Phillips

"Bruce Roberson" wrote in message
...
Its Excel 2000, and I ocassionally use Excel 97 at my house.
"Bob Phillips" wrote in message
...
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.
--

HTH

Bob Phillips

"Bruce Roberson" wrote in message
...
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,

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

"Bruce Roberson" wrote in message
...
OK, how do I make it modeless in Excel 2000 then. Is it a property, or is
it
additional code?
"Bob Phillips" wrote in message
...
It won't work with 97. New in 2000.

--

HTH

Bob Phillips

"Bruce Roberson" wrote in message
...
Its Excel 2000, and I ocassionally use Excel 97 at my house.
"Bob Phillips" wrote in message
...
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.
--

HTH

Bob Phillips

"Bruce Roberson" wrote in message
...
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