In Excel 2016, how can I force Worksheet scope when creating names using "Create from Selection"?

In Excel 2016 (Windows), when using "Create from Selection" to create names constructed to be Worksheet-scoped, the Worksheet-scoping character ! gets converted to an underscore, undermining the attempt to create Worksheet-scoped names. How can this be avoided?

I want to attach, to a set of cells in Sheet1, the following names: the_name_A, the_name_B, the_name_C, and so on. And I want the same for a set of cells in Sheet2. And I want to do it by using Worksheet-scoped names. That is, the names to be attached to the Sheet1 cells will be: Sheet1!the_name_A, Sheet1!the_name_B, etc; and the corresponding Sheet2 cells will be named Sheet2!the_name_A, Sheet2!the_name_B, etc.

All of that works fine manually, by selecting each cell in tu and typing its sheet-name-qualified name into the name box. But I'm trying to do it using "Create from Selection", typically where the names are placed in a column, and the cells-to-be-named in the column to the right.

However, that "Create from Selection" process (which I'm recording into a macro, and that shows it's simply a call to Selection.CreateNames), is refusing to treat the ! character as indicating that the name is Sheetname-scope and instead translates it to an underscore. So instead of getting a set of Worksheet-scoped names such, as Sheet1!the_name_A, Sheet2!the_name_B, I get a set of Workbook-scoped names, Sheet1_the_name_A, Sheet2_the_name_B

How can I avoid this behavior?

I can do this in VBA, but I prefer not to if possible. I already have two non-VBA workarounds, so I'm looking for a response to the specific question I asked (and if it can't be done, fair enough). FWIW, those two workarounds are:

1. Pseudo-Worksheet-scoping via a naming convention. This involves accepting the fact that all my names will be Workbook-scope, but still have them prefixed with their sheet name, albeit followed not by the ! character but by something designated for use only as a scope-indicator (e.g. two underscores). So when referring to the_name_A in Sheet1, I would use name (something like) Sheet1__the_name_A, and the same name in Sheet2 could be Sheet2__the_name_A.

2. Pre-create Workbook-scoped versions to force subsequent creation of Worksheet-scoped versions. The other workaround is to first ensure that every single name that I want to be Worksheet-scoped already pre-exists (on some sheet reserved for that purpose). "Create from Selection" automatically adds the correct sheet prefix (this time without converting the ! to underscore) when it is asked to create a name that already exists elsewhere.

Recent Questions...