Text Size in dropdown box

  • Thread starter Thread starter Randy Vieira
  • Start date Start date
R

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,
 
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
 
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,

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

'===========================
 
Back
Top