Why combobox change event is running 3 times when changing text format?

[Jamualson]

I want to change the format of the ".Text" property, but then linked cell property is lost. (textColumn and BoundColumn are different)

- I figured out the cause: in the modified format the text is not recognized anymore, so it doesn't know which value to give back to the linked cell and somehow the change event is running 3 times.

But then how can I format only the text property, so it will be a date, and still keeping the linked cell's value as it was?

Seems to me that seperating the text column and bound column formatting destroys things and strange loops are starting.

Private Sub ComboBox1_Change()
    
    MsgBox "Running"
    
    ComboBox1.Text = Format(ComboBox1.Text, "dd_mmm_yyyy")


End Sub

Sub test()

    With MySheet.ComboBox1
        
        .ListFillRange = "A1:B5"
        .LinkedCell = Range("G8").Address
        .ColumnCount = 2
        
        .TextColumn = 1                     'text showing as selected at the top
        .BoundColumn = 2                    'linked cell value

    End With
                                                      
End Sub

 

[RoryA]

My understanding of the sequence would be:
You change the value selected in the control - Change event fires.
Change event reformats the control's Text, which changes it, so the change event fires again.
The reformatted text does not match any of the items in the list, so the Value changes to Null, and the change event fires a third time (reformatting the text doesn't actually change it this time as it was already formatted)

 

[Jamualson]

My understanding of the sequence would be:
You change the value selected in the control - Change event fires.
Change event reformats the control's Text, which changes it, so the change event fires again.
The reformatted text does not match any of the items in the list, so the Value changes to Null, and the change event fires a third time (reformatting the text doesn't actually change it this time as it was already formatted)

Thank you very much.

I understand it better now. So how could I seperate the text and value format?
My idea is:
- instead of using linked cell i can create the same functionality by writing the combobox value directly into a cell (Range("G9").Value = ComboBox1.Value)
- and I could stop the loop part (even if it loops 3 times or if loops endless) by a flag mechanism (see picture)

So eventually the change event is running only once and I can set the text and value format independently, since I switched to a custom cell instead of linked cell.

What do you think about this solution, idea, Rory? It works well on my side, I am just curious if it has down sides. Thank you very much

However I still wondering if this could be achieved by keeping the linked cell property. (With that involved the loop things got too complicated however.)

 

[RoryA]

I never link controls directly to ranges. (I also never use activex controls at all if I can avoid it) Use arrays to populate the controls, which you can format however you like before you load them into the control. I can't imagine why you would format a control differently from its source as you are doing here anyway - it's just confusing.

Your flag variable is the accepted way to prevent event recursion though.

 

[Jamualson]

I never link controls directly to ranges. (I also never use activex controls at all if I can avoid it) Use arrays to populate the controls, which you can format however you like before you load them into the control. I can't imagine why you would format a control differently from its source as you are doing here anyway - it's just confusing.

Your flag variable is the accepted way to prevent event recursion though.

Thank you. I set the ListFillRange and the combobox was modifiing it to "43202" format. That is why i had to format specially the text property.

In the source range the format was correct, so i don't know why it read in wrong format for the text property by default.