Cannot change name of Named Range in VBA if named ranged appears in a cell formula

[mcomp72]

I have code that will change the name of a named range. It works just fine if the named range does not appear in a formula anywhere in the workbook. But if it does appear, even in just one formula (and even in something as simple as something like =MyName), the code doesn't change the name. It doesn't cause an error, it just doesn't do what it's supposed to. It's as if it is simply ignoring the line of code.

The code:

UnprotectEntireWorkbook
UnprotectAllSheets
               
Set nm = ThisWorkbook.Names(oldName)
               
nm.Name = newName
               
ProtectAllSheets
ProtectEntireWorkbook

oldName and newName are string variables, and I have confirmed the values of both are legit names. UnprotectEntireWorkbook, UnprotectAllSheets, ProtectAllSheets, and ProtectEntireWorkbook are all subs that do exactly what their names suggest.

So my workbook is unprotected and all of the sheets within the workbook are unprotected, yet the line nm.Name = newName does not do anything if the named range (oldName) appears in any cells in the workbook.

Does anyone have any idea why this would be happening?

 

[Alex Blakenburg]

You've done well getting a response from Microsoft in the past, let me know how you go. Will also be interested to hear how this method holds up in actual usage.

Not sure what to do with the case where the name is already in use since it means the original formula is referencing a name that doesn't exist yet and will be showing an error.
Handling that scenario would probably mean searching the entire workbook for where it is used.