Combo box Dynamic Input Range

[Leeinsa]

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.

 

[Sahak]

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]

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]

Hi Lee,

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

 

[Leeinsa]

Did you get my email address?

here it is again. Lee@damana.net.

Thanks,

Lee.

 

[sykes]

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...........

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]

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]

Hi lee

I have sent sample file. Please let me know.

 

[Leeinsa]

Hey Sahak,

I have not yet received the file, If there has been a problem, perhaps you could try this email address instead. Once i've received the file, i'll let you know how it goes.

Lee@psicom.co.za

Lee@damana.net

Thanks,

Lee.

 

[Sahak]

Hi Lee

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

& Lee@damana.net