Issue With ControlFormat.List When Changing the Input Range In a Form ComboBox

[eblues]

The attached workbook contains 2 macros, both of which do the same thing... toggle the Input Range of a Forms Control ComboBox between two named ranges.

Macro_1 declares the variables Rng1 and Rng2 as Ranges, and Sets them to = range("Name"). Then it alternately toggles which range is set as the Combobox Input Range using ControlFormat.List:

With ActiveSheet.Shapes("RngSelection").ControlFormat
.List = Rng1
.DropDownLines = Rng1RowCount
End With

Macro_2 performs the same task in a slightly different way. Rng1 and Rng2 are declared as strings, and only the range name strings are assigned to them. In this example, the Input Range toggle is accomplished with ControlFormat.ListFillRange:

With ActiveSheet.Shapes("RngSelection").ControlFormat
.ListFillRange = Rng1
.DropDownLines = Rng1RowCount
End With



The issue with Macro_1 is that this method leaves the Input Range blank under the Control tab in the ComboBox Format Control dialog box. I don't know if this is the case with all earlier versions of Excel, but I do know it is the case with Excel365 and Excel 2013.

Despite this glitch, the approach of Macro_1 functions as expected and has for years through several version iterations of Excel. However, in Excel365 there is an added twist. In the 365 version, when the workbook is saved and later reloaded, the ComboBox initially displays a blank (presumably because the Input Range is blank?). But as soon as you click on the Down Arrow, the selected item appears and the Combobox then functions as expected.

In all previous versions of Excel we have used, the selected item appeared in the Combobox as expected upon initial loading of the workbook, even though the Input Range in the Format Control dialog box is left blank by Macro_1. This is why this glitch wasn't previously apparent.

I know... very minor. But it annoyed me, so I've spent the better part of two work shifts trying to figure out what is going on.

I came up with the solution used in Macro_2 through much Googling and experimentation. This routine properly sets the Input Range for the Combobox each time it's toggled. So, I don't need help with a solution. But I could use some help understanding why Macro_1 leaves the Input Range blank.

Is ControlFormat.List used improperly in Macro_1?
Or is it just an unsupported use?
Or is it just an Excel bug?

I have read some suggestions that ControlFormat.ListFillRange sometimes behaves unpredictably, and advice to avoid using it if possible. Does anyone see a potential issue with it's use in Macro_2?

As a final point of curiosity, you can click on the Switch List Macro1 button repeatedly, and you will see the switch is being performed. Then you can move on to clicking the Switch List Macro2 button repeatedly, and it will continue switching as expected. However, after clicking the Macro2 button, if you then go back and click the Macro1 button, the CellLink Value (at C5) goes to zero, and the ComboBox displays a blank. The ComboBox is still functional however, and you can click the down arrow to make a new selection.

I just wonder why I can go from Macro1 to Macro2 with no issues, but not the other way around?

Thanks in advance for any input give to this topic.

Herman