[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

Excel Drop downs

Is there a way to controll the number of lines you see in a dropdown list created using data validation, currently it appears to be limited to 8 lines
Gordon
0
GiaHughes
Asked:
GiaHughes
  • 13
  • 5
  • 4
  • +1
1 Solution
 
Chris Raisin(Retired Analyst/Programmer)Commented:
What version of Excel are you using?  2010?
0
 
Rory ArchibaldCommented:
No, there isn't. You would have to use a separate control on the worksheet to do that.
0
 
jppintoCommented:
No, you can't change the number of visible items on the dropdown list of a validation list.

jppinto
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GiaHughesAuthor Commented:
Well
It is a bit limited, but thanks for responses
Am currenlty using version 2010

Gordon
0
 
jppintoCommented:
Unfortunatlly, not even on the Excel 2010 version (wich I'm also using) you can change it.
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
HStandy By - I might have a way....
0
 
GiaHughesAuthor Commented:
Hi craisin:

I wait in anticipation
Gordon
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Not looking good, but am investigating one final thing. Will get back to you after I finish teaching this afternoon.  :-)

Cheers
Chris
(craisin)
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Found it!

It is just a matter of preparing the procedure for you...stand by.
0
 
GiaHughesAuthor Commented:
Standing by
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Gordon,

Can you send me  a sample of what you are working wioth at the moment?
(or is the data confidential?)

I am interested only in the section containing one of your dropdowns.
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Hello? Are you still working on getting me a sample of the code?
0
 
GiaHughesAuthor Commented:
Hi Craisin

Sorry for the delay attached file, the cells I am interested in are in the first column which refer to a lookup list
Good luck
Gordon
Test-Excel-Dropdowns.xls
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Thanks Gordon.

Am working on it and will get back ASAP

Chris
(craisin)
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Sorry for the delay . I have been quite ill the last few days and am just recovering (flu).

Now I just need to remember where I put that code. Stand by (again........ :-)   )
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
UUUrrrhhh! Feeling so crook at the moment!

Anyway, looking into this and on the threshold.

Perhaps you can just clarify your procedure for setting up the listbox you currently have on your sheet.

We will have to use VB code and an ActiveX list box.

Will there be any other liostboxes in your workbook apart from the current one?
0
 
Rory ArchibaldCommented:
So that's a no to the question then? ;)
Shame, I thought I was about to learn something handy!
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Yes, I'm afraid that is NO if using of the standard listbox from the Excel menu, sorry!  :-)

Of course, if we had true inheritence within VB we could probably create a class called (say) "droplist" and simply enhance the listbox object, but no can do that I'm afraid. ("Inherits" does not truly work except on user defined objects).

Anyway, stay tuned because I have a nifty solution I am going to post in the next hour or so. Looks the same and is quite efficient.

(Suffering from flu, so this is hard going)....

Chris
(craisin)
0
 
Rory ArchibaldCommented:
There are lots of solutions with non-DV approaches - such as http://www.contextures.com/xlDataVal10.html
See also Dave's article here: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_5062-Drop-Down-List-with-Unique-Distinct-Values-enhancing-the-ComboBox-with-a-few-steps-and-a-little-code.html

(note also it's comboboxes rather than lists here to mimic DV behaviour)
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Yes, I am using combos box too, but solution is simpler than thsoe given.
0
 
Rory ArchibaldCommented:
Looking forward to it! :)
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
OK, here we go.

I attach the revised workbook which includes the code.

You must enable "Programmer" in Outlook 2010 to be able to access the code.

It should be self explanatory, but let me know what you think of this simpler option. This time we have only ONE Combobox which is used everywhere
when a value is changed in column one. This avoids having to have 999 (or more) listboxes.

It may have a few glitches. let me know and I'll see if they can be fixed.
 
The code is included below for interest sake.

It does not solve the problem of short "ListBoxes" but it is an alternative,

When an item is selected the actual value is placed in the field as text.
This workbook does NOT have static listboxes everywhere displaying the current value (as many spreadsheets do). The previous workbook had 999 listboxes (one for each row for column A), plus many more which must have been previously created and then deleted (they do not remove themselves from memory data stored with the spreadsheets).

I have set the comboBox to display 20 items.....much better than 8
(the number with a normal listbox)

Note that there is a new page called "Lists" in which you can maintain the list of items. The list is names "CAR_PARTS" and the Combobox1 picks up those values via its method "ListFillRange"

Cheers
Chris
(craisin)

Private Sub Worksheet_activate()
  Dim nRow As Long
  'If the worksheet loses the combobox control (accidental deletion etc.)
  'simply place a combox in cell "A4" . The oonly value you need to initialize
  'within the control itself is ".ListFillRange" which should be set
  'to a named range (in ComboBox1 we have it set to CAR_PARTS)

  Static bInitialized As Boolean  'Only do this once
  If Not bInitialized Then
    With ComboBox1
      .Height = Cells.Range("A4").Height
      .Width = 133
      .Font.Size = 8
      .Font.Name = "Arial"
      .AutoWordSelect = True
      .BackStyle = fmBackStyleOpaque
      .BorderStyle = fmBorderStyleNone
      .BoundColumn = 1
      .DropButtonStyle = fmDropButtonStyleArrow
      .EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection
      .HideSelection = True
      .ListRows = 20
      .ListStyle = fmListStylePlain
      .MatchEntry = fmMatchEntryComplete
      .MatchRequired = True
      .SelStart = 0
      .ShowDropButtonWhen = fmShowDropButtonWhenFocus
      .Style = fmStyleDropDownList
      .AutoLoad = True
      .SpecialEffect = fmSpecialEffectFlat
    End With
    bInitialized = True
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  Dim nIndex As Integer
  On Error GoTo quickout ' This is in case you are deleting entries from column 1
  If Target.Column = 1 Then
    'Change the combobox to the current cell
    ComboBox1.Top = Target.Top
    ComboBox1.Visible = True
    ComboBox1.BringToFront
    If Len(Target.Value) > 0 Then
      'set the default value in the combobox
      For nIndex = 0 To ComboBox1.ListCount - 1
        If ComboBox1.List(nIndex) = Target.Value Then
          ComboBox1.ListIndex = nIndex
          'no need to keep going
          Exit For
        End If
        DoEvents
      Next
    Else
      ComboBox1.ListIndex = 0
    End If
  Else
    ComboBox1.Visible = False
    ComboBox1.SendToBack
  End If
quickout:
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, cancel As Boolean)
  If Target.Column = 1 Then
    ComboBox1.DropDown
  End If
End Sub
Private Sub ComboBox1_Change()
  ActiveCell.Value = ComboBox1.Value
  ComboBox1.Visible = False
  ComboBox1.SendToBack
End Sub

Open in new window

0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
And here is the file....
SoloCombo.xlsm
0
 
GiaHughesAuthor Commented:
Hi craisin

Thanks for your solution, I now have to get my head around this
In the meantime I will close the question
Gordon
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 13
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now