Text Size in dropdown box

[Randy Vieira]

Is there any way to control the size of the text in a list controlled
validation drop down box? I have quite a long list to shown and my text is
microscopic. I've searched the group and didn't see anything.

TIA

Randy,

 

[Bob Flanagan]

Randy, I believe it is a function of the zoom setting of the sheet. Try
setting the zoom to 100%.

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Randy Vieira]

I've tried that, and my text is more readable, however, my worksheet is then
to large to view completely. Even @ 100%, the text in the dropdown box is
smaller. I'm trying to modifiy an existing worksheet to select from a list
so I'm limited on modifying the overall page setup.

Randy,

 

[Debra Dalgleish]

If these are data validation dropdown lists, you can't change the font
size. There are a couple of workarounds here:

http://www.contextures.com/xlDataVal08.html#Larger

 

[Randy Vieira]

Debra,

I followed your advice and it seemed to work well until I tried to apply to
more than one worksheet.

Zoom in when any cell with data validation is selected

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngDV As Range
Application.EnableEvents = False
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If rngDV Is Nothing Then Exit Sub
If Intersect(Target, rngDV) Is Nothing Then
ActiveWindow.Zoom = 100
Else
ActiveWindow.Zoom = 120
End If
Application.EnableEvents = True
End Sub

The second worksheet I applied it to never returned to the 100 zoom value.
Could you tell me why and how to fix that? Also, (I don't know squat about
VBA), can I have the code read the existing zoom level and return it to that
instead of setting the active window zoom to 100?

TIA

Randy Vieira

 

[Debra Dalgleish]

Randy,

I've added more error handling to the code, which should solve the problem.

Debra

'=================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngDV As Range
Dim intZoom As Integer
Dim intZoomDV As Integer
intZoom = 100
intZoomDV = 120
Application.EnableEvents = False
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo errHandler
If rngDV Is Nothing Then GoTo errHandler
If Intersect(Target, rngDV) Is Nothing Then
With ActiveWindow
If .Zoom <> intZoom Then
.Zoom = intZoom
End If
End With
Else
With ActiveWindow
If .Zoom <> intZoomDV Then
.Zoom = intZoomDV
End If
End With
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub

'===========================