Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    78

    Unanswered: Disable right click on Sheet tabs

    Is there a way to disable right click on Sheet tabs (Sheet1, Sheet2, Sheet3...) so that user cannot create/insert a new worksheet.

    Thanks.

    ~BS

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Protect the workbook structure this will prevent insertion deletion or moving of worksheets

    you can turn it on and off in code like this

    Code:
    Sub test()
        ThisWorkbook.Protect "abc", True
    End Sub
    
    Sub testoff()
        ThisWorkbook.Unprotect "abc"
    End Sub

  3. #3
    Join Date
    Dec 2004
    Posts
    78
    I am using the following code to lock my worksheets (though I am allowing users to format rows and columns). Is there a way to disable right click on sheet tabs using this?


    Code:
    ActiveSheet.Protect Password:=12345
    ActiveSheet.EnableOutlining = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=PWord, UserInterfaceOnly:=True, AllowSorting:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFormattingCells:=True
    Thanks.

    ~BS.

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    nope all of the sheet tabs events are associated with the workbook,
    i think its the contents but id have to look it up to be sure

    if you proyect the workbook as above the delet and move and insertion of worksheets should be disabled

  5. #5
    Join Date
    Dec 2004
    Posts
    78

    Thumbs up

    Thanks David. I am using the following code. This locks the worksheet, but at the same time allows me to change column/row width and allows to expand/contract grouped rows and columns.

    Code:
    ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=12345
    ActiveSheet.EnableOutlining = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=PWord, UserInterfaceOnly:=True, AllowSorting:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFormattingCells:=True

Posting Permissions

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