How can I disable multiselection of tabs in excel

[srinu1264]

Hi I want to disable multi-selection of tabs in a particular worksheet in a workbook. Is there any way to accomplish this??

[Chip Pearson]

There is no way to disable selecting multiple worksheet tabs.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"srinu1264"
<[email protected]> wrote in
message
news:[email protected]...
>
> Hi I want to disable multi-selection of tabs in a particular
> worksheet
> in a workbook. Is there any way to accomplish this??
>
>
> --
> srinu1264
> ------------------------------------------------------------------------
> srinu1264's Profile:
> http://www.excelforum.com/member.php...o&userid=34155
> View this thread:
> http://www.excelforum.com/showthread...hreadid=539634
>

[Tom Ogilvy]

I may be missing something obvious, but I think the best you could do is to
make the tabs hidden.


ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayWorkbookTabs = True



--
Regards,
Tom Ogilvy



"srinu1264" wrote:

>
> Hi I want to disable multi-selection of tabs in a particular worksheet
> in a workbook. Is there any way to accomplish this??
>
>
> --
> srinu1264
> ------------------------------------------------------------------------
> srinu1264's Profile: http://www.excelforum.com/member.php...o&userid=34155
> View this thread: http://www.excelforum.com/showthread...hreadid=539634
>
>

[Doug Glancy]

srinu1264,

I don't know if you are, but I'm assuming that you don't want users to be
able to make changes across multiple worksheets. This would show a message,
undo the change, and deselect the other sheets. Even if I'm right about
what you are trying to do, this is not a great solution.

Paste it into the worksheet's code module:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo err_handler
If ActiveWindow.SelectedSheets.Count > 1 Then
If ActiveSheet Is Target.Parent Then
Application.EnableEvents = False
MsgBox "Please don't make changes with multiple sheets selected."
Application.Undo
ActiveSheet.Select
End If
End If

err_handler:
Application.EnableEvents = True
End Sub


hth,

Doug


"srinu1264" <[email protected]> wrote
in message news:[email protected]...
>
> Hi I want to disable multi-selection of tabs in a particular worksheet
> in a workbook. Is there any way to accomplish this??
>
>
> --
> srinu1264
> ------------------------------------------------------------------------
> srinu1264's Profile:
> http://www.excelforum.com/member.php...o&userid=34155
> View this thread: http://www.excelforum.com/showthread...hreadid=539634
>

[GS]

Hi Doug,

Your solution works very well, but don't you mean use it in the
Workbook_SheetChange() to monitor the entire workbook?

Regards,
Garry

[Doug Glancy]

Garry,

I started with that assumption, but rereading the OP (which I've learned is
a good idea for me) they mentioned a "particular sheet":

"Hi I want to disable multi-selection of tabs in a particular worksheet in a
workbook."

Otherwise yes, they could delete the "Target.Parent" line and put in the
workbook-level event and I think it would work for all sheets.

Thanks,

Doug


"GS" <[email protected]> wrote in message
news:[email protected]...
> Hi Doug,
>
> Your solution works very well, but don't you mean use it in the
> Workbook_SheetChange() to monitor the entire workbook?
>
> Regards,
> Garry

[GS]

Doug,

Thanks for the input. I agree with your assessment of the OP, and the
rereading advice. It truly makes a lot of difference. I guess I was looking
to cover the aspect of what the OP might have meant as it's not always put
how we would put it. That said, I was thinking ..if he might have wanted to
monitor the entire workbook to see if the subject wks was included in grouped
sheets.

Your solution will follow the sheet if it's copied, so it covers the sheet
itself wherever it is AS LONG AS IT'S THE ACTIVE SHEET. My take on the OP's
post was ..not allow it to be included in ANY multi-sheet selection changes,
-activesheet or not. If so, he'll need to add more code to see if it's in the
group.

Regards,
Garry

[srinu1264]

I should have been more clear...

instead of disabling multiple tabs... I should have said I want to disable multi selection of cells in a same worksheet.

Is there any way to programattically control it??

[NickHK]

srinu1264,
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static FirstRange As Range

If Target.Areas.Count > 1 Then
MsgBox "Only single area selection allowed"
FirstRange.Select
Else
Set FirstRange = Target
End If

End Sub

NickHK

"srinu1264" <[email protected]> wrote
in message news:[email protected]...
>
> I should have been more clear...
>
> instead of disabling multiple tabs... I should have said I want to
> disable multi selection of cells in a same worksheet.
>
> Is there any way to programattically control it??
>
>
> --
> srinu1264
> ------------------------------------------------------------------------
> srinu1264's Profile:
http://www.excelforum.com/member.php...o&userid=34155
> View this thread: http://www.excelforum.com/showthread...hreadid=539634
>

[srinu1264]

thanks it works....

[P.Bedo]

If you are just trying to prevent someone from accidently affecting all of the sheets in a workbook here is a simple solution. Create a new tab and protect that entire sheet. Now if the user tries to alter a cell with all of the sheets selected they will get the "protected and therefore read-only" message.