Add to finally successful dialog box
 

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

Add to finally successful dialog box
 

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

Add to finally successful dialog box
 

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

Add to finally successful dialog box
 

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

Add to finally successful dialog box
 

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

Add to finally successful dialog box
 

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

Add to finally successful dialog box
 

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

"Bob Phillips" wrote in message
...
Steve,

I haven't been able to get this to work when the ranges are on different
sheets as Bruce's are.

--

HTH

Bob Phillips

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

Add to finally successful dialog box
 

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.

--

HTH

Bob Phillips

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

"Bob Phillips" wrote in message
...
Steve,

I haven't been able to get this to work when the ranges are on different
sheets as Bruce's are.

--

HTH

Bob Phillips

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

Add to finally successful dialog box
 

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

"Bob Phillips" wrote in message
...
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.

--

HTH

Bob Phillips

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

"Bob Phillips" wrote in message
...
Steve,

I haven't been able to get this to work when the ranges are on
different
sheets as Bruce's are.

--

HTH

Bob Phillips

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

Add to finally successful dialog box
 

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!

--

HTH

Bob Phillips

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

"Bob Phillips" wrote in message
...
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.

--

HTH

Bob Phillips

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

"Bob Phillips" wrote in message
...
Steve,

I haven't been able to get this to work when the ranges are on
different
sheets as Bruce's are.

--

HTH

Bob Phillips

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

Add to finally successful dialog box
 

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