I am running into a problem for a few days already that I can't seem to properly solve. I have simplified my problem a little bit below:

I have an activex combobox that I fill using the following data:

Company_Short Company_Long
Google Google Inc.
Yahoo Yahoo Inc.
KLM Royal Dutch Airlines
Wall-mart Wall-Mart Stores

This data is obtained using a SQL query which is done via a button on a sheet. This data is stored locally on the sheet, so no "active" link with the SQL database.

I fill the combobox using the following code:

Private Sub CommandButtonRefresh_Click()
    
    Dim cbvalue As String
    Dim rcount As Integer
    
    rcount = 4
    cbvalue = ComboBoxCompanies.Value
    
    With ComboBoxCompanies
        .Clear
        For i = 0 To rcount - 1
            .AddItem Cells(2 + i, 10).Value
            .List(.ListCount - 1, 1) = Cells(2 + i, 11).Value
         Next i
    End With
    
    ComboBoxCompanies.Value = cbvalue
    
End Sub
A link with a cell is created via:
Private Sub ComboBoxCompanies_Click()
    Range("B4").Value = ComboBoxCompanies.Value
End Sub
The combobox has the following properties:
BoundColumn: 2
ColumnCount: 1

This shows me only the Company_Short name in the combobox while using Company_Long as output.

What I would like a user to be able to do is overwrite the "linked" cell (B4). Of course just typing something in that cell is no problem. The problem occurs when the data is refreshed. I save the current selection (.Value) and use this so set the ComboBox.Value after the ComboBox is filled again with data. This causing the Click() event to run, which than overwrites my "custom" data with combobox data. What would be a nice way to deal with this situation?

I have some ideas, like using an extra checkbox that a user can select to "enable" custom input or link with combobox. But I would really like not to do it this way.

(I need to use something like .Value because it can happen that the data is refreshed and certain rows are not available anymore. Using .ListIndex will definitely select a wrong row afterwards. Or can this be done any way more efficient?)

My second problem is what happens if an item is no longer available. What I do now is use On Error to catch the event at the moment I set the value (Err.Number 380). I this really the best way to do so?