1

I have some ComboBoxes in my Excel worksheet. When the user selects choices in these boxes, saves the document and opens it again, the selected items are gone. There are no choices available and nothing is selected.

I populate the boxes' choices via macro at Workbook.Open by reading settings from a hidden worksheet "formdata". I also tried to save the user selected values there on ComboBox.Change and read them back at Workbook.Open. However, as soon as the first item is added to the choices, this already triggers ComboBox.Change, effectively deleting the saved value (setting in to -1).

Private Sub Workbook_Open()
    Dim obj As ComboBox
    Dim s As Variant
    Dim col As Integer, row As Integer

    Application.EnableEvents = False  ' <-- this doesn't help
    
    With ThisWorkbook.Worksheets("formdata")
        col = 1
        For Each s In Array("ComboBox1", "ComboBox2")
            Set obj = CallByName(ThisWorkbook.Worksheets(sht_tool), s, VbGet)
            row = 2
            While Not IsEmpty(.Cells(row, col))
                obj.AddItem .Cells(row, col).Value  ' This already triggers ComboBox.Change and writes -1 to "formdata", overwriting the saved value
                row = row + 1
            Wend
            obj.ListIndex = .Cells(1, col + 1).Value  ' At this point the saved value was already set back to -1
            col = col + 2
        Next s
    End With
    
    Application.EnableEvents = True
End Sub

My ComboBox.Change callbacks look like this:

Private Sub ComboBox1_Change()
    ThisWorkbook.Worksheets("formdata").Cells(1, 2).Value = _
    ThisWorkbook.Worksheets("tool").ComboBox1.ListIndex
End Sub
ascripter
  • 5,665
  • 12
  • 45
  • 68
  • Jus a question, why don't you use the [LinkedCell](https://learn.microsoft.com/fr-fr/office/vba/api/excel.oleobject.linkedcell) and [ListFillRange](https://learn.microsoft.com/fr-fr/office/vba/api/excel.oleobject.listfillrange) properties? – Vincent G Jun 29 '22 at 15:30
  • In `Workbook_Open` set a Global flag variable which you can which you check inside of `ComboBoxX_Change` to exit without taking any action. Unset the flag when you're done filling the comboboxes. – Tim Williams Jun 29 '22 at 15:51
  • @VincentG didn't know about that and just tried it - it works in the way that the options are populated and the hidden worksheet is updated when the entry is changed. But at `Workbook.Open` still nothing happens :-/ – ascripter Jun 29 '22 at 17:05
  • There are some options, depending on the context. If your code (somewhere) save the workbook (to reopen it again), you can use `Workbook_BeforeClose` event and save the combo `listIndex` in Registry, or on a workbook sheet. In the `Workbook_Open` event read and use it. Now, **you can also avoid the `Change` event to be triggered** adding `Application.EnableEvents = False` before the line `While Not IsEmpty(.Cells(row, col))` and add `Application.EnableEvents = True` before `Next s`. – FaneDuru Jun 29 '22 at 18:14

0 Answers0