Losing Items in Forms.ComboBox Problem
 

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.

Losing Items in Forms.ComboBox Problem
 

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

Losing Items in Forms.ComboBox Problem
 

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

merjet wrote:
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