Re: Displaying formulae changes formatting

Hi macropod

Many thanks for replying. This is certainly an interesting way of resolving
the problem, but unfortunately the spread*** must be printed with the
formulae in the relevant cells and the values in the non-formula cells
showing to one decimal place.

Thanks for your suggestion, though, much appreciated.

SueJB

"macropod" wrote:

Hi Sue,

The following macro adds the formulae to the comments for each selected
cell, or even the whole work***, and displays the comments in an
appropriately-sized comment box. This should allow you to both display the
formulae and their results. Depending on what you're trying to achieve, and
how crowded your work*** is, you may still need to do a bit of
reformatting, and maybe get the macro the position the comments differently,
but showing/hiding the formulae will then be as easy as toggling the comment
display on/off.

Sub AddFormulaToComment()
Dim CommentRange As Range, TargetCell As Range
'skip over errors caused by trying to delete comments in cells with no
comments
On Error Resume Next
'If the whole work*** is selected, limit action to the used range.
If Selection.Address = Cells.Address Then
Set CommentRange = Range(Active***.UsedRange.Address)
Else
Set CommentRange = Range(Selection.Address)
End If
'If the cell contains a formula, make it a comment.
For Each TargetCell In CommentRange
With TargetCell
'check whether the cell has a formula
If Left(.Formula, 1) = "=" Then
'delete any existing comment
.Comment.Delete
'add a new comment
.AddComment
'copy the formula into the comment box
.Comment.Text Text:=.Formula
'display the comment
.Comment.Visible = True
With .Comment.Shape
'automatically resizes the comment
.TextFrame.AutoSize = True
'position the comment adjacent to its cell
If TargetCell.Column < 254 Then .IncrementLeft -11.25
If TargetCell.Row <> 1 Then .IncrementTop 8.25
End With
End If
End With
Next
MsgBox " To print the comments, choose" & vbCrLf & _
" File|Page Setup|***|Comments," & vbCrLf & _
"then choose the required print option.", vbOKOnly
End Sub

Cheers


"SueJB" <SueJB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CFC50C0D-D9DB-425B-9BEB-818C5A5E5E67@xxxxxxxxxxxxxxxx
Hi all,

Can anyone help with a strange quirk? I have a spread*** with a series
of
numbers which have to be formatted to 1 decimal place. These show fine in
"normal" mode.

If I display formulae, some of the cells not containing formulae lose
their
formatting - numbers with a decimal part are still shown to one place (eg
1.6) but those without lose the decimal (eg 1 not 1.0). All attempts to
change the formatting fail.

I need to be able to print the spread*** with formulae but also with
values displayed to one d.p. and I'm currently beaten.

Can anyone advise?

Thanks
SueJB



.