How to change the font size of selections in a listbox?

  • Thread starter Thread starter Charlie
  • Start date Start date
C

Charlie

I have put a listbox (drop-down selection box) into a cell and the values
are stored on another worksheet in the file. However, when the user clicks
on the drop-down arrow to show the possible selections in the listbox, the
font is tiny, and I can't find a way to change it. I'd be grateful for any
advice on how to format the listbox selections...

Many thx,


Charlie
 
Frank-

MANY thx for pointing me to that site. However, the only other thing I
would like to do is to query the worksheet for what the ActiveWindow.Zoom
was before entering the DataValidation field so that I can reset it to that
when leaving. Any way to do that?

Again, many thx for your help.


Charlie
 
Hi
try
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
static zoom_factor
If Target.Address = "$A$2" Then
zoomfactor = activewindow.zoom
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = zoomfactor
End If
End Sub
 
Frank,

That code gives me the following error:

Run-time error '1004'
Unable to set the Zoom property of the Window class

and debug highlights the line with the asterixes below.

Again, my thanks for your continued assistance.


Charlie
 
There was a zoom_factor and zoomfactor variable.

This minor correction seemed to work ok:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static zoomfactor As Variant
If IsEmpty(zoomfactor) Then
zoomfactor = ActiveWindow.Zoom
End If

If Target.Address = "$A$2" Then
zoomfactor = ActiveWindow.Zoom
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = zoomfactor
End If

End Sub
 
Dave-- thx. The problem I am having, even with yours, is that this doesn't
take into account the possibility of the user changing the zoom while
working on the sheet. So if he starts at 70, then decides to go to 90, he's
now stuck at 90 for the rest of the time (except when he's in a
DataValidation cell). What I am trying to figure out is a way to poll for
the zoom each time the cell selection changes, capturing the current zoom
(assuming NOT in a DataValidation cell), instead of staticing it at the zoom
first used upon opening the sheet. I'm a bit fuzzy on Excel code, but my
instinct would be to have some code that is global instead of limited to the
SelectionChange Sub, but I don't know if that's possible.

Also, my base code is actually the following, so that it triggers on ALL
DataValidation cells in the sheet:

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 = 70
Else
ActiveWindow.Zoom = 120
End If
Application.EnableEvents = True
End Sub

Again, many thx for your input.


Charlie
 
I had the same trouble and couldn't think of a way out of it.

Any chance you could just ask the user--or even give them a different macro that
sets the zoom factor--so you know what to use?
 
Possible-- always like to leave the user out of it as much as possible! ;)

Will see what else I can come up with, but thx again for your input.

Regards,


Charlie
 
Back
Top