Disable right click on Sheet tabs

bhavesh78 · #1 (**permalink**) 02-07-05, 16:44

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

DavidCoutts · #2 (**permalink**) 02-08-05, 05:40

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

bhavesh78 · #3 (**permalink**) 02-09-05, 20:09

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.

DavidCoutts · #4 (**permalink**) 02-10-05, 02:50

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

bhavesh78 · #5 (**permalink**) 02-10-05, 16:31

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

Internet Services

Web Development

Digital Marketing

Consumer Tech