Filling a ComboBox range on focus

D

Dkline

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?
 
N

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
'<<=============
 
D

Dkline

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.
 
N

Norman Jones

Hi D,

Try removing the manual ListFillrange assignment
before running the code
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top