Solved

Excel Drop downs

Posted on 2011-03-16
24
229 Views
Last Modified: 2012-06-22
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
Comment
Question by:GiaHughes
  • 13
  • 5
  • 4
  • +1
24 Comments
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 35147464
What version of Excel are you using?  2010?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35147566
No, there isn't. You would have to use a separate control on the worksheet to do that.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35147584
No, you can't change the number of visible items on the dropdown list of a validation list.

jppinto
0
 

Author Comment

by:GiaHughes
ID: 35147842
Well
It is a bit limited, but thanks for responses
Am currenlty using version 2010

Gordon
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35147940
Unfortunatlly, not even on the Excel 2010 version (wich I'm also using) you can change it.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 35147978
HStandy By - I might have a way....
0
 

Author Comment

by:GiaHughes
ID: 35148000
Hi craisin:

I wait in anticipation
Gordon
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 35153392
Not looking good, but am investigating one final thing. Will get back to you after I finish teaching this afternoon.  :-)

Cheers
Chris
(craisin)
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 35155704
Found it!

It is just a matter of preparing the procedure for you...stand by.
0
 

Author Comment

by:GiaHughes
ID: 35155793
Standing by
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 35161402
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
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 35281694
Hello? Are you still working on getting me a sample of the code?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:GiaHughes
ID: 35283485
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
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 35289167
Thanks Gordon.

Am working on it and will get back ASAP

Chris
(craisin)
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 35322598
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
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 35329453
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35330449
So that's a no to the question then? ;)
Shame, I thought I was about to learn something handy!
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 35330693
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35330759
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
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 35331010
Yes, I am using combos box too, but solution is simpler than thsoe given.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35331034
Looking forward to it! :)
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 35333129
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
 
LVL 13

Accepted Solution

by:
Chris Raisin earned 500 total points
ID: 35333363
And here is the file....
SoloCombo.xlsm
0
 

Author Comment

by:GiaHughes
ID: 35383154
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now