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

 

[SteveO59L]

in excel 2010+ there is an option under the file menu to disable changes to wb structure

 

[F0RE5T]

in excel 2010+ there is an option under the file menu to disable changes to wb structure

thanks and sorry I should of said I am using 2007.
I assume the answer is the same then.

Cheers
F0re5t

 

[SteveO59L]

pre 2010, I think the equivalent functionality was to protect the workbook

 

[Jonmo1]

I think it's the same in 2007 as it is in 2010

The option is on the Review Tab - Protect Workbook

 

[F0RE5T]

I think it's the same in 2007 as it is in 2010

The option is on the Review Tab - Protect Workbook

hi

I was hoping to use vba, so not just protect the whole workbook. I suppose I have gotten so far and not wanting to redo everything again with simply using protect workbook combinations.... am I lazy >>>

many thanks

f0re5t

 

[Jonmo1]

am I lazy >>>

I'd say the opposite.
Going to great lengths to 'avoid' the easy solution.

 

[F0RE5T]

I'd say the opposite.
Going to great lengths to 'avoid' the easy solution.

Hi

Just trying to learn

many thanks and I will keep you posted if I resolve it.

F0re5t

 

[mikerickson]

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?

 

[Jonmo1]

I suppose I have gotten so far and not wanting to redo everything again

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?