Selecting all sheets and sheet methods

I've got some code in a spreadsheet that will be called when the user tries
to insert a sheet, so that any sheet inserted already has the formatting and
other hidden ranges needed inside it. Part of this process involves
unprotecting the current activesheet, making some changes, then reprotecting
it.

The activesheet object seems to be accessible programitcally when all sheets
have been selected - at least in regards to retreiving property values - but
falls over when the unprotect and protect methods are called.

The user shouldn't really be selecting multiple sheets but they shouldn't be
seeing such errors either. Is there any way of checking to see if multiple
sheets have been selected from within VBA code within the spreadsheet?
Activesheet doean't seem to provide me with any useful information and I
would prefer to avoid calling any methods to check to see if any errors are
returned.

I'm currently using Excel 2003, but the solution would have to work in Excel
97 too.

---
Patrick Seurre

I don;t know of a way to figure out how many sheets are selected at any 1 time.

I would advise this though.

Find out the error number that Excel is throwing at you and then at the
beginning of your code do this....

On error Goto ErrorHandler

ErrorHandler:

If Err.Num = [Error Number} then

MsgBox "More than 1 sheet is selected. Please try again!"
End Sub

End If

A quick and easy answer to get around the problem by making the user aware.

HTH

"Patrick Seurre" wrote:

> I've got some code in a spreadsheet that will be called when the user tries
> to insert a sheet, so that any sheet inserted already has the formatting and
> other hidden ranges needed inside it. Part of this process involves
> unprotecting the current activesheet, making some changes, then reprotecting
> it.
>
> The activesheet object seems to be accessible programitcally when all sheets
> have been selected - at least in regards to retreiving property values - but
> falls over when the unprotect and protect methods are called.
>
> The user shouldn't really be selecting multiple sheets but they shouldn't be
> seeing such errors either. Is there any way of checking to see if multiple
> sheets have been selected from within VBA code within the spreadsheet?
> Activesheet doean't seem to provide me with any useful information and I
> would prefer to avoid calling any methods to check to see if any errors are
> returned.
>
> I'm currently using Excel 2003, but the solution would have to work in Excel
> 97 too.
>
> ---
> Patrick Seurre

Hi Patrick,

Maybe

If Activeworkbook.windows(1).SelectedSheets.count > 1 Then
MsgBox "oops!"
End If

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Patrick Seurre" <msdn_newsgroups001@_removetextplusunderscores_seurre.com.>
wrote in message news:62EBA31F-827B-4F44-BFAA-2A32FDCC1C6E@microsoft.com...
> I've got some code in a spreadsheet that will be called when the user
tries
> to insert a sheet, so that any sheet inserted already has the formatting
and
> other hidden ranges needed inside it. Part of this process involves
> unprotecting the current activesheet, making some changes, then
reprotecting
> it.
>
> The activesheet object seems to be accessible programitcally when all
sheets
> have been selected - at least in regards to retreiving property values -
but
> falls over when the unprotect and protect methods are called.
>
> The user shouldn't really be selecting multiple sheets but they shouldn't
be
> seeing such errors either. Is there any way of checking to see if multiple
> sheets have been selected from within VBA code within the spreadsheet?
> Activesheet doean't seem to provide me with any useful information and I
> would prefer to avoid calling any methods to check to see if any errors
are
> returned.
>
> I'm currently using Excel 2003, but the solution would have to work in
Excel
> 97 too.
>
> ---
> Patrick Seurre

DaveO plan seems good

Else create a hidden sheet

on Insert sheet event first activate hidden sheet, ensuring only one
sheet is activated