0

I have a worksheet with Combobox Form Controls (dropdowns) embedded for which the Input Range is a dynamic named range (not validation dropdown).

When the range change, the values listed in the drop down is modified accordingly, but the linked cell value is not updated even if the displayed value in the drop down is modified.

The workbook should be macro free, so I cannot use VBA to force a refresh of the cell value.

My internet search seems to indicate that it is impossible, but I might have missed something.


Edit:

There is no external content in the workbook and no links, so refreshing the external link does not help.

The named dynamic ranges are all internal, for example one named range (Choice_List) can refers to =OFFSET(Test!$A$2:$A$11;0;0;Test!$B$2).

When the cell Test!$B$2 change the list displayed change accordingly, but if the new list contains fewer elements than the currently selected item index, the drop down will display the last item of the list, and the value of the linked cell won't change.

Settings:

Initial choice:

After changing the max number of items:

  • Save your Workbook as Macro enabled & use VBA code to Refresh links, simple !! – Rajesh S Jun 27 '18 at 10:11
  • 2
    Well, as I said, my workbook should be macro free. – Vincent G Jun 27 '18 at 10:18
  • Does the linked cell need to move as the Input Range changes? – Mark Fitzgerald Jun 27 '18 at 10:35
  • @VincentG, yes I know that you are trying to avoid Macro, but in your case use a Macro should be a wise move. This simple VBA code will solve your problem ActiveWorkbook.RefreshAll !! – Rajesh S Jun 27 '18 at 11:00
  • @MarkFitzgerald Nope, the linked cell does not need to move (well, having it moving would cause other problems) – Vincent G Jun 27 '18 at 11:07
1

I did some investigation and this looks like another Micro$oft bug.

The internal value stored in the DropDown object corresponding to the ComboBox correctly changes to 2, but the linked cell is not updated with the new value.

Apparently this is because neither the user nor some VBA code changed the value, but it happened as a result of a "correction" (by Excel itself!) required to bring the value back into a valid range after the list itself has shrunk.

Nothing I tried could force an update, not even manually (other that selecting a different value from the ComboBox, of course).

The only solution is to use VBA. The best option might be to create an Add-in containing the code. That way the workbook itself doesn't contain the macro.

  • Yes, that what I feared... – Vincent G Jun 27 '18 at 13:23
  • @VincentG Just clarified the possible solution. Is installing an Add-in an acceptable option? – robinCTS Jun 27 '18 at 13:27
  • Not really, the workbook is expected to be distributed in companies where code executions are very restricted. – Vincent G Jun 27 '18 at 13:37
  • @VincentG The only other option I can think of is to have a formula check for the validity of the value in the linked cell. How are you using the value? – robinCTS Jun 27 '18 at 13:42
  • Yes, I will be at least able to display a warning for the user, but sometimes the list of choice is reduced to only 1 choice, and then the user cannot even select the currently displayed choice :( – Vincent G Jun 27 '18 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.