software

Hi all,

Can anyone help with a strange quirk? I have a spreadsheet with a series of
numbers which have to be formatted to 1 decimal place. These show fine in
quot;normalquot; 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 spreadsheet with formulae but also with
values displayed to one d.p. and I'm currently beaten.

Can anyone advise?

Thanks
SueJB

Hi Sue,

The following macro adds the formulae to the comments for each selected
cell, or even the whole worksheet, 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 worksheet 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 worksheet is selected, limit action to the used range.
If Selection.Address = Cells.Address Then
Set CommentRange = Range(ActiveSheet.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) = quot;=quot; 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 lt; 254 Then .IncrementLeft -11.25
If TargetCell.Row lt;gt; 1 Then .IncrementTop 8.25
End With
End If
End With
Next
MsgBox quot; To print the comments, choosequot; amp; vbCrLf amp; _
quot; File|Page Setup|Sheet|Comments,quot; amp; vbCrLf amp; _
quot;then choose the required print option.quot;, vbOKOnly
End Sub

Cheersquot;SueJBquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; Can anyone help with a strange quirk? I have a spreadsheet with a series
of
gt; numbers which have to be formatted to 1 decimal place. These show fine in
gt; quot;normalquot; mode.
gt;
gt; If I display formulae, some of the cells not containing formulae lose
their
gt; formatting - numbers with a decimal part are still shown to one place (eg
gt; 1.6) but those without lose the decimal (eg 1 not 1.0). All attempts to
gt; change the formatting fail.
gt;
gt; I need to be able to print the spreadsheet with formulae but also with
gt; values displayed to one d.p. and I'm currently beaten.
gt;
gt; Can anyone advise?
gt;
gt; Thanks
gt; SueJB
Hi macropod

Many thanks for replying. This is certainly an interesting way of resolving
the problem, but unfortunately the spreadsheet 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

quot;macropodquot; wrote:

gt; Hi Sue,
gt;
gt; The following macro adds the formulae to the comments for each selected
gt; cell, or even the whole worksheet, and displays the comments in an
gt; appropriately-sized comment box. This should allow you to both display the
gt; formulae and their results. Depending on what you're trying to achieve, and
gt; how crowded your worksheet is, you may still need to do a bit of
gt; reformatting, and maybe get the macro the position the comments differently,
gt; but showing/hiding the formulae will then be as easy as toggling the comment
gt; display on/off.
gt;
gt; Sub AddFormulaToComment()
gt; Dim CommentRange As Range, TargetCell As Range
gt; 'skip over errors caused by trying to delete comments in cells with no
gt; comments
gt; On Error Resume Next
gt; 'If the whole worksheet is selected, limit action to the used range.
gt; If Selection.Address = Cells.Address Then
gt; Set CommentRange = Range(ActiveSheet.UsedRange.Address)
gt; Else
gt; Set CommentRange = Range(Selection.Address)
gt; End If
gt; 'If the cell contains a formula, make it a comment.
gt; For Each TargetCell In CommentRange
gt; With TargetCell
gt; 'check whether the cell has a formula
gt; If Left(.Formula, 1) = quot;=quot; Then
gt; 'delete any existing comment
gt; .Comment.Delete
gt; 'add a new comment
gt; .AddComment
gt; 'copy the formula into the comment box
gt; .Comment.Text Text:=.Formula
gt; 'display the comment
gt; .Comment.Visible = True
gt; With .Comment.Shape
gt; 'automatically resizes the comment
gt; .TextFrame.AutoSize = True
gt; 'position the comment adjacent to its cell
gt; If TargetCell.Column lt; 254 Then .IncrementLeft -11.25
gt; If TargetCell.Row lt;gt; 1 Then .IncrementTop 8.25
gt; End With
gt; End If
gt; End With
gt; Next
gt; MsgBox quot; To print the comments, choosequot; amp; vbCrLf amp; _
gt; quot; File|Page Setup|Sheet|Comments,quot; amp; vbCrLf amp; _
gt; quot;then choose the required print option.quot;, vbOKOnly
gt; End Sub
gt;
gt; Cheers
gt;
gt;
gt; quot;SueJBquot; gt; wrote in message
gt; ...
gt; gt; Hi all,
gt; gt;
gt; gt; Can anyone help with a strange quirk? I have a spreadsheet with a series
gt; of
gt; gt; numbers which have to be formatted to 1 decimal place. These show fine in
gt; gt; quot;normalquot; mode.
gt; gt;
gt; gt; If I display formulae, some of the cells not containing formulae lose
gt; their
gt; gt; formatting - numbers with a decimal part are still shown to one place (eg
gt; gt; 1.6) but those without lose the decimal (eg 1 not 1.0). All attempts to
gt; gt; change the formatting fail.
gt; gt;
gt; gt; I need to be able to print the spreadsheet with formulae but also with
gt; gt; values displayed to one d.p. and I'm currently beaten.
gt; gt;
gt; gt; Can anyone advise?
gt; gt;
gt; gt; Thanks
gt; gt; SueJB
gt;
gt;
gt;

Hi Sue,

When Excel's displaying formulae, it can't display both the formula and
result for a given cell (at least with Excel 2000 and earlier). Any cells
with values are simply displayed as per the raw data (eg dates get converted
to numbers, decimal values get truncated at the last non-zero value, etc.).

With the macro I posted, you can display both the formula and it's result,
without compromising any of the value formatting. If you want to have both
the formula and its result appear in the same cell, you could increase the
row height, and re-position the comment in code to put it within the cell
and above/below the value, or you could increase the column width and put
the formula beside the result. If you don't want the formula's result to
show, re-position the comment in code to put it over the result.
repositioning the comment is managed by changing the values in the lines:
If TargetCell.Column lt; 254 Then .IncrementLeft -11.25
If TargetCell.Row lt;gt; 1 Then .IncrementTop 8.25

Cheersquot;SueJBquot; gt; wrote in message
...
gt; Hi macropod
gt;
gt; Many thanks for replying. This is certainly an interesting way of
resolving
gt; the problem, but unfortunately the spreadsheet must be printed with the
gt; formulae in the relevant cells and the values in the non-formula cells
gt; showing to one decimal place.
gt;
gt; Thanks for your suggestion, though, much appreciated.
gt;
gt; SueJB
gt;
gt; quot;macropodquot; wrote:
gt;
gt; gt; Hi Sue,
gt; gt;
gt; gt; The following macro adds the formulae to the comments for each selected
gt; gt; cell, or even the whole worksheet, and displays the comments in an
gt; gt; appropriately-sized comment box. This should allow you to both display
the
gt; gt; formulae and their results. Depending on what you're trying to achieve,
and
gt; gt; how crowded your worksheet is, you may still need to do a bit of
gt; gt; reformatting, and maybe get the macro the position the comments
differently,
gt; gt; but showing/hiding the formulae will then be as easy as toggling the
comment
gt; gt; display on/off.
gt; gt;
gt; gt; Sub AddFormulaToComment()
gt; gt; Dim CommentRange As Range, TargetCell As Range
gt; gt; 'skip over errors caused by trying to delete comments in cells with no
gt; gt; comments
gt; gt; On Error Resume Next
gt; gt; 'If the whole worksheet is selected, limit action to the used range.
gt; gt; If Selection.Address = Cells.Address Then
gt; gt; Set CommentRange = Range(ActiveSheet.UsedRange.Address)
gt; gt; Else
gt; gt; Set CommentRange = Range(Selection.Address)
gt; gt; End If
gt; gt; 'If the cell contains a formula, make it a comment.
gt; gt; For Each TargetCell In CommentRange
gt; gt; With TargetCell
gt; gt; 'check whether the cell has a formula
gt; gt; If Left(.Formula, 1) = quot;=quot; Then
gt; gt; 'delete any existing comment
gt; gt; .Comment.Delete
gt; gt; 'add a new comment
gt; gt; .AddComment
gt; gt; 'copy the formula into the comment box
gt; gt; .Comment.Text Text:=.Formula
gt; gt; 'display the comment
gt; gt; .Comment.Visible = True
gt; gt; With .Comment.Shape
gt; gt; 'automatically resizes the comment
gt; gt; .TextFrame.AutoSize = True
gt; gt; 'position the comment adjacent to its cell
gt; gt; If TargetCell.Column lt; 254 Then .IncrementLeft -11.25
gt; gt; If TargetCell.Row lt;gt; 1 Then .IncrementTop 8.25
gt; gt; End With
gt; gt; End If
gt; gt; End With
gt; gt; Next
gt; gt; MsgBox quot; To print the comments, choosequot; amp; vbCrLf amp; _
gt; gt; quot; File|Page Setup|Sheet|Comments,quot; amp; vbCrLf amp; _
gt; gt; quot;then choose the required print option.quot;, vbOKOnly
gt; gt; End Sub
gt; gt;
gt; gt; Cheers
gt; gt;
gt; gt;
gt; gt; quot;SueJBquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi all,
gt; gt; gt;
gt; gt; gt; Can anyone help with a strange quirk? I have a spreadsheet with a
series
gt; gt; of
gt; gt; gt; numbers which have to be formatted to 1 decimal place. These show
fine in
gt; gt; gt; quot;normalquot; mode.
gt; gt; gt;
gt; gt; gt; If I display formulae, some of the cells not containing formulae lose
gt; gt; their
gt; gt; gt; formatting - numbers with a decimal part are still shown to one place
(eg
gt; gt; gt; 1.6) but those without lose the decimal (eg 1 not 1.0). All attempts
to
gt; gt; gt; change the formatting fail.
gt; gt; gt;
gt; gt; gt; I need to be able to print the spreadsheet with formulae but also with
gt; gt; gt; values displayed to one d.p. and I'm currently beaten.
gt; gt; gt;
gt; gt; gt; Can anyone advise?
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt; SueJB
gt; gt;
gt; gt;
gt; gt;
Aah, I see ... many thanks, I'll try this.

Best wishes
SueJB

quot;macropodquot; wrote:

gt; Hi Sue,
gt;
gt; When Excel's displaying formulae, it can't display both the formula and
gt; result for a given cell (at least with Excel 2000 and earlier). Any cells
gt; with values are simply displayed as per the raw data (eg dates get converted
gt; to numbers, decimal values get truncated at the last non-zero value, etc.).
gt;
gt; With the macro I posted, you can display both the formula and it's result,
gt; without compromising any of the value formatting. If you want to have both
gt; the formula and its result appear in the same cell, you could increase the
gt; row height, and re-position the comment in code to put it within the cell
gt; and above/below the value, or you could increase the column width and put
gt; the formula beside the result. If you don't want the formula's result to
gt; show, re-position the comment in code to put it over the result.
gt; repositioning the comment is managed by changing the values in the lines:
gt; If TargetCell.Column lt; 254 Then .IncrementLeft -11.25
gt; If TargetCell.Row lt;gt; 1 Then .IncrementTop 8.25
gt;
gt; Cheers
gt;
gt;
gt; quot;SueJBquot; gt; wrote in message
gt; ...
gt; gt; Hi macropod
gt; gt;
gt; gt; Many thanks for replying. This is certainly an interesting way of
gt; resolving
gt; gt; the problem, but unfortunately the spreadsheet must be printed with the
gt; gt; formulae in the relevant cells and the values in the non-formula cells
gt; gt; showing to one decimal place.
gt; gt;
gt; gt; Thanks for your suggestion, though, much appreciated.
gt; gt;
gt; gt; SueJB
gt; gt;
gt; gt; quot;macropodquot; wrote:
gt; gt;
gt; gt; gt; Hi Sue,
gt; gt; gt;
gt; gt; gt; The following macro adds the formulae to the comments for each selected
gt; gt; gt; cell, or even the whole worksheet, and displays the comments in an
gt; gt; gt; appropriately-sized comment box. This should allow you to both display
gt; the
gt; gt; gt; formulae and their results. Depending on what you're trying to achieve,
gt; and
gt; gt; gt; how crowded your worksheet is, you may still need to do a bit of
gt; gt; gt; reformatting, and maybe get the macro the position the comments
gt; differently,
gt; gt; gt; but showing/hiding the formulae will then be as easy as toggling the
gt; comment
gt; gt; gt; display on/off.
gt; gt; gt;
gt; gt; gt; Sub AddFormulaToComment()
gt; gt; gt; Dim CommentRange As Range, TargetCell As Range
gt; gt; gt; 'skip over errors caused by trying to delete comments in cells with no
gt; gt; gt; comments
gt; gt; gt; On Error Resume Next
gt; gt; gt; 'If the whole worksheet is selected, limit action to the used range.
gt; gt; gt; If Selection.Address = Cells.Address Then
gt; gt; gt; Set CommentRange = Range(ActiveSheet.UsedRange.Address)
gt; gt; gt; Else
gt; gt; gt; Set CommentRange = Range(Selection.Address)
gt; gt; gt; End If
gt; gt; gt; 'If the cell contains a formula, make it a comment.
gt; gt; gt; For Each TargetCell In CommentRange
gt; gt; gt; With TargetCell
gt; gt; gt; 'check whether the cell has a formula
gt; gt; gt; If Left(.Formula, 1) = quot;=quot; Then
gt; gt; gt; 'delete any existing comment
gt; gt; gt; .Comment.Delete
gt; gt; gt; 'add a new comment
gt; gt; gt; .AddComment
gt; gt; gt; 'copy the formula into the comment box
gt; gt; gt; .Comment.Text Text:=.Formula
gt; gt; gt; 'display the comment
gt; gt; gt; .Comment.Visible = True
gt; gt; gt; With .Comment.Shape
gt; gt; gt; 'automatically resizes the comment
gt; gt; gt; .TextFrame.AutoSize = True
gt; gt; gt; 'position the comment adjacent to its cell
gt; gt; gt; If TargetCell.Column lt; 254 Then .IncrementLeft -11.25
gt; gt; gt; If TargetCell.Row lt;gt; 1 Then .IncrementTop 8.25
gt; gt; gt; End With
gt; gt; gt; End If
gt; gt; gt; End With
gt; gt; gt; Next
gt; gt; gt; MsgBox quot; To print the comments, choosequot; amp; vbCrLf amp; _
gt; gt; gt; quot; File|Page Setup|Sheet|Comments,quot; amp; vbCrLf amp; _
gt; gt; gt; quot;then choose the required print option.quot;, vbOKOnly
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; Cheers
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;SueJBquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Hi all,
gt; gt; gt; gt;
gt; gt; gt; gt; Can anyone help with a strange quirk? I have a spreadsheet with a
gt; series
gt; gt; gt; of
gt; gt; gt; gt; numbers which have to be formatted to 1 decimal place. These show
gt; fine in
gt; gt; gt; gt; quot;normalquot; mode.
gt; gt; gt; gt;
gt; gt; gt; gt; If I display formulae, some of the cells not containing formulae lose
gt; gt; gt; their
gt; gt; gt; gt; formatting - numbers with a decimal part are still shown to one place
gt; (eg
gt; gt; gt; gt; 1.6) but those without lose the decimal (eg 1 not 1.0). All attempts
gt; to
gt; gt; gt; gt; change the formatting fail.
gt; gt; gt; gt;
gt; gt; gt; gt; I need to be able to print the spreadsheet with formulae but also with
gt; gt; gt; gt; values displayed to one d.p. and I'm currently beaten.
gt; gt; gt; gt;
gt; gt; gt; gt; Can anyone advise?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt; SueJB
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;