Form Automation

[dave8]

I have a worksheet with a bunch of comboboxes. The combobox points to cell ranges on another sheet. Is there a way to automate this process such that if another item is added to the list, it would automatically reference this in the comboxbox listfillrange? For example, in combobox1.listfillrange = "sheet2!A1:A5". Now, I'm going to add a value in A6 of Sheet2. Is there a way to program combobox1.listfillrange = "sheet2!A1:A6" ?

 

[Nalani]

Set up two Named ranges

Size
Refers to:
=MATCH(9.99999999999999E+307,Sheet1!$A:$A)-ROW(Sheet1!$A$1)+1

Data
Refers to:
=OFFSET(Sheet1!$A$1,0,0,Size)


Then see if you can modify your macro as such to populate.

Sub WorkSheet_Activate()
 
Worksheets(1).OLEObjects("ComboBox1").ListFillRange = "Data"
ComboBox1.ListIndex = 0
 
End Sub

I just tested with data in A1:A5 and then added another 10 rowsof data.

 

[dave8]

Do you mean to setup the two named ranges in Cell A1?? And, are you referring to the sheet with my comboboxes or the data sheet?

 

[Nalani]

After rereading your post, you say that you have more then one CB but you do not say what sheet(s) they are on. I also see that you are referencing one list, A1:A5, on Sheet2.

Assuming that all ComboBoxes are on Sheet1 and all Lists are on Sheet2:


1. Open your Name Manager window by pressing Ctrl and F3.
2. Click on New
3. In the Name: box, type in Size
4. In the Refers to: box, place this formula
=MATCH(9.99999999999999E+307,Sheet2!$A:$A)-ROW(Sheet2!$A$1)+1
5. Click OK

6. Click New again
7. In the Name: box, type in a name for your range (I used Data)
8. In the Refers to: box, place this formula
=OFFSET(Sheet2!$A$1,0,0,Size)
9. Click OK

The above will take care of the first Named Range and Combo Box1.

For the second Range and ComboBox repeat the above but you will have to give a different name for each. (i.e. Size2 and Data2). You will also have to adjust the column and row references in the formulas.

Also in the Sheet Module the code will look like this for two ComboBoxes

Sub WorkSheet_Activate()
 
Worksheets(1).OLEObjects("ComboBox1").ListFillRange = "Data"
ComboBox1.ListIndex = 0
Worksheets(1).OLEObjects("ComboBox2").ListFillRange = "Data2"
ComboBox2.ListIndex = 0
 
End Sub