dropbox list limitations, work around

Topic

New Reply

I have become aware of the 8 row limits [without scrolling] in a dropbox list. Apparently a combobox approach overcomes this limitation.
I have an example that works but I need help to modify it.

In attached file, “J” is the list for “B” and “L” for “C”.

If you select cell and click arrow, you get the Limitation 8 rows visible without scrolling, but if you double click cell a box drops down which exceeds this 8 row limitation, “B” shows 7 rows [only 7 rows in list “J”] and “C” shows all 12 rows in “L”. This is fine but I need to modify this.

I need to:
1) increase “C” from 12 rows to 15 rows, or any other drop down list screen in the future.
2) add “D” from list “N” with adequate rows visible.

Reply | Quote

Replies

Skipro,

May solution follows the technique you seemed to have abandoned by using a textbox instead of a dropdown box. In fact, I have coded 3 of theM, one for each column that ride up and down the columns appearing right over the selected cell. When you dropdown the combo box, it will open with the number of rows to match the number of items in the source list. If you add additional items to the lists, the comboboxes grow accordingly. I built this in 2010 but I do not have 2003 on hand to test compatibility.

HTH,
Maud

39062-Skipro1

Code:

Dim Cell As Range

Private Sub ComboBox1_Change()
Cell.Value = ComboBox1.Value
ComboBox1.Visible = False
Set Cell = Nothing
End Sub

Private Sub ComboBox2_Change()
Cell.Value = ComboBox2.Value
ComboBox2.Visible = False
Set Cell = Nothing
End Sub

Private Sub ComboBox3_Change()
Cell.Value = ComboBox3.Value
ComboBox3.Visible = False
Set Cell = Nothing
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[COLOR=”#008000″]’—————————————–
‘SET AND DECLARE VARIABLES[/COLOR]
Dim LastRowJ As Long, LastRowL As Long, LastRowN As Long
LastRowJ = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row
LastRowL = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
LastRowN = ActiveSheet.Cells(Rows.Count, 14).End(xlUp).Row
If Target.Cells.Count > 1 Then Exit Sub
[COLOR=”#008000″]’—————————————-
‘POPULATE COMBOBOXES, ADJUST ITEM LENGTH, AND SET VISIBILITY[/COLOR]
Select Case Target.Column
    Case 2
        Set Cell = Target
        If Not Intersect(Target, Range(“B2:B12”)) Is Nothing Then
            ComboBox2.Visible = False
            ComboBox3.Visible = False
            ComboBox1.Visible = True
            ComboBox1.Top = ActiveCell.Top
            ComboBox1.Left = ActiveCell.Left
            ComboBox1.List = Range(“J2:J” & LastRowJ).Value
            ComboBox1.ListRows = Range(“J2:J” & LastRowJ).Count
        End If
     Case 3
        Set Cell = Target
        If Not Intersect(Target, Range(“C2:C12”)) Is Nothing Then
            ComboBox1.Visible = False
            ComboBox3.Visible = False
            ComboBox2.Visible = True
            ComboBox2.Top = ActiveCell.Top
            ComboBox2.Left = ActiveCell.Left
            ComboBox2.List = Range(“L2:L” & LastRowL).Value
            ComboBox2.ListRows = Range(“L2:L” & LastRowL).Count
        End If
      Case 4
        Set Cell = Target
        If Not Intersect(Target, Range(“D2:D12”)) Is Nothing Then
            ComboBox1.Visible = False
            ComboBox2.Visible = False
            ComboBox3.Visible = True
            ComboBox3.Top = ActiveCell.Top
            ComboBox3.Left = ActiveCell.Left
            ComboBox3.List = Range(“N2:N” & LastRowN).Value
            ComboBox3.ListRows = Range(“N2:N” & LastRowN).Count
        End If
End Select
End Sub

Reply | Quote

Hi Maud

Nice method. I tried a different approach.
In my attached version, I use the right-click menu for each of the data entry columns.

When you open the attached workbook (with macros enabled), the User is reminded that they can use the right-click menu for data entry.

The new right-click menu is context-sensitive, i.e. will only show the relevent new menu option depending on the column you are in.
The new menu option is placed first in the list (and a second option ‘Save’ has been added for convenience)
If you right-click in any other columns, you just see the default right-click menu.

The menu items are created from named lists of valid items e.g. [listWeekday] , [listContract1], [listContract2]
If you update the entries in these named ranges, they will be shown in the relevant right-click menu.

You could further refine my method to limit the new right-click menus to specific named data entry ranges if you wish.
For simplicity, I just left it for the specified columns.

I used the Sub Worksheet_BeforeRightClick method to achieve this.

zeddy

Reply | Quote

Zeddy,

Sweet!! I often manipulate the right context menu to achieve tasks but I really like your method to open a side menu. Can you adjust the code to increase the number of selections as the selection list grows?

Maud

Reply | Quote

Hi Maud

If you update the entries in these named ranges, they will be shown in the relevant right-click menu.

..no code adjustments required.

zeddy

Reply | Quote

Zeddy,

Adding the following lines (in blue) to your code will update your named ranges on the fly instead of manually having to do it.

Nice code!

Code:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim LastRow As Long
zCol = Target.Column
[COLOR=”#008000″]’——————————————————–
‘UPDATE NAMED RANGES[/COLOR]
    [COLOR=”#0000FF”]LastRow = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row
    ActiveWorkbook.Names(“listWeekday”).RefersTo = Range(“J2:J” & LastRow)
    LastRow = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
    ActiveWorkbook.Names(“listContract1”).RefersTo = Range(“L2:L” & LastRow)
    LastRow = ActiveSheet.Cells(Rows.Count, 14).End(xlUp).Row
    ActiveWorkbook.Names(“listContract2”).RefersTo = Range(“N2:N” & LastRow)
[/COLOR][COLOR=”#008000″]’———————————————————[/COLOR]

Select Case zCol
Case [B1].Column
LastRow = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row
menuWeekday
Case [C1].Column
menuContract1
Case [D1].Column
menuContract2
Case Else
Application.CommandBars(“Cell”).Reset
End Select

End Sub

Reply | Quote

Hi Maud

What I meant was, if you changed any of the entries in the named lists, then the menus will immediately show those current items. And if you insert cells within the named ranges, then these will also be immediately available.

My preference is to have a separate (hidden) sheet which I would name [Lists], to keep my various ‘pick lists’.
And then on this sheet, I would use event triggers to automatically update named ranges whenever any item was added to the bottom of existing lists. In my case, I would also have the list entries automatically sorted too. But there are cases where the actual list may be required in the existing ‘as is’ order.

Your code does the trick OK, but if columns are inserted etc it means not forgetting to update the code again.

zeddy

Reply | Quote

I need to:
1) increase “C” from 12 rows to 15 rows, or any other drop down list screen in the future.

Yep, I understood that the list would but updated if the existing values were changed and your code does that quite well. With the added code, it will also automatically update additional entries added which I interpreted as what Skipro wanted to happen. It was just a tweek to a nicely written piece of code.

Your code does the trick OK, but if columns are inserted etc it means not forgetting to update the code again

Yet, with new columns, your code would have to be updated as well to accept a new named range. No way around it unless you write code to check for new columns, create a new named ranges, then add it to your submenus.

Maud

Reply | Quote

Maud and zeddy,
Thanks. This is like dueling scripts [as in dueling banjos].

One last question about the original file which I had attached and which I have again attached for convenience. In this file, where are the ranges defined for the number of rows that appear in the large dropdown screens A & B when you double click them?
I see where you define ranges or rows in your scripts but no in this file. It seems this should have either defined names associated with the comboboxes, but none appears to exist, or in the scripts, which I do not see where this is defined. Also, changing “ListRows” in properties has no effect, which I think is intentionally scripted not to.

Reply | Quote

Hi skipro

Maud and I aren’t duelling – we are playing with Excel, showing different ways of doing things.

As for your question, the ranges in your first file are ‘defined’ in the Data>Validation>Source box, for the relevant cells.
For example, select cell [B7], then, in the top-panel toolbar select Data, then from the menu options select Validation, then on the [Settings] tab, look in the Source box for the defined range.
Ditto, select say, cell [C3] and repeat.

zeddy

Reply | Quote

Skipro

When I write a piece of code, I always invite someone to add, change, or recommend modifications to improve the code. I have honed my skills from other member’s inputs, especially Zeddy, who often sees things from a different angle. I can only hope that I can do the same for someone else. Bouncing ideas only makes us better.

Maud

Reply | Quote