Losing Items in Forms.ComboBox Problem

[Wayne Cressman]

I'm creating a forms combobox to place on a worksheet using the
following code, which also populates the combobox with an array.

----------------------
Public Sub CreateGraphFilterCombos(rng1 As Range, varComboName As
Variant, pt As PivotTable)
Dim combo1 As OLEObject, objWs As Worksheet, i As Integer, arrPf As
Variant, lineNum As Integer
Dim strTemp As Integer
Set objWs = rng1.Worksheet

'create Drop - down
With rng1
Set combo1 = objWs.OLEObjects.Add _
(ClassType:="Forms.Combobox.1", Link:=False, _
DisplayAsIcon:=False, Left:=.Left, _
Top:=.Top, Width:=.Width, Height:=.Height)
combo1.Placement = xlMoveAndSize
combo1.Height = combo1.Height + 3
End With
combo1.Name = "combo" & RemoveIllegalSQLChars(varComboName)

'get array to populate combo from varComboName column in database
arrPf = getUniqueColValuesDb(CStr(varComboName))

'populate combo with array
With combo1.Object 'gives us access to combo object
.AddItem "(All)"
For i = 1 To UBound(arrPf, 1)
.AddItem (arrPf(i, 1))
Next
.ListIndex = 0
.FontSize = 9
End With
End Sub
----------------------

This procedure works great. However I have an unusual problem. If I
close and then open the workbook, I lose all but the selected value in
the combobox. So if the combobox shows MfrA,MfrB,MfrC,MfrD when I
create it, and I select MfrC, all the other values disappear when I
reopen the workbook.

What am I missing here?

Thanks,
Wayne C.

 

[merjet]

Maybe the Sub that populates the ComboBox should execute whenever the
file is opened (skip adding the Combobox when it's already there) and
store the prior selected value in a cell.

Hth,
Merjet

 

[Wayne Cressman]

That's a possibility I've considered, but it seems to be a bit of a
kludgy workaround.