Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Data Validation Question

    I like to use data validation drop down picks to fill in a cell. My question is about the size of the drop down window and the size of the font. Is there any control over these two aspects? I would like to see a larger font/bow so it would easy to read by seniors that use my spreadsheets.

    Thanks in advance

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alrom,

    I can't find any direct way to change the settings. You can however teach your users to use the zoom function {Ctrl+Mouse wheel} to zoom the entire worksheet and it will also zoom the drop-downs proportionally. HTH

    Example 100%
    z00m100.JPG

    Example 145%
    z00m145.JPG

    P.S. the thumbnails look like they they are the same but if you click on one then use the next previous buttons you'll clearly see the difference.
    Last edited by RetiredGeek; 2013-05-24 at 15:23.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    If RG's solution does not work for you, there are some tips here that might help you. Scroll down to: Data Validation Font Size and List Length
    http://www.contextures.com/xlDataVal08.html

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts

    Custom Listbox as a dropdown

    Alrom,

    Here is some code that will use a listbox instead of a dropdown validation list. The font is customizable and the size will grow/shrink to accommodate the number of list items. If the user clicks on certain predetermined cells (hot cells), the listbox will appear.

    CustomDataValidation.jpg CustomDataValidation2.jpg

    Once the code is in placed in the sheet module, adding new hot cells is as easy as adding new data validation:

    1. Darw a listbox on the sheet. The contents, postion, size, and visibility are unimportant as they will be controlled when the code runs.

    2. Using the Name Manager, create a named range called HotCells that include all the cells that will have the validation. In this example, there are B1, B2, B4, and B5. Add to it if you add additional cells.

    3. Add the cell address to an existing case statement with the same values or copy and paste a new case statement and change the values.

    HTH,
    Maud

    Code:
    Private Sub ListBox1_Change()
        'ADDS THE SELECTED VALUE TO THE CELL FROM LISTBOX
        ActiveCell.Value = ListBox1.Value
        ListBox1.Visible = False
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("HotCells")) Is Nothing Then
            Val1 = Target.Value
            ListBox1.Clear
            Target.Value = Val1
            Select Case Target.Address
                Case "$B$1", "$B$4"  'ADD ADDITIONAL CELL ADDRESSES IF THE SAME CHOICES
                    ListBox1.AddItem ("Bob")
                    ListBox1.AddItem ("Jim")
                    ListBox1.AddItem ("John")
                    ListBox1.AddItem ("Kevin")
                    ListBox1.AddItem ("Larry")
                    ListBox1.AddItem ("Mike")
                    ListBox1.AddItem ("Bob")
                    ListBox1.AddItem ("Paul")
                    ListBox1.AddItem ("Rick")
                    ListBox1.AddItem ("Steve")
                    ListBox1.AddItem ("Ted")
                    ListBox1.AddItem ("William")
                    ListBox1.AddItem ("")
                    Call ListboxSetup  'RESIZES AND REPOSITIONS THE LIST BOX
                Case "$B$2", "$B$5"  'ADD ADDITIONAL CELL ADDRESSES IF THE SAME CHOICES
                    ListBox1.Clear
                    ListBox1.AddItem ("CEO")
                    ListBox1.AddItem ("Doctor")
                    ListBox1.AddItem ("Nurse")
                    ListBox1.AddItem ("Security")
                    ListBox1.AddItem ("Technician")
                    ListBox1.AddItem ("Transportation")
                    ListBox1.AddItem ("")
                    Call ListboxSetup  'RESIZES AND REPOSITIONS THE LIST BOX            
                     'PASTE A COPIED CASE STATEMENT HERE FOR A NEW CELL AND CHANGE THE VALUES
            End Select
        Else
            ListBox1.Visible = False
        End If
    End Sub
    
    Public Sub ListboxSetup()
    'RESIZES AND REPOSITIONS THE LIST BOX
        ListBox1.Font.Size = 12  'CHANGE THE FONT SIZE HERE
        ListBox1.Height = ListBox1.ListCount * (ListBox1.Font.Size * 1.2)
        ListBox1.Width = ActiveCell.Width
        ListBox1.Left = ActiveCell.Left
        ListBox1.Top = ActiveCell.Top + ActiveCell.Height
        ListBox1.Visible = True
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-05-25 at 02:04. Reason: Revised file to avoid error message when opening if saved last while listbox open

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    The process can be made even easier by adding as the first line of the Private Sub Worksheet_SelectionChange routine:

    Names.Add Name:="HotCells", RefersTo:="=$B$1,$B$2,$B$4,$B$5"

    The initial Hotcell range will be created the first time the code is run. To add a new hotcell, the cell can be added to the HotCell range via the above line instead of the Name Manager. Step 2 in the above post is totally eliminated.

    Also, the AddItem values may be filled from cell values instead of hard coded.

    Code:
                Case "$B$1", "$B$4"  'ADD ADDITIONAL CELL ADDRESSES IF THE SAME CHOICES
                    For I = 1 To 13
                        ListBox1.AddItem (Worksheets("Sheet2").Cells(I, 1).Value)
                    Next I
                    Call ListboxSetup  'RESIZES AND REPOSITIONS THE LIST BOX

Posting Permissions

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