If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Number of entries in drop down box
Glad we could be of help! If your query has been solved please take a moment to let us and eveyone else know by going to Thread ToolsMark Thread Solved,shown by this icon 29 +-------------------------------------------------------------------+ |Filename: marksolved.gif | |Download: http://www.thecodecage.com/attachment.php?attachmentid=29| +-------------------------------------------------------------------+ -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320 |
#12
|
|||
|
|||
Number of entries in drop down box
Simon Lloyd wrote:
Glad we could be of help! If your query has been solved please take a moment to let us and eveyone else know by going to Thread ToolsMark Thread Solved,shown by this icon 29 +-------------------------------------------------------------------+ |Filename: marksolved.gif | |Download: http://www.thecodecage.com/attachment.php?attachmentid=29| +-------------------------------------------------------------------+ Just thought I would let you know. I couldn't get the Combo box to work properly (wouldn't copy to any other cells ). I therefore came across avideo on YouTube that showed how to make a Data\Validation\Drop down box. My brain fart than cleared and I remembered that was what my drop down box was. I than checked to see what my range of cells was and I just increased the range from 12 cells to 17 and it works fine. Only think I can't understand is that the range that it references to is a blank range of cells (=#A$230:$A$242). However; when I make changes in a defined range (Builderslist)(='reference Tables'!$A$21:$D$37); which is in a complete different area of the spreadsheet; these changes are reflected in the drop down list. Comment? |
#13
|
|||
|
|||
[SOLVED]: Number of entries in drop down box
Thats because you haven't created a Dynamic range name like this: *=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) * Your named ranges should be dynamic this way they expand and contract with the data in the range -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320 |
#14
|
|||
|
|||
Number of entries in drop down box
Dave,
My question is in relation to this older posting about expanding dropdown lists to include all choices, not just 8-12. I'm currently using your 'myNavigator' code to create a floating toolbar that opens each time I start Excel. The toolbar has a dropdown in it with the names of all worksheets in the open workbook. How do I expand the dropdown list so that all 60-70 worksheet names appear WITHOUT needing to scroll? Thanks. Tondos "Dave Peterson" wrote: If it's a combobox from the control toolbox toolbar, you can go into design mode (another icon on that control toolbox toolbar), then click the properties icon. Look for ListRows and change it to what you want. If it's a dropdown from the Forms toolbar: Rightclick on it and choose Format Control. On the Control tab, change the drop down lines to what you want. If it's a data|validation dropdown, you're stuck with what excel gives you. theslaz wrote: I had to check. I believe it is a combo box! -- theslaz ------------------------------------------------------------------------ theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320 -- Dave Peterson |
#15
|
|||
|
|||
Number of entries in drop down box
I'm not sure you can.
If you add a combobox to a userform, it has a .listrows property that you can specify that number of rows to show. I don't see that property in the combobox placed on a commandbar. But maybe someone will see a way to do this... Tondos wrote: Dave, My question is in relation to this older posting about expanding dropdown lists to include all choices, not just 8-12. I'm currently using your 'myNavigator' code to create a floating toolbar that opens each time I start Excel. The toolbar has a dropdown in it with the names of all worksheets in the open workbook. How do I expand the dropdown list so that all 60-70 worksheet names appear WITHOUT needing to scroll? Thanks. Tondos "Dave Peterson" wrote: If it's a combobox from the control toolbox toolbar, you can go into design mode (another icon on that control toolbox toolbar), then click the properties icon. Look for ListRows and change it to what you want. If it's a dropdown from the Forms toolbar: Rightclick on it and choose Format Control. On the Control tab, change the drop down lines to what you want. If it's a data|validation dropdown, you're stuck with what excel gives you. theslaz wrote: I had to check. I believe it is a combo box! -- theslaz ------------------------------------------------------------------------ theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320 -- Dave Peterson -- Dave Peterson |
#16
|
|||
|
|||
Number of entries in drop down box
An alternative to Dave's sheet navigator is code from Bob Phillips.
See this google search thread for the BrowseSheets macro from Bob Phillips. http://tinyurl.com/yoa3dw You should easily be able to fit 60-70 sheets onto the Form. Gord Dibben MS Excel MVP On Sat, 14 Feb 2009 12:37:02 -0800, Tondos wrote: Dave, My question is in relation to this older posting about expanding dropdown lists to include all choices, not just 8-12. I'm currently using your 'myNavigator' code to create a floating toolbar that opens each time I start Excel. The toolbar has a dropdown in it with the names of all worksheets in the open workbook. How do I expand the dropdown list so that all 60-70 worksheet names appear WITHOUT needing to scroll? Thanks. Tondos "Dave Peterson" wrote: If it's a combobox from the control toolbox toolbar, you can go into design mode (another icon on that control toolbox toolbar), then click the properties icon. Look for ListRows and change it to what you want. If it's a dropdown from the Forms toolbar: Rightclick on it and choose Format Control. On the Control tab, change the drop down lines to what you want. If it's a data|validation dropdown, you're stuck with what excel gives you. theslaz wrote: I had to check. I believe it is a combo box! -- theslaz ------------------------------------------------------------------------ theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320 -- Dave Peterson |
#17
|
|||
|
|||
Number of entries in drop down box
Thanks, guys! I like Bob's macro, too (it appears to solve the problem!)
They're both awesome! I really appreciate your help! Tondos "Gord Dibben" wrote: An alternative to Dave's sheet navigator is code from Bob Phillips. See this google search thread for the BrowseSheets macro from Bob Phillips. http://tinyurl.com/yoa3dw You should easily be able to fit 60-70 sheets onto the Form. Gord Dibben MS Excel MVP On Sat, 14 Feb 2009 12:37:02 -0800, Tondos wrote: Dave, My question is in relation to this older posting about expanding dropdown lists to include all choices, not just 8-12. I'm currently using your 'myNavigator' code to create a floating toolbar that opens each time I start Excel. The toolbar has a dropdown in it with the names of all worksheets in the open workbook. How do I expand the dropdown list so that all 60-70 worksheet names appear WITHOUT needing to scroll? Thanks. Tondos "Dave Peterson" wrote: If it's a combobox from the control toolbox toolbar, you can go into design mode (another icon on that control toolbox toolbar), then click the properties icon. Look for ListRows and change it to what you want. If it's a dropdown from the Forms toolbar: Rightclick on it and choose Format Control. On the Control tab, change the drop down lines to what you want. If it's a data|validation dropdown, you're stuck with what excel gives you. theslaz wrote: I had to check. I believe it is a combo box! -- theslaz ------------------------------------------------------------------------ theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320 -- Dave Peterson |
#18
|
|||
|
|||
Number of entries in drop down box
Thanks for the feedback.
I too like Bob's sheet browser/selector. Gord On Mon, 16 Feb 2009 10:30:01 -0800, Tondos wrote: Thanks, guys! I like Bob's macro, too (it appears to solve the problem!) They're both awesome! I really appreciate your help! Tondos "Gord Dibben" wrote: An alternative to Dave's sheet navigator is code from Bob Phillips. See this google search thread for the BrowseSheets macro from Bob Phillips. http://tinyurl.com/yoa3dw You should easily be able to fit 60-70 sheets onto the Form. Gord Dibben MS Excel MVP On Sat, 14 Feb 2009 12:37:02 -0800, Tondos wrote: Dave, My question is in relation to this older posting about expanding dropdown lists to include all choices, not just 8-12. I'm currently using your 'myNavigator' code to create a floating toolbar that opens each time I start Excel. The toolbar has a dropdown in it with the names of all worksheets in the open workbook. How do I expand the dropdown list so that all 60-70 worksheet names appear WITHOUT needing to scroll? Thanks. Tondos "Dave Peterson" wrote: If it's a combobox from the control toolbox toolbar, you can go into design mode (another icon on that control toolbox toolbar), then click the properties icon. Look for ListRows and change it to what you want. If it's a dropdown from the Forms toolbar: Rightclick on it and choose Format Control. On the Control tab, change the drop down lines to what you want. If it's a data|validation dropdown, you're stuck with what excel gives you. theslaz wrote: I had to check. I believe it is a combo box! -- theslaz ------------------------------------------------------------------------ theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320 -- Dave Peterson |
|
Thread Tools | |
Display Modes | |
|
|