making the font bigger in the validation list tool drop-down

JDM-LTCS

New Member
Joined
Sep 7, 2006
Messages
40
I don't suppose there is a way to make the font used in the validation tool when selecting "list" that the items in the drop-down list?

I'm on a spreadsheet using TW Cent size 12 and the font in the drop-down list is micro compared to that. =/



Thanks in advance!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Found a workaround. The short answer is you can't.

But these 3 methods will work. I chose method #2.

------------------------------------------------------------------
http://www.contextures.com/xlDataVal08.html
------------------------------------------------------------------

Data Validation Font Size and List Length

The font size in a data validation list can't be changed, nor can its default list length, which has a maximum of eight rows.

If you reduce the zoom setting on a worksheet, it can be almost impossible to read the items in the dropdown list, as in the example at right.

One workaround is to use programming, and a combo box from the Control Toolbox, to overlay the cell with data validation. If the user double-clicks on a data validation cell, the combobox appears, and they can choose from it. There are instructions here.






Make the Dropdown List Appear Larger

In a Data Validation dropdown list, you can't change the font or font size.

To make the text appear larger, you can use an event procedure (three examples are shown below) to increase the zoom setting when the cell is selected. (Note: this can be a bit jumpy)

Or, you can use code to display a combobox, as described in the previous section.

Zoom in when specific cell is selected

If cell A2 has a data validation list, the following code will change the zoom setting to 120% when that cell is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$2" Then
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = 100
End If
End Sub


To add this code to the worksheet:

Right-click on the sheet tab, and choose View Code.
Copy the code, and paste it onto the code module.
Change the cell reference from $A$2 to match your worksheet.












Zoom in when specific cells are selected

If several cells have a data validation list, the following code will change the zoom setting to 120% when any of those cells are selected. In this example, cells A1, B3 and D9 have data validation.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1,B3,D9")) Is Nothing Then
ActiveWindow.Zoom = 100
Else
ActiveWindow.Zoom = 120
End If
End Sub









Zoom in when any cell with a data validation list is selected

The following code will change the zoom setting to 120% when any cell with a data validation list is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lZoom As Long
Dim lZoomDV As Long
Dim lDVType As Long
lZoom = 100
lZoomDV = 120
lDVType = 0

Application.EnableEvents = False
On Error Resume Next
lDVType = Target.Validation.Type

On Error GoTo errHandler
If lDVType <> 3 Then
With ActiveWindow
If .Zoom <> lZoom Then
.Zoom = lZoom
End If
End With
Else
With ActiveWindow
If .Zoom <> lZoomDV Then
.Zoom = lZoomDV
End If
End With
End If

exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub
 
Upvote 0
yea i asked this question awhile ago as well. I couldn't find anything unless you do some heavy duty programming.
 
Upvote 0
The above wasn't heavy duty at all. Just a simple copy and paste and then tweak the range to be the cells of the drop-down's.... nothing could be simpler. :)
 
Upvote 0
THanks, this did the trick. JUMPY is exaclty how is should be described.


Is there anyway to make the dropdown list narrower... I have 41 items in my list but instead of 8, could it be forced to only show 3 and thus give a large font without the JUMP?


I have another list that has only 2 items and they appear at a decent size.



BTW if 100 is not the zoom level you want, and your selection perhaps fits with the zoom at 56% then here is how I did it

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("G5,G6")) Is Nothing Then
ActiveWindow.Zoom = 56
Else
ActiveWindow.Zoom = 120
End If
End Sub
 
Upvote 0
the code to zoom when the validation cell is selected is great.

is it possible to zoom when the arrow is selected rather than the cell and is is possibe to put the zoom level back towhat it was rather than a specified zoom level?
 
Upvote 0
One workaround that doesn't require any coding that may work for you: Rather than working in a regular size font that you have to reduce to see, try changing the entire table to a much smaller font, that you don't have to zoom on. Then the drop-downs will appear bigger.
 
Upvote 0
The above wasn't heavy duty at all. Just a simple copy and paste and then tweak the range to be the cells of the drop-down's.... nothing could be simpler. :)

Paste where? Tweak the range how? And, what's a range?

Let's not forget that everyone who uses this forum is not a programmer with familiarity let alone comfort in working with code.

It's quite frustrating, actually, when programmers speak programming and think everyone should understand it like plain language. Coding is a specialized language that not all are born fluent in.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top