disable new sheet insertion

[F0RE5T]

Hi

in the Private sub for when a workbook opens I include the following code:

'developers tab
Application.ShowDevTools = False
'hide ribbon
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
'.................
'Hide formula bar
Application.DisplayFormulaBar = False
'..................
'max screen
Application.WindowState = xlMaximized
'..................
'do not show file history
Application.DisplayRecentFiles = False
'............................

The application I have built is driven from a sheet I use as a menu and the above disables certain user activity / views.

However during testing I have found that a user can right click on the "menu" sheet and still have access to the "insert" option. Though selection of this will not insert a sheet the application hangs and I cannot find a way to disable the "right click", not show "insert" or msgbox a msg to say don't .....

Can anyone help please?

many thanks
F0re5t

 

[Jonmo1]

You can even do it within your code...

ActiveWorkbook.Protect "PasswordGoesHere", True, False

 

[F0RE5T]

If the user is going to go to those lengths (hunting down the key combo that you found) to avoid using a spreadsheet the way its designed, does that say something about its design?

hi

why does it? I am sure we both come up with ideas to do something adhoc. most users know about the key function.
Not being rude as I like replies but your logic fails in the case for passwords doesn't it as most users can search for the hack.

take care
Fore5t

 

[F0RE5T]

Why would you have to 'redo' everything

We're only talking about 1 small part of your overall objective.
The ability to insert sheets is not related to all the other things you've disabled..(formulabar, windowstate, showrecentfiles,etc...)
None of that woulud have to be 'redone'

Sometimes the simple solution really is the best solution.

The functionality to prevent users from inserting sheets already exists within the Excel Program as provided by MS.
Why Re-Invent another method to accomplish the same task?

Hi

I agree with keeping things simple but as I mentioned I like to learn new ways for future concepts as well
I do not like hard coding passwords which may have been the case because of the desired requirements in other functionality.

Thank you for your advice though.

f0re5t

 

[Jonmo1]

You don't have to 'hard code' the password into the code..
You could do

pswd = InputBox("Enter A Password")
ActiveWorkbook.Protect pswd, True, False

Also, The password is completely optional. You can set it as ""
And Doing the proteciton with code is optional too.
Do it by hand, and save the book.

Most users don't even know about workbook protection, they only know about sheet protection.
And those that do, can probably circumvent passwords (and all the other stuff you're securing) anyway.