Combo box Dynamic Input Range

Leeinsa

Board Regular
Joined
Jul 24, 2009
Messages
96
A more difficult one this time.

Is there a way of setting the input range on a combo box to a vale set in a cell?

ie, if my current input range is A2:A40 and i then users add another 20 records to that column, i need the combo box input range to change to A2:A60, so I can leave the sheet unmonitored for the users to add lines and not have to manually change the input range of the combo box.

Many thanks,

Lee.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Sahak

Well-known Member
Joined
Nov 10, 2006
Messages
864
Hi Leeinsa,

Try This:

Define name to range A2:A...
Insert > Names > Define
in "Refers To:" enter formula =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
Define name for example "InputRange"
enter same name in RowSource Property of ComboBox
 

Leeinsa

Board Regular
Joined
Jul 24, 2009
Messages
96
Thanks for the reply Sahak,

This idea looked like a winner, but when i try to enter the defined name range Into the combo box as the rowsource it tells me ""reference is not valid.

Any ideas?


Lee

 

Sahak

Well-known Member
Joined
Nov 10, 2006
Messages
864
Hi Lee,

give me your email addres & i will send you sample file
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,683
Office Version
365
Platform
Windows
Why not set the ListFillRange at the runtime of the ComboBox's drop down event, so whenever the dropdown's clicked, the ListFillRange of the Box dynamically updates...........

Code:
Private Sub ComboBox1_DropButton()
Dim last_row As Long

last_row = Sheets("Sheet2").Range("A65536").End(xlUp).Row
Me.ComboBox1.ListFillRange = "Sheet2!A1:A" & last_row
End Sub
Something whacky's happening with the page at present - it won't let me put the full first line of code in properly - the "DropButton" ...should end with "Click()", but it keeps replacing the last few characters with a line of asterisks if I spell it correctly - you'll just have to work it out, but if you go into design mode on your worksheet, and right-click the combobox, then select "View code" you'll be able to select the correct placeholder from the right hand drop down anyway.........

The code finds the last used cell in Column A (sheet 2 in my case, so change to suit) and sets the List Fill Range of the ComboBox to the correct range of cells for you.
 

Leeinsa

Board Regular
Joined
Jul 24, 2009
Messages
96
Sykes,

I tried your code but nothing. I made sure to tweak the code to my specs but still nothing.

Please could you email me a sheet with a working combo box on it so i can try and find what i'm doing wrong?

Many thanks for all your help.

Lee.

Lee@damana.net
 

Sahak

Well-known Member
Joined
Nov 10, 2006
Messages
864
Hi Lee

I have sent two separate emails to Lee@psicom.co.za

& Lee@damana.net
 

Watch MrExcel Video

Forum statistics

Threads
1,102,287
Messages
5,485,903
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top