Disabling Selection of multiple worksheets

[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

 

[Ron de Bruin]

You can disable the Sheets Tabs

ActiveWindow.DisplayWorkbookTabs = False

But the user can use Tools>Options to set it to true so
to be save you must disable the options menu

 

[Guest]

An interesting idea, though it would then require more extensive programming to make each desired tab available/visible for editing.

Certainly a fix, but not a good one in this scenario. Thank you though.

Still looking to disable the selection of multiple worksheets within a workbook. Want to have all sheets available to be selected, but only allowing selection of a single worksheet at any/all times.

 

[Tom Ogilvy]

I don't think you can prevent it, but you can force the sheets to be
ungrouped by checking on every selection change

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, ByVal Target As Range)
If ActiveWindow.SelectedSheets.Count > 1 Then
ActiveSheet.Select
End If
End Sub


As with any macro solution, disabling macros disables the protection.

--
Regards,
Tom Ogilvy

An interesting idea, though it would then require more extensive

programming to make each desired tab available/visible for editing.

Certainly a fix, but not a good one in this scenario. Thank you though.

Still looking to disable the selection of multiple worksheets within a

workbook. Want to have all sheets available to be selected, but only
allowing selection of a single worksheet at any/all times.

 

[Guest]

Ok, this partially fixes the problem, though there is a single case that could escape this "protection" if the cell that will be changed, is already selected, then multiple tabs are selected, and then the cell is changed, all cells at that location will also be changed to the resulting data.

I tried to put the same code in the SheetChange event, as I've picked up that when multiple sheets are selected, and data is entered into one of the selected sheets, the other sheets go through the sheetchange event as well, so I thought if I applied the fix you suggested, (Which I was doing something similar in a different event), that the other pages would be deselected and not get updated, however Excel is smarter than that, and still updates the other selected pages, even though they are not selected when it gets to them. :\

 

[Ron de Bruin]

Hi Tom

I was thinking the same as you but this will not work correct.
If you select for example two sheets and type something in the Activecell
the cell in the two sheets will be populate with this value before the activesheet will be select

 

[Tom Ogilvy]

So use the SheetsChange Event to handle that or do it all in the
SheetsChange event.

Private Sub Workbook_SheetChange( _
ByVal Sh As Object, ByVal Target As Range)
If ActiveWindow.SelectedSheets.Count > 1 Then
Application.EnableEvents = False
vVal = Target.Value
Application.Undo
ActiveSheet.Select
Target.Value = vVal
Application.EnableEvents = True

End If

End Sub

 

[Ron de Bruin]

Hi Tom

Yes this way COM have a good solution

 

[Guest]

That solution is a little sleaker than what I did... Disabling events, never have used it, though I can really see a benefit now that I see the code below. thanks again all. I posted my version of the code on my 12/23/03 thread. Thank you, thank you, thank you......

 

[Guest]

I modified my version to be more compact, disabling events rather than dealing with the repetative iterations, however I would make one suggestion change... Instead of using Target.Value, use target.formula. This way when multiple worksheets are selected and if a formula is entered into the cell, (hopefully correctly so), the formula will remain and not simply the result of the formula.

So to do everything I wanted, and to prevent each of the possible "means" of resolution the code would look like this:

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveWindow.SelectedSheets.Count > 1 Then
ActiveSheet.Select
End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim vVal As Variant

If ActiveWindow.SelectedSheets.Count > 1 Then
Application.EnableEvents = False
vVal = Target.Formula
Application.Undo
ActiveSheet.Select
Target.Formula = vVal
Application.EnableEvents = True
End If

End Sub

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, ByVal Target As Range)
If ActiveWindow.SelectedSheets.Count > 1 Then
ActiveSheet.Select
End If
End Sub

 

[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

 

[Guest]

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.