Is there a way out there to figure out from VB code if more than one
worksheet is selected?

Thanks

Something like this...

MsgBox ActiveWindow.SelectedSheets.Count
--
HTH...

Jim Thomlinson


"Peter-d" wrote:

Is there a way out there to figure out from VB code if more than one
worksheet is selected?

Thanks

Sub HowMany()
Dim ThatMany As Long
ThatMany = ActiveWindow.SelectedSheets.Count
MsgBox ThatMany
End Sub

"Peter-d"

wrote in message
Is there a way out there to figure out from VB code if more than one
worksheet is selected?
Thanks

Just to expand a little...

I have created a workbook where I have trapped all printing and print
preview function. Different things happen for different sheets.
But it all goes wrong if the user has selected multiple sheets to print all
at once.

I tried using
ActiveSheet.Select Replace:= True
in the BeforePrint event of the workbook to try forcing the active sheet to
be the only one selected, but while this seems to work in the immediate
window, it seems to be ignore during the actual actual event.

My preference would be to determine that more than one sheet was selected
and give the user a proper warning message and cencelling the print event.

Any suggestions?

"Peter-d" wrote:

Is there a way out there to figure out from VB code if more than one
worksheet is selected?

Thanks

msgbox activewindow.selectedsheets.count

You can select different sheets in each window, though.

Peter-d wrote:

Is there a way out there to figure out from VB code if more than one
worksheet is selected?

Thanks

--

Dave Peterson

Right on... I don't use the activewindow too often. I should have looked
harder.

Thanks Jim & Jim


"Jim Thomlinson" wrote:

Something like this...

MsgBox ActiveWindow.SelectedSheets.Count
--
HTH...

Jim Thomlinson


"Peter-d" wrote:

Is there a way out there to figure out from VB code if more than one
worksheet is selected?

Thanks

Nevermind this... Jim and Jim provided me with my solution.

"Peter-d" wrote:

Just to expand a little...

I have created a workbook where I have trapped all printing and print
preview function. Different things happen for different sheets.
But it all goes wrong if the user has selected multiple sheets to print all
at once.

I tried using
ActiveSheet.Select Replace:= True
in the BeforePrint event of the workbook to try forcing the active sheet to
be the only one selected, but while this seems to work in the immediate
window, it seems to be ignore during the actual actual event.

My preference would be to determine that more than one sheet was selected
and give the user a proper warning message and cencelling the print event.

Any suggestions?

"Peter-d" wrote:

Is there a way out there to figure out from VB code if more than one
worksheet is selected?

Thanks

Thanks Dave.

The ability to select different sheet in each window won't bother me. If you
look at my own reply, you will see that I just need to know before I print,
and since each window will handle their print selection individually, it
wan't matter.

But thatnks for the activewindow.selectedsheets tip.

"Dave Peterson" wrote:

msgbox activewindow.selectedsheets.count

You can select different sheets in each window, though.

Peter-d wrote:

Is there a way out there to figure out from VB code if more than one
worksheet is selected?

Thanks

--

Dave Peterson

I don't think you can ever know.

Inside the file|Print dialog, there's an option for "entire workbook".

I think the best you could do would be to disable the built in printing.

In the ThisWorkbook module.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Use the Buttons on each sheet!"
End Sub

Then provide the user with macros that print what you want.

Kind of...

Option explicit
sub testme()
'set up whatever you want
'turn off event handling, so your code can print
application.enableevents = false
'do your specific printing
'turn on events
application.enableevents = true
end sub


But all this fails if events are disabled--or macros are disabled.


Peter-d wrote:

Thanks Dave.

The ability to select different sheet in each window won't bother me. If you
look at my own reply, you will see that I just need to know before I print,
and since each window will handle their print selection individually, it
wan't matter.

But thatnks for the activewindow.selectedsheets tip.

"Dave Peterson" wrote:

msgbox activewindow.selectedsheets.count

You can select different sheets in each window, though.

Peter-d wrote:

Is there a way out there to figure out from VB code if more than one
worksheet is selected?

Thanks

--

Dave Peterson


--

Dave Peterson

Well, yeha, I'm essentially doing something like this...e xcept I took it one
step further where I hooked my own code instead of the standard print
function. The only thing, and thanks for pointing it out, is that in using
this line:
Application.Dialogs(xlDialogPrint).Show
in my code to actually invoke the menu, I didn't look at all options
available to the user and didn't think og the "entire workbook" option.

I could just use the PrintOut method, but I liked giving the user the dialog
in case they wanted to change printer and other properties.

I'll have to give this some more thought.

Thanks

"Dave Peterson" wrote:

I don't think you can ever know.

Inside the file|Print dialog, there's an option for "entire workbook".

I think the best you could do would be to disable the built in printing.

In the ThisWorkbook module.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Use the Buttons on each sheet!"
End Sub

Then provide the user with macros that print what you want.

Kind of...

Option explicit
sub testme()
'set up whatever you want
'turn off event handling, so your code can print
application.enableevents = false
'do your specific printing
'turn on events
application.enableevents = true
end sub


But all this fails if events are disabled--or macros are disabled.


Peter-d wrote:

Thanks Dave.

The ability to select different sheet in each window won't bother me. If you
look at my own reply, you will see that I just need to know before I print,
and since each window will handle their print selection individually, it
wan't matter.

But thatnks for the activewindow.selectedsheets tip.

"Dave Peterson" wrote:

msgbox activewindow.selectedsheets.count

You can select different sheets in each window, though.

Peter-d wrote:

Is there a way out there to figure out from VB code if more than one
worksheet is selected?

Thanks

--

Dave Peterson


--

Dave Peterson

In case anybody else is reading this and happens to care...

I ended up simply hiding all unactive sheets in my print function and
unhiding them when the print is complete. This way, I can still use the
dialog box and I don't care if they try to change the "Print What" to entire
workbook.


"Peter-d" wrote:

Well, yeha, I'm essentially doing something like this...e xcept I took it one
step further where I hooked my own code instead of the standard print
function. The only thing, and thanks for pointing it out, is that in using
this line:
Application.Dialogs(xlDialogPrint).Show
in my code to actually invoke the menu, I didn't look at all options
available to the user and didn't think og the "entire workbook" option.

I could just use the PrintOut method, but I liked giving the user the dialog
in case they wanted to change printer and other properties.

I'll have to give this some more thought.

Thanks

"Dave Peterson" wrote:

I don't think you can ever know.

Inside the file|Print dialog, there's an option for "entire workbook".

I think the best you could do would be to disable the built in printing.

In the ThisWorkbook module.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Use the Buttons on each sheet!"
End Sub

Then provide the user with macros that print what you want.

Kind of...

Option explicit
sub testme()
'set up whatever you want
'turn off event handling, so your code can print
application.enableevents = false
'do your specific printing
'turn on events
application.enableevents = true
end sub


But all this fails if events are disabled--or macros are disabled.


Peter-d wrote:

Thanks Dave.

The ability to select different sheet in each window won't bother me. If you
look at my own reply, you will see that I just need to know before I print,
and since each window will handle their print selection individually, it
wan't matter.

But thatnks for the activewindow.selectedsheets tip.

"Dave Peterson" wrote:

msgbox activewindow.selectedsheets.count

You can select different sheets in each window, though.

Peter-d wrote:

Is there a way out there to figure out from VB code if more than one
worksheet is selected?

Thanks

--

Dave Peterson


--

Dave Peterson

Maybe you could give them an abbreviated dialog:

Application.Dialogs(xlDialogPrinterSetup).Show



Peter-d wrote:

Well, yeha, I'm essentially doing something like this...e xcept I took it one
step further where I hooked my own code instead of the standard print
function. The only thing, and thanks for pointing it out, is that in using
this line:
Application.Dialogs(xlDialogPrint).Show
in my code to actually invoke the menu, I didn't look at all options
available to the user and didn't think og the "entire workbook" option.

I could just use the PrintOut method, but I liked giving the user the dialog
in case they wanted to change printer and other properties.

I'll have to give this some more thought.

Thanks

"Dave Peterson" wrote:

I don't think you can ever know.

Inside the file|Print dialog, there's an option for "entire workbook".

I think the best you could do would be to disable the built in printing.

In the ThisWorkbook module.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Use the Buttons on each sheet!"
End Sub

Then provide the user with macros that print what you want.

Kind of...

Option explicit
sub testme()
'set up whatever you want
'turn off event handling, so your code can print
application.enableevents = false
'do your specific printing
'turn on events
application.enableevents = true
end sub


But all this fails if events are disabled--or macros are disabled.


Peter-d wrote:

Thanks Dave.

The ability to select different sheet in each window won't bother me. If you
look at my own reply, you will see that I just need to know before I print,
and since each window will handle their print selection individually, it
wan't matter.

But thatnks for the activewindow.selectedsheets tip.

"Dave Peterson" wrote:

msgbox activewindow.selectedsheets.count

You can select different sheets in each window, though.

Peter-d wrote:

Is there a way out there to figure out from VB code if more than one
worksheet is selected?

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson