multi tab selection prohibition

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to prevent users from selecting/highlighting multiple tabs (worksheets) in a workbook, though I want all worksheets to be visible, any ideas?

(This is a repost of yesterday, but didn't get a response that accomplished that desired above)
 
I don't think there is a way to prohibit the user from selecting
multiple sheets.

COM said:
I want to prevent users from selecting/highlighting multiple
Click to expand...
tabs (worksheets) in a workbook, though I want all worksheets to
be visible, any ideas?
(This is a repost of yesterday, but didn't get a response that
Click to expand...
accomplished that desired above)
 
See response to your original post. (for a possible workaround).

--
Regards,
Tom Ogilvy
COM said:
I want to prevent users from selecting/highlighting multiple tabs
Click to expand...
(worksheets) in a workbook, though I want all worksheets to be visible, any
ideas?
(This is a repost of yesterday, but didn't get a response that
Click to expand...
accomplished that desired above)
 
May be the best solution possible, without going through and undoing each change that was then applied to all selected pages.

See my response on yesterday's thread.
 
The only situation where this would fail is if the user selected multiple
sheets and then immediately made a change to the current activecell on the
activesheet - the event would not be triggered. You could couple this with
a check in the SheetChange event to check Activewindow.SelectedSheets.Count
1 then do an immediate
Click to expand...
Application.Undo

Or just use that type of approach in SheetChange.

--
Regards,
Tom Ogilvy

COM said:
May be the best solution possible, without going through and undoing each
Click to expand...
change that was then applied to all selected pages.
 
Came up with a fix... And it deals with all of the possible combinations that we have discussed.

Thank you for the suggestion of Application.Undo. I'm including my resulting code, if anyone would care to accomplish what we've done, and for the length of time that this message will remain on the site.

Of course this "fix" may tick off some users, but let me tell you it is for the better interest of the group than for the one individual that might think to select say all the sheets and potentially overwrite all good data, just to fix one possible mistake on a single sheet. Anyways, the following code works like a charm, thank you each and every one for the assistance. Plagiarize away. :)

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 CellData As Variant
Dim CellLoc As Variant


If ActiveWindow.SelectedSheets.Count > 1 Then
ActiveSheet.Select
CellLoc = Target.Address()
CellData = Target.Formula

Application.Undo
ActiveSheet.Range(CellLoc).Formula = CellData

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
 
Back
Top