Is it possible to create a dynamic range in CONTROL TOOLBOX (form)

[Qull666]

Is it possible to create a dynamic range in CONTROL TOOLBOX (form).

I have tried this:

1) in the ListFillRange: Listing (refer 2)

2) Name Range:
Called: Listing
Syntax: =offset(Sheet1!$E$2,0,0,CountA(Sheet1!$E:E),1)

But somehow the form doesn't pick up the new data in the name range.

Is there a way to automate this?

Thanks.

 

[JLatham]

My assumptions on this:
The control is a ComboBox
It's from the Controls Toolbox (not the Forms tools)
It's on a worksheet in the workbook.
Sheet with list is Sheet1 and list is in column E starting at E2.

Go to the sheet with the combobox on it and copy and paste, then modify the
code below into the worksheet's code area (right-click on the sheet's name
tab and choose [View Code] to get to the proper place to paste the code into).

Private Sub ComboBox1_GotFocus()
Dim listRange As String

'if no gaps in the list
listRange = "Sheet1!E2:" &
Worksheets("Sheet1").Range("E2").End(xlDown).Address
ComboBox1.ListFillRange = listRange

End Sub

This will refresh the contents of the list each time that control gets
'focus'.

If your set up is different than I've presumed it to be, then describe the
setup in more detail, please.

 

[Qull666]

Dear JLatham,

Yes, this is it!!!!!...... The codes work fine.
Thanks for helping me out.

I was testing the Controls Toolbox (not the Forms tools) coz it has more
formatting features as opposed to Forms tools before putting it into the
actual workbook.

Once again, thanks for the help!!!!

My assumptions on this:
The control is a ComboBox
It's from the Controls Toolbox (not the Forms tools)
It's on a worksheet in the workbook.
Sheet with list is Sheet1 and list is in column E starting at E2.

Go to the sheet with the combobox on it and copy and paste, then modify the
code below into the worksheet's code area (right-click on the sheet's name
tab and choose [View Code] to get to the proper place to paste the code into).

Private Sub ComboBox1_GotFocus()
Dim listRange As String

'if no gaps in the list
listRange = "Sheet1!E2:" &
Worksheets("Sheet1").Range("E2").End(xlDown).Address
ComboBox1.ListFillRange = listRange

End Sub

This will refresh the contents of the list each time that control gets
'focus'.

If your set up is different than I've presumed it to be, then describe the
setup in more detail, please.

Is it possible to create a dynamic range in CONTROL TOOLBOX (form).

I have tried this:

1) in the ListFillRange: Listing (refer 2)

2) Name Range:
Called: Listing
Syntax: =offset(Sheet1!$E$2,0,0,CountA(Sheet1!$E:E),1)

But somehow the form doesn't pick up the new data in the name range.

Is there a way to automate this?

Thanks.

 

[Qull666]

Dear JLatham,

NEW QUESTION:

The codes work fine, however when I actually tested it out, the display on
the Combo Box shows 26 rows of data. My data (listing) is 34 rows (no empty
lines in between).

What I've Done
1) I have checked the Dynamic Range, the range is 34 rows.
2) Checked the Combo Properties for data limitation (can't find one)

Please advice. Thank you.

 

[JLatham]

Are you sure about no gaps/empty cells from E2 all the way to the end of your
list? That's the only way I can duplicate the limitation using Excel 2003.

But just in case, try this code instead of the one posted earlier (and if
you're using Excel 2007, change Rows.Count to Rows.CountLarge) along with
whatever other changes you may have had to make for your specific
workbook/sheet. The previous code would give you the address of the last
cell below E2 with something in it - an empty cell would cause it to stop.
This code will find the last cell in column E with something in it, so in
effect it ignores gaps in your list. Of course, if you've got stuff in
column E below the bottom of your list, it's going to include those into the
list also. The limit for # of items is far greater than 24 or 36. The limit
of items (in Excel 2003) in a Data Validation list is 1024, and it's far
greater than than for a combo or listbox from one of the toolbars.
Private Sub ComboBox1_GotFocus()
Dim listRange As String

'if gaps in the list
listRange = "Sheet1!E2:" &
Worksheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Address
ComboBox1.ListFillRange = listRange

End Sub

 

[Qull666]

Dear JLatham,

My mistake, I shouldn't have included the Dynamic Data Range
(=offset...something something) in the ListFillRange.

There is no need to include the Dynamic Range. Just the codes will do!

Sorry for the hassle and THANK YOU for the help!!!!

 

[JLatham]

No problem - now you have 2 different ways of doing it (from top down or
bottom up) along with having learned, on your own, how to look for problems
within the code or control setup.

 

[Qull666]

Yup!!... I have the two codes. Thank you once again!!!