Dynamic Range in Combo Combo Box

P

plaskettm

New Member
Joined
Feb 14, 2011
Messages
5
Hello, I've generally been able to muddle through problems using the posts on this site - so thanks to everyone for help so far. However, I've now become stuck, hence my first post.

I've created 2 combo boxes using the control tool box.
The first combo box is straight forwards. The user selects a number between 1 & 4, which is linked to cell H2
The second combo box uses the named range "COMB2_Range"
Range = Offset($I$3,0,0,$H$2,1) - thus the number of rows in the list is determined by the selection in of combo box 1.

My problem is that the list displayed when selecting the combo box does not reflect this - i.e. if I select 4 in combo box 1, combox 2 does not display 4 items.

I know this works fine using the form controls but wanted to use the Control options as to enable some of the cosmetic options.

Any help appreciated
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Make sure that your named range is evaluating to the full list when you put it as the LinkedFillRange in design mode ... the number of items will determine how many items are in the drop down ( if less than 8 ). Once designed it will be OK for any amount up to the full list.
 
Upvote 0
Hello Glen,

Thanks for suggestion. This does work but I was trying to get the drop down list to reflect the number of choices without setting to the max potential. i.e. if only 2 options were available I wanted the control to reflect this by having a list of just 2 - not 2 and lots of blank fields. Cosmetics but often important?

Thanks
 
Upvote 0
Put this in your sheet code area:
Code: 
Private Sub ComboBox1_Change()
    Sheet4.ComboBox2.ListFillRange = Range("COMB2_Range").Address
End Sub
... changing the Sheet4 to whatever your sheets code name is.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
ActiveX Control Combo box
Replies
3
Views
216
Jeffrey Mahoney
Jeffrey Mahoney
X
  • Question
Locking/unlocking a cell with a Combo Box value Mouse click problem
Replies
0
Views
484
XrayLemi
X
K
  • Solved
How to using Combo box like As Searchable Dropdown list ? And easy coding For Two OR More Combo box using VBA
Replies
1
Views
725
Akuini
Akuini
C
  • Question
Reset user form combo box to top record
Replies
5
Views
567
James006
James006
J
  • Solved
Combo Box Fill in Header
Replies
2
Views
871
JarekM
J

Forum statistics

Threads
1,215,007
Messages
6,122,670
Members
449,091
Latest member
peppernaut

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