Form Automation

D

dave8

Active Member
Joined
Jul 8, 2007
Messages
275
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" ?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
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.
Code: 
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.
 
Upvote 0
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?
 
Upvote 0
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
Code: 
Sub WorkSheet_Activate()
 
Worksheets(1).OLEObjects("ComboBox1").ListFillRange = "Data"
ComboBox1.ListIndex = 0
Worksheets(1).OLEObjects("ComboBox2").ListFillRange = "Data2"
ComboBox2.ListIndex = 0
 
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Run-Time error ‘-2147417848(800010108)’: (Automation error)
Replies
1
Views
480
julhs
J
L
  • Solved
Worksheet Change_Event not working on cell value change
Replies
9
Views
162
Lil Stinker
L
M
  • Question
Dynamic Cell Editing from Another Sheet
Replies
3
Views
185
Cubist
Cubist
N
  • Solved
Automatic solution for hyperlink within the same file
2
Replies
10
Views
332
etaf
etaf
J
  • Question
Set cell color based on another cell color
Replies
3
Views
598
bobsan42
bobsan42

Forum statistics

Threads
1,216,110
Messages
6,128,895
Members
449,477
Latest member
panjongshing

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top