Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    dropbox list limitations, work around

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

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    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

    Skipro1.png

    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)
    '-----------------------------------------
    'SET AND DECLARE VARIABLES
    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
    '----------------------------------------
    'POPULATE COMBOBOXES, ADJUST ITEM LENGTH, AND SET VISIBILITY
    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
    Attached Files Attached Files

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Attached Files Attached Files

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    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

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    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
    '--------------------------------------------------------
    'UPDATE NAMED RANGES
        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)
    '---------------------------------------------------------
    
    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

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    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
    Last edited by Maudibe; 2015-01-23 at 22:36.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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.
    Attached Files Attached Files

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •