Change Text size on focus

[Rick S.]

Is it possible to change the text size when a cell is selected? I would like
to simulate magnifying the cell contents when a cell is progmatically
selected to draw user attention to the cell.
Upon leaving that cell the font size would revert to default (its originial
format value).

Happy New Year!
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[sebastienm]

Hi,
Method1
---------
Resizing the font may (will) resize the column width and the row height, so
it may create more issues than good. ANyway, the code bellow works ok.

Method2
---------
A less invasive solution would be to have a picture of the cell next to the
selected cell that show its contents in larger font.
- select an empty cell and do a Copy Picture (in xl 2003 and prior, press
SHIFT while clicking menu Edit and the new item Copy Picture shows up) as
bitmap.
- paste the picture (menu Edit > Paste). Say its name is 'Picture 1'
- enlarge it a little bit
- now in the sheet module, use the code (uses the name of the picture):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim p As Excel.Picture
Set p = ActiveSheet.Pictures("picture 1")
p.Formula = "=" & ActiveCell.Address(False, False)
End Sub
- On book closing, you could just hide the picture.

Method1 - COde
-------------------
''' ########## IN SHEET ######################
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ResetToPrevious
SetTracker ActiveCell
End Sub

''' ########## IN MODULE ######################
Public Type Tracker
Range As Range
RowHeight As Double
ColumnWidth As Double
FontSize As Single
End Type

Public mPrevious As Tracker

Public Sub ResetToPrevious()
If Not mPrevious.Range Is Nothing Then ''' reset previous range if any
With mPrevious
.Range.Font.Size = .FontSize
.Range.ColumnWidth = .ColumnWidth
.Range.RowHeight = .RowHeight

Set .Range = Nothing
End With
End If
End Sub

Public Sub SetTracker(Target As Range, Optional Size As Long = 24)
Dim cell As Range
Set cell = Target.Cells(1)
With mPrevious
Set .Range = cell
.ColumnWidth = cell.ColumnWidth
.RowHeight = cell.RowHeight
.FontSize = Size

.Range.Font.Size = Size
End With

End Sub
''' #####################################

 

[Ken]

Rick

I am not sure what you mean by programaticlly selected, but, if you
just are using the mouse to select a cell, the following
Worksheet_SelectionChange code will do what you want. Put this in the
sheet object code:

Public c As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range(c).Font.Size = 10
c = ActiveCell.Address
ActiveCell.Font.Size = 32
End Sub

If your cell is being selected through some other VBA code, you will
need to simply put some similar statements in the existing code. If
you by "programatically" you mean using the basic Excel program, then
this should work.

Good luck.

Ken
Norfolk, Va

 

[Per Jessen]

Hi Rick

Place this code in the codesheet for Sheet1, and see what happens

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Font.Size = 10
Target.Font.Size = 20
End Sub

Happy New Year!

Per

 

[Ken]

Rick

If you definitely want the cell to revert to size 10 (or any uniform
size) when you leave it, go with Per's two liner. If you want it to
revert to whatever it was (allowing for a mix of sized in cells
throughout the sheet) you can use:

Public c As String
Public x As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Range(c).Font.Size = x
c = ActiveCell.Address
x = ActiveCell.Font.Size
ActiveCell.Font.Size = 32
End Sub

Change the 32 to whatever you want the "magnified" size to be. If you
don't like row heights and columns widths changing as a result of your
selection, just make sure to set them manually to something you like.

Ken

 

[Rick S.]

I will check these out to see which is most usefull, initial testing seems to
work for each submission.

Thanks for your help!

--
Regards

VBA.Noob.Confused
XP Pro
Office 2007