Combo Box Update

[Rawlings99]

Hi,

My combobox is set to update everyone time there is a change to the particular worksheet. Its contents are also clear in order to prevent the combo box from continuously adding the same values to the sheet. However, this combo box is also linked to cell C30. Is there a way to prevent the value of C30 from being cleared everytime there is a change to the worksheet?

Thanks, code below.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long
Dim col As Long
    i = 3
    col = 2
With ActiveSheet.OLEObjects("combobox1").Object
    .Clear
    Do Until Cells(i, col).Value = "Latest 12 Mos"
        .AddItem Cells(i, col).Value
        i = i + 1
    Loop
End With
End Sub

[Andy Pope]

I guess you need to store the current value in a variable and then re-apply when the list update has completed.

[Cheeky Charlie]

Or break the link and apply the value to the cell on combobox_change?