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

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
''' #####################################
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>
<http://www.ready-reports.com>


"Rick S." wrote:

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

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

On Dec 28, 4:38*pm, Rick S. <Ri...@discussions.microsoft.com> wrote:
> Is it possible to change the text size when a cell is selected? *I wouldlike
> 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

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

On 28 Dec., 22:38, Rick S. <Ri...@discussions.microsoft.com> wrote:
> Is it possible to change the text size when a cell is selected? *I wouldlike
> 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

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

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



"sebastienm" wrote:

> 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
> ''' #####################################
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
> <http://www.ready-reports.com>
>
>
> "Rick S." wrote:
>
> > 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
> >