+ Reply to Thread
Results 1 to 11 of 11

How can I disable multiselection of tabs in excel

  1. #1
    Registered User
    Join Date
    05-05-2006
    Posts
    11

    How can I disable multiselection of tabs in excel

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

  2. #2
    Chip Pearson
    Guest

    Re: How can I disable multiselection of tabs in excel

    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
    >




  3. #3
    Tom Ogilvy
    Guest

    RE: How can I disable multiselection of tabs in excel

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


  4. #4
    Doug Glancy
    Guest

    Re: How can I disable multiselection of tabs in excel

    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
    >




  5. #5
    GS
    Guest

    Re: How can I disable multiselection of tabs in excel

    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

  6. #6
    Doug Glancy
    Guest

    Re: How can I disable multiselection of tabs in excel

    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




  7. #7
    GS
    Guest

    Re: How can I disable multiselection of tabs in excel

    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

  8. #8
    Registered User
    Join Date
    05-05-2006
    Posts
    11

    thanks for ur replies , but I am looking for something different

    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??

  9. #9
    NickHK
    Guest

    Re: How can I disable multiselection of tabs in excel

    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
    >




  10. #10
    Registered User
    Join Date
    05-05-2006
    Posts
    11

    thanks it works....

    thanks it works....

  11. #11
    Registered User
    Join Date
    05-07-2015
    Location
    Durham, NC
    MS-Off Ver
    2010
    Posts
    1

    Re: thanks it works....

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1