OLEObject ComboBox or ListBox

[Flue]

Anyone know the reason why when I populate a ComboBox or a ListBox via VBA the values clear out as soon as I click anywhere else on the sheet, or save and reopen?

Does the box try to revert the linked cell that has not been defined after code has finished running?

TIA.

 

[Norie]

How are you populating the combobox/listbox?

 

[Flue]

Here is the code I have working.

Dim Desc as String
Dim lstDup As Boolean
Dim WS As Excel.Worksheet

  Application.ScreenUpdating = False

Windows("Master.xls").Activate
Sheets("Master_MAR11").Select
  Set WS = ActiveSheet
  
  'create checkbox
  Set ShtComboBx = WS.OLEObjects.Add("Forms.ComboBox.1")
  With ShtComboBx
    .Left = WS.Range("G2").Left
    .Top = WS.Range("G2").Top
    .Name = "AnsiCombo"
    .Width = "400"
    .Height = "24.00"
  End With

Windows("Master.xls").Activate

WS.OLEObjects("AnsiCombo").Object.Clear

myRow = Application.CountA(ActiveSheet.Range("A:A"))
For j = 3 To myRow
lstDup = False
What = Trim$(Cells(j, 1))

'Search for Code
Windows("CodeList.xls").Activate
   
Do
        Set rng = ActiveSheet.Range("A:A").Find(What, lookat:=xlWhole)
        If rng Is Nothing Then
                        Exit Do
        Else
            Desc = Cells(rng.Row, 2)
            Exit Do
        End If
    Loop
Windows("Master.xls").Activate
For i = 0 To WS.OLEObjects("AnsiCombo").Object.ListCount - 1
    If WS.OLEObjects("AnsiCombo").Object.List(i) = What & " - " & Desc Then
    lstDup = True
    Exit For
    End If
Next

If lstDup = False And What <> "" Then
  WS.OLEObjects("AnsiCombo").Object.AddItem What & " - " & Desc
End If

Next

Which ever item is displayed when the worksheet is saved is the only entry available in the combobox when the worksheet is reopened. Listbox is blank when reopened.

Thanks.