Solved

Excel Drop downs

Posted on 2011-03-16
24
220 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
Comment Utility
What version of Excel are you using?  2010?
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
Comment Utility
No, you can't change the number of visible items on the dropdown list of a validation list.

jppinto
0
 

Author Comment

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

Gordon
0
 
LVL 33

Expert Comment

by:jppinto
Comment Utility
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
Comment Utility
HStandy By - I might have a way....
0
 

Author Comment

by:GiaHughes
Comment Utility
Hi craisin:

I wait in anticipation
Gordon
0
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
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
Comment Utility
Found it!

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

Author Comment

by:GiaHughes
Comment Utility
Standing by
0
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
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
Comment Utility
Hello? Are you still working on getting me a sample of the code?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:GiaHughes
Comment Utility
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
Comment Utility
Thanks Gordon.

Am working on it and will get back ASAP

Chris
(craisin)
0
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yes, I am using combos box too, but solution is simpler than thsoe given.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Looking forward to it! :)
0
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
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
Comment Utility
And here is the file....
SoloCombo.xlsm
0
 

Author Comment

by:GiaHughes
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

11 Experts available now in Live!

Get 1:1 Help Now