Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling a ComboBox range on focus
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling a ComboBox range on focus
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 '<<============= --- Regards, Norman "Dkline" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling a ComboBox range on focus
Hi D,
Replace: .List = Sheets("Foglio1").Range("A6:A20").Value with .List = Rng.Value --- Regards, Norman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling a ComboBox range on focus
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" wrote: Hi D, Replace: .List = Sheets("Foglio1").Range("A6:A20").Value with .List = Rng.Value --- Regards, Norman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling a ComboBox range on focus
Hi D,
Try removing the manual ListFillrange assignment before running the code --- Regards, Norman "Dkline" wrote in message ... 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" wrote: Hi D, Replace: .List = Sheets("Foglio1").Range("A6:A20").Value with .List = Rng.Value --- Regards, Norman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling a ComboBox range on focus
That solved the problem. Thank you for your help. I am good to go.
"Norman Jones" wrote: Hi D, Try removing the manual ListFillrange assignment before running the code --- Regards, Norman "Dkline" wrote in message ... 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" wrote: Hi D, Replace: .List = Sheets("Foglio1").Range("A6:A20").Value with .List = Rng.Value --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Canīt set focus on OleObjects ComboBox | Excel Programming | |||
Which combobox has the focus? | Excel Programming | |||
Focus after combobox | Excel Programming | |||
ComboBox and Set Focus | Excel Programming | |||
Focus on combobox | Excel Programming |