Named Ranges: Disable or alert/auto-display

[MrSamExcel]

Is there a way I can disable creation of Named Ranges in a specific workbook for all users? I didn’t see anything in the Protect/Lock menus.
If not, is there a way to auto-alert if Named Ranges exist when the file is opened? Similar idea as the “Ask to update automatic links” prompt.
If not, is there a non-VBA formula I can leave in a worksheet that will populate all/any Named Ranges that exist in the workbook? Similar to using Formulas menu > Use in Formula > Paste Names, but an approach that populates automatically without user clicks required.
Thanks.

 

[6StringJazzer]

In my version, if a protected sheet is active, creation of new named ranges is disabled. Even if all cells are unlocked, this is true when the sheet is protected. So one solution is to protect all the worksheets in the file, leaving all cells unlocked that you want to allow the user to change.

There is no non-VBA way to list defined names automatically.

 

[MrSamExcel]

In my version, if a protected sheet is active, creation of new named ranges is disabled. Even if all cells are unlocked, this is true when the sheet is protected. So one solution is to protect all the worksheets in the file, leaving all cells unlocked that you want to allow the user to change.

There is no non-VBA way to list defined names automatically.

I can make that work in this case, thanks for your suggestion!
So the Protect Workbook function does not disable Named Ranges but the Protect Sheet function does. The Protect Sheet function cannot be applied to more than one sheet at a time (though could be achieved with a macro). I have 60 sheets so it will be a bit tedious, but it's only a one-time setup so this is still a reasonable solution. If I had to repeat the process frequently, or if the workbook had several more tabs, I'd have to rely on VBA.

 

[6StringJazzer]

Protecting all the sheets with a macro is trivial and the code could be in a different file so your file would not have to be macro-enabled.

Dim WS As Worksheet

For Each WS In ActiveWorkbook
   WS.Protect
Next WS

You could do the same kind of thing to get a list of named ranges.