Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to create a dynamic range in CONTROL TOOLBOX (form)
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to create a dynamic range in CONTROL TOOLBOX (form)
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).Addre ss 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. " wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to create a dynamic range in CONTROL TOOLBOX (f
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!!!! "JLatham" wrote: 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).Addre ss 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. " wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to create a dynamic range in CONTROL TOOLBOX (f
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to create a dynamic range in CONTROL TOOLBOX (f
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 " wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to create a dynamic range in CONTROL TOOLBOX (f
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!!!! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to create a dynamic range in CONTROL TOOLBOX (f
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. " wrote: 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!!!! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to create a dynamic range in CONTROL TOOLBOX (f
Yup!!... I have the two codes. Thank you once again!!!
"JLatham" wrote: 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. " wrote: 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!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where is the control toolbox? | New Users to Excel | |||
Where is the control toolbox? | New Users to Excel | |||
Control Toolbox | Excel Discussion (Misc queries) | |||
Control toolbox | Excel Worksheet Functions | |||
How do I create a form in a worksheet with control option buttons. | New Users to Excel |