0

I have a ComboBox whose list refers to a named range that creates a dynamic list based on the matches with the linked text to the ComboBox (linked with a cell). The list has the length of the amount of matches, The ComboBox has a ComboBox_Change to refresh it.

Now my issue is, I can't Ctrl+Z anything in the document and it makes it pretty hard to work with. I used to also have an issue of the list trigerrin with any change in the worksheet, but that seems to have stopped since I changed the named range "zone" to it's sheet instead of the whole Excel (even in it's own sheet strangely). The cells used in the named range do refer to another sheet.

I had seen a link yesterday where someone mentionned it happens because every change refreshes the box, and the list should be "cached" as an array (Why is a (Excel VBA) combobox change event triggering every time one of its properties is referenced?). How would I go about doing this given that my list length keeps changing, and I can't ctrl+shift+enter 500 cells at once (would that even work?)?

Here are some codes and formulas. I freelance translated from French so there might be formula typos. Say Sheet1 is where my ComboBox is.

Cell formula used to populate named list (not really relevant I believe)

=IFERROR(INDEX(Sheet2!$H$2:$H$501;MATCH(ROWS(Sheet1!$B$1:B1);Sheet1!$B$1:$B$503;0));"")

My ComboBox VBA is as follow :

Private Sub ComboBox1_Change()
    ComboBox1.ListFillRange = "DropDownList"
    Me.ComboBox1.DropDown
    Range("H1").Value = 1
End Sub

The Range("H1") is irrelevant for this, it is to reset a count so that my "Next" and "Previous" picture looking buttons resets.

Named range (dynamic length), where A gives me 1/0 partial match with my "search" in the ComboBox, B gives me the count of how many partial matches I get and C lists the values filling the dropdown list from the other tab.

Don't think it's helpful but here are those forumlas. No determined sheet means Sheet1.

A : 

=--ISNUMBER(IFERROR(SEARCH(Sheet1!$D$1;Sheet2!H2;1);""))

B : 

=IF(A1=1;SUMIF($A$1:A1;1);"")

C : 

=Sheet1!$C$1:INDEX(Sheet1!$C$1:$C$503;MAX(Sheet1!$B$1:$B$503);1)

EDIT : In response to Nick, further clarification. I'm basically looking for a different way to write the macro so that it doesn't execute after every worksheet change, even those not affecting it's dynamic list. I forgot to mention in the main thread, but if my ComboBox is empty (so nothing is filtered out of columns A to C and the list is at "max capacity"), than my Undo works just fine, but as soon as there is something in the ComboBox (or the matching cell) from there on I can't use undo.

Bulbuzor
  • 39
  • 1
  • 3
  • So firstly you cannot undo anything after executing a macro. There are ways but they're very complicated IMO. See [This](https://social.msdn.microsoft.com/Forums/office/en-US/ffbb5456-bc80-4c05-80f5-fb07245541aa/why-does-excel-not-allow-quotundoquot-after-a-vba-execution?forum=exceldev). Regarding your 2nd question, you can read a named range into an array in VBA and then set the `listfillrange` to said array. Many posts on SO on how to create an array in VBA. You will have to use a `for loop` and `ReDim Preserve` – Nick May 08 '19 at 14:45
  • Isn't there a different way to write the macro so that it doesn't execute after every worksheet change, even those not affecting it's dynamic list? I forgot to mention in the main thread, but if my ComboBox is empty (so nothing is filtered out of columns A to C and the list is at "max capacity"), than my Undo works just fine, but as soon as there is something in the ComboBox (or the matching cell) from there on I can't use undo. – Bulbuzor May 08 '19 at 15:05

1 Answers1

0

To avoid your sheet updating whenever a list property is referenced you can replace the combobox_change event with combobox_click or alternatively combobox_dropdownclick which updates the list in the box only when the box itself is clicked or F4 is pressed.

If you want to use the worksheet_change event you could do something like this

Worksheet_change (ByVal Target as Range)
 If Not Intersect (Target, Range("your named range"))  Is Nothing Then
   Combobox1.ListfillRange = range("Your named range")

This would only update the combobox if there's a change in the target range.

In general you can't undo after executing VBA code and I wouldn't recommend trying to create a custom undo method. I'd just copy the sheet and use that copy to tinker with.

Nick
  • 667
  • 7
  • 19
  • Unfortunately this won't refresh the dynamic list as it is being typed, strangely it won't even show the full list (only the 1st value) when clicked. In the meanwhile I've also tried to link the dropdown appearance and update to a specific cell change (Worksheet_Change(ByVal Target As Range) / Dim KeyCells As Range / Set KeyCells = Range("D1"), but that didn't work either. I'll probably just end tell the people who will be using the database to leave the combobox empty while changes are being made to the sheets. – Bulbuzor May 08 '19 at 15:49
  • You can use `intersect(target)` which should work fine – Nick May 09 '19 at 00:17