Drop down list Font size

Student1000

Board Regular
Joined
Jul 17, 2007
Messages
58
How do you increase the font size of a drop down list in excel apart from zooming in. I have 10 dropdwnbxs each with about 16 items.
Everything works fine but users can not see the contents of each drop down box(fonts are too small). Is there a solution to this? (or just keep zooming in/out). Example i have a drop down box in A2 with source from F2:F17.....etc

How can i increse the font size?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
It might help if you said what drop downs you mean - Data validation, ComboBox
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,397
Office Version
  1. 365
Platform
  1. Windows
Student1000

There is a work-around. It involves using a ComboBox instead of Data Validation. There is a good, clear explanation and instructions here:
http://www.contextures.com/xlDataVal10.html
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
Student1000

There is a work-around. It involves using a ComboBox instead of Data Validation. There is a good, clear explanation and instructions here:
http://www.contextures.com/xlDataVal10.html

That's a neat solution, the only problem IMO is that Double clicking in not intuitive, you have to actually inform the user to do this, unlike Data Validation. This amendment allows the Selection_Change event to be used instead of double_click.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim str    As String
    Dim cboTemp As OLEObject
    Dim ws     As Worksheet
    Set ws = ActiveSheet

    If Target.Validation.Type = xlValidateList Then
        Set ws = ActiveSheet
        Cancel = True
        Set cboTemp = ws.OLEObjects("TempCombo")
        On Error Resume Next
        With cboTemp
            .ListFillRange = ""
            .LinkedCell = ""
            .Visible = False
        End With
        On Error GoTo errHandler
        Application.EnableEvents = False
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)

        With cboTemp
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            .ListFillRange = ws.Range(str).Address
            .LinkedCell = Target.Address
        End With
        cboTemp.Activate
    End If

errHandler:
    Application.EnableEvents = True
    Exit Sub

End Sub
 

Student1000

Board Regular
Joined
Jul 17, 2007
Messages
58
Thanks guys....i got it to work. Very cool!
 

Student1000

Board Regular
Joined
Jul 17, 2007
Messages
58
VB, excel

Yes, i had it working using double click but then it got so clumsy(hard for others to use), so then i decided to use this code instead which was previously given to me also. So i erased the previous code and insert this one in the worksheet module. (My assumption is to delete the old code & inserting this one...). But it doesnt seem to have any effect.

BTW: i will stick to this post from now on ...(sorry!)



Private Sub Worksheet_Change(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

If Target.Validation.Type = xlValidateList Then
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)

With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
 

Student1000

Board Regular
Joined
Jul 17, 2007
Messages
58
ComboBox Properties, excel

I have combo boxes in excel to allow bigger fonts. I want make the arrow beside the combobx(dropdown) to disappear when a cell loses focus. It seems that everytime i change a value in cell A2 and move down to A3, the arrow in A2 will be blocking some of the text in B2...
I want know what property or code i need to include to make the arrows disapper(after focus lost) without going back to each cell and double click it (sometimes doesnt work very well). I tried to take a picture of it but couldnt paste it here

Please, let me know if you know the solution to this. By the way i downloaded the example at http://www.contextures.com/xlDataVal10.html
and this example doesnt have this problem.
 

Student1000

Board Regular
Joined
Jul 17, 2007
Messages
58
ComboBox Properties, excel

I have combo boxes in excel to allow bigger fonts. I want make the arrow beside the combobx(dropdown) to disappear when a cell loses focus. It seems that everytime i change a value in cell A2 and move down to A3, the arrow in A2 will be blocking some of the text in B2...
I want know what property or code i need to include to make the arrows disapper(after focus lost) without going back to each cell and double click it (sometimes doesnt work very well). I tried to take a picture of it but couldnt paste it here

Please, let me know if you know the solution to this. By the way i downloaded the example at http://www.contextures.com/xlDataVal10.html
and this example doesnt have this problem.


Thanks.
 

Forum statistics

Threads
1,181,648
Messages
5,931,213
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top