Disabling Selection of multiple worksheets

C

COM

I have not been able to find anything that would allow me
to prevent Excel spreadsheet users from selecting multiple
worksheets in a given workbook.

Problem I'm trying to solve is this, though it can be
summed up differently, Users use a workbook that has
multiple worksheets in it. As is commonly known to Excel
users, if multiple worksheets are selected, then data is
changed on any of those multiple sheets the same data is
applied to all selected worksheets. I want to be able to
prevent the selection of multiple worksheets in the first
place. I have figured out that if multiple sheets are
already highlighted/selected, then any other non-active
worksheet is selected, to make that new active worksheet
the only selected sheet. However, I have not figured out
how to prevent a user from first selecting a worksheet
then say holding the shift key selecting a worksheet
several tabs over, and then editing data on the active
sheet which would then change the data of all the selected
sheets.

If anyone knows how to prevent the selection of multiple
worksheets from the get go, I wouldn't need so much user
error correction/prevention.

Thanks
 
S

Steve Hieb

I can't find an easy way, but one workaround might be to put the
following code in EVERY worksheet object.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Wks As Variant
For Each Wks In Selection
Worksheets(Wks.Worksheet.Name).Select
Exit For
Next
End Sub

Select a sheet, use CTRL or SHIFT to select other sheets, then select
a different cell and it will un-select the additional sheets. One
problem though. Its obviously triggered by changing the range
selection, so after selecting the other sheets if you simply enter a
value without selecting a different cell then all the code is
bypassed. Not sure how often this would happen though. Didn't take
the time to close that door, but I'm sure you or others can think of
something if you could live w/ this solution.

Would be nice to have an easier way like a Thisworkbook-level event,
but I didn't see any. I'll be curious to see if someone else found a
better solution.

Happy Holidays,
Steve Hieb
 
C

COM

If you look at my final post on the thread response starting with Ron de Bruin.. The method you suggested might work, but would be cumbersome as in my case I would have to put that code in something like 90 worksheets.

I did fail to mention that all the code I/we wrote went into the ThisWorkbook section.
 
Top