In Excel 2007, how do I get a larger font size inside the selectable drop-down list?

I have the whole worksheet zoomed to only 65 % but must keep it so. Then when I created a selectable drop-down list in a cell with 6 items each item has a very small font size so it's hard to read it. Can I enter some code in the Data validation window when creating the selectable drop-down list?
LVL 1
hermesalphaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

You can do it by using an ActiveX Dropdown

be sure to fill the ListFillRange and the LinkedCell Properties and change the Font to the size you want

see example to see the properties

Goto Developer / Controls / Activate Design Mode
select the combobox and
Goto Developer / Controls / Activate Properties

Regards
EE20151023.xlsx
Martin LissOlder than dirtCommented:
There are problems you can encounter when you use ActiveX controls on a worksheet such as the control getting smaller each time you click it. You can however float an ActiveX combobox over the data validation list as described in this article of mine.
Martin LissOlder than dirtCommented:
Here's how to float the combobox on the sheet. It's based on my The magical floating ActiveX control article.

Add an ActiveX combobox called TempCombo to the sheet and then add the following code. Please note that after adding this code that the "Maintenance" sub needs to be run if you want to modify the data validation for the combobox.

Sheet Code
Option Explicit
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
' Move to next cell if Tab or Enter are pressed
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 1).Activate
        Case 13 'Enter
            ActiveCell.Offset(1, 0).Activate
    End Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Application.EnableEvents = False
    Application.ScreenUpdating = True

    If Application.CutCopyMode Then
        'allow copying and pasting on the worksheet
        GoTo errHandler
    End If
    
    ShowAutocomplete Target
    
errHandler:
    Application.EnableEvents = True
    Exit Sub

End Sub

Open in new window

Module Code
Option Explicit
Public gbMaintBeingDone As Boolean
Sub Maintenance()
' This macro is a toggle and it's purpose is to prevent/allow the
' autocomplete combobox from being displayed so that Data
' Validation can be maintained if necessary.
gbMaintBeingDone = Not gbMaintBeingDone
End Sub

Public Sub ShowAutocomplete(Target As Range)
    Dim strVF As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Dim strParts() As String
    Dim lngIndex As Long
    
    On Error GoTo errHandler

    Set ws = ActiveSheet
    
    If gbMaintBeingDone Then
        Exit Sub
    End If
    
    Set cboTemp = ws.OLEObjects("TempCombo")
    'On Error Resume Next
    With cboTemp
        ' Clear and hide the combo box
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    
    If Target.Validation.Type = 3 Then
        ' The cell contains a data validation list
        Application.EnableEvents = False
        With cboTemp
            ' Show the combobox with the list
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            ActiveSheet.TempCombo.Font.Size = 24
            If Left$(Target.Validation.Formula1, 1) <> "=" Then
                ActiveSheet.TempCombo.Clear
                strParts = Split(Target.Validation.Formula1, ",")
                For lngIndex = 0 To UBound(strParts)
                    ActiveSheet.TempCombo.AddItem strParts(lngIndex)
                Next
            Else
                ' Get the data validation formula
                strVF = Target.Validation.Formula1
                strVF = Right(strVF, Len(strVF) - 1)
                .ListFillRange = strVF
            End If
            .LinkedCell = Target.Address
        End With
        cboTemp.Activate
        ' Open the drop down list automatically
        ActiveSheet.TempCombo.DropDown
    End If
    
    Application.EnableEvents = True
    On Error GoTo 0
    Exit Sub

errHandler:

    Application.EnableEvents = True
    ' If it's 1004 there's no data validation in the cell
    If Err.Number <> 1004 Then
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShowAutocomplete"
    End If
    
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

hermesalphaAuthor Commented:
Very sorry for not replying until now.

Martin, I began now with adding the ActiveX Combo Box over my drop-down menu, and get this:

=EMBED("Forms.ComboBox.1";"")

Is it here I should enter the name of the Combo Box? Where it now reads "Forms.ComboBox.1"? And keep everything else as it is above?
Martin LissOlder than dirtCommented:
After adding the control anywhere on the sheet, type "TempCombo" without the quotes where it says "ComboBox1" and press return. That names (actually renames) the control.Name the control
hermesalphaAuthor Commented:
So I don't need to delete my current drop-down list (can keep it as it is)?

And add the combobox anywhere on the sheet (but can I make it look better, for example by hiding it)? Or should I follow the instructions in your article when placing the combobox (and not follow Rgonzo1971's instructions here)?:
http://www.experts-exchange.com/articles/12362/The-magical-floating-ActiveX-control.html 
So that I place each combobox over a dropdown menu?

And then rename the combobox by placing the cursor over it and type TempCombo?

And finally I enter the VBA for sheet and module?

And after this, I am not clear what happens with the font size: do you mean it will be automatically adjusted? Or will I be given an option somewhere to adjust the font size (given the option when having activated the cell with the dropdown menu)?
Martin LissOlder than dirtCommented:
Keep your Data Validation dropdown list(s), in fact the code depends on them being there. The code will still work if all you want to do is to show data from a range of cells. The combobox will automatically be hidden by the code, and will show up automatically when the cell that contains the data validation is entered. The font size is also set in the code. I'm attaching a working example.
28768099.xls
hermesalphaAuthor Commented:
Thanks, your example is exactly how I would like to have it.

I already have a VBA code for that page could I see now. Is it just to add your code on the row after where the first code ends? And after having added your code I add the combo box?
Martin LissOlder than dirtCommented:
Can you post the code that you are referring to?

You should add the combobox before you add my code.
hermesalphaAuthor Commented:
I didn't have any code for the sheet, but I had a code for the module (module 1). So I added your module code to a new module called module 2, should I have done that? Or just added your code to the end of the existing code in module 1?

I added the ActiveX combo box also, and then added worksheet code and module code (to a new module, module 2). Then exit the VBA Editor, but the combobox is not hidden, it's visible all the time. And the text in my drop-down lists is of the same size as before.

I then tried going back to VBA Editor and chose "Run Sub/UserForm", but it didn't work either.

Could the reason that it doesn't work now be that I should have pasted your module code into module 1?
Martin LissOlder than dirtCommented:
Please attach your workbook.
hermesalphaAuthor Commented:
Here is the Excel page. Now the combobox has disappeared (invisible?), but all the alternatives are displayed in cell AA84. And when I select the cell the text size becomes to big.
To-EE-20151203.xlsm
Martin LissOlder than dirtCommented:
In the ShowAutoComplete sub in Module2 you'll find this code. Line 6 sets the width of the combobox and line 8 sets the font size. You can change them to whatever values you want. For example you could change line 6 to .Width = Target.Width + 100 and/or line 8 to ActiveSheet.TempCombo.Font.Size = 14.

        With cboTemp
            ' Show the combobox with the list
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            ActiveSheet.TempCombo.Font.Size = 24
            If Left$(Target.Validation.Formula1, 1) <> "=" Then
                ActiveSheet.TempCombo.Clear
                strParts = Split(Target.Validation.Formula1, ",")
                For lngIndex = 0 To UBound(strParts)
                    ActiveSheet.TempCombo.AddItem strParts(lngIndex)
                Next
            Else
                ' Get the data validation formula
                strVF = Target.Validation.Formula1
                strVF = Right(strVF, Len(strVF) - 1)
                .ListFillRange = strVF
            End If
            .LinkedCell = Target.Address
        End With

Open in new window

hermesalphaAuthor Commented:
Ok, I changed to 100 and 14 and the font size got normal again. But all the alternatives are still displayed after each other (not in a list from top down).
hermesalphaAuthor Commented:
There is no possibility to select any alternative in the drop-down menu because all menu items are on a row after each other.
Martin LissOlder than dirtCommented:
Sure they are.
list
Martin LissOlder than dirtCommented:
I'm sorry but I don't understand what you mean by

There is no possibility to select any alternative in the drop-down menu because all menu items are on a row after each other.
hermesalphaAuthor Commented:
Here is a screencast of what I mean with that the alternatives are not in a list any longer but displayed one after each other on a single row only:

http://screencast.com/t/Qu9sQs49S

Everything else works fine now with font size and width, only remains this to get the alternatives in a list displayed vertically and not horisontally like now.
hermesalphaAuthor Commented:
I didn't read your comment at 20.01.36. I wish I had that, exactly the way it should be. But mine displays all alternatives "horisontally" only, not vertically in a list like yours.
Martin LissOlder than dirtCommented:
Do you know how to create Named Ranges? If so, put the individual values for the priorities in a column (one value per cell) and create a Named Range from the values. Then delete the contents of AA84 and change the Data Validation for that cell to be equal to that named range.

BTW what version of Excel do you use?
hermesalphaAuthor Commented:
I don't know much about VBA, am learning a little now as I ask questions here at EE. I can follow your explanation somewhat: so empty cell AA84 and point it to retrieve value from named range? But I have no idea how to perform the operation. But I would like to avoid having an external list (I suppose named range means I must create a second list somewhere, would like to avoid it because I have so much info already on this Excel and so many different tabs).

My version of Excel is 2007.
hermesalphaAuthor Commented:
I created a new drop-down list now in another area on the same sheet, and the same thing happens here also: all the options in the drop-down menu are displayed on a single row. By the way, the way I created the drop-down menues is that I chose Data validation and entered the options "manually" (not using an "external" list).
Martin LissOlder than dirtCommented:
I'm attaching a workbook from my magical floating ActiveX control article. It uses Named Ranges rather than a manual list as you do. Please give it a try and see if it works properly.
Floating-Combo-On-entry.xls
hermesalphaAuthor Commented:
Thanks Martin, after you added your code in my workbook it works fine for this drop-down menu in cell AA84.

I have several other drop-down menues I would like the same thing happen for (that the text inside it becomes readable). So I wonder if it's possible to change anything in your existing code so it is valid for all drop-down menues on all worksheets in the whole workbook?
Martin LissOlder than dirtCommented:
The code in the worksheet will work for all drop downs on the sheet and you just to copy that code to any other worksheet to have it also work there.
hermesalphaAuthor Commented:
For some reason it doesn't work for the other drop-down menu I have on the same sheet. This drop-down menu (in cell J8 on tab "Viktad rankning") doesn't behave the same way as your drop-down menu. I created this drop-down menu the "manual" way by adding the menu options directly.
Martin LissOlder than dirtCommented:
Do you have a test or real workbook you could attach here or send to me via EE message?
Martin LissOlder than dirtCommented:
I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.

Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.