Filling a ComboBox range on focus

[Guest]

I have a combobox in which the end user wants to add new items from time to
time. I cannot figure out how to fill the ListFillRange from a macro.

On the sheet the control in the formula bar is EMBED("Forms.ComboBox.1","").

In the Name Box it is named as "cmbFinCarrier".

Right now the range is AJ6:AJ41.

I've tried to figure out how to just change the range in the properties.
I've tried the AddItem method. I can't get anything to work.

What do I need to do?

 

[Norman Jones]

Hi D,

Try something like:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim oleObj As OLEObject


Set WB = Workbooks("MyBook.xls") '<<=== CHANGE
Set SH = WB.Sheets("Sheet1") '<<=== CHANGE
Set Rng = SH.Range("AJ6:AJ41")
Set oleObj = SH.OLEObjects("cmbFinCarrier")

With oleObj.Object
.List = Sheets("Foglio1").Range("A6:A20").Value
.ListIndex = 0
End With
End Sub
'<<=============

 

[Norman Jones]

Hi D,

Replace:

.List = Sheets("Foglio1").Range("A6:A20").Value

with

.List = Rng.Value

 

[Guest]

Thank you for your response. However I am running into a "Permission Denied"
error. It appears I need to empty the ListFillRange before I can put in the
new range

I've tried adding a .List = "" and a .List = " " to clear the range first. I
get a runtime 381 "Invalid Range Array Index" error.

 

[Norman Jones]

Hi D,

Try removing the manual ListFillrange assignment
before running the code

 

[Guest]

That solved the problem. Thank you for your help. I am good to go.