Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying formulae changes formatting
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 "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 spreadsheet with formulae but also with values displayed to one d.p. and I'm currently beaten. Can anyone advise? Thanks SueJB |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying formulae changes formatting
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) = "=" 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|Sheet|Comments," & vbCrLf & _ "then choose the required print option.", vbOKOnly End Sub Cheers "SueJB" wrote in message ... 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 "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 spreadsheet with formulae but also with values displayed to one d.p. and I'm currently beaten. Can anyone advise? Thanks SueJB |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying formulae changes formatting
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 "macropod" wrote: 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) = "=" 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|Sheet|Comments," & vbCrLf & _ "then choose the required print option.", vbOKOnly End Sub Cheers "SueJB" wrote in message ... 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 "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 spreadsheet with formulae but also with values displayed to one d.p. and I'm currently beaten. Can anyone advise? Thanks SueJB |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying formulae changes formatting
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 < 254 Then .IncrementLeft -11.25 If TargetCell.Row < 1 Then .IncrementTop 8.25 Cheers "SueJB" wrote in message ... 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 "macropod" wrote: 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) = "=" 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|Sheet|Comments," & vbCrLf & _ "then choose the required print option.", vbOKOnly End Sub Cheers "SueJB" wrote in message ... 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 "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 spreadsheet with formulae but also with values displayed to one d.p. and I'm currently beaten. Can anyone advise? Thanks SueJB |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying formulae changes formatting
Aah, I see ... many thanks, I'll try this.
Best wishes SueJB "macropod" wrote: 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 < 254 Then .IncrementLeft -11.25 If TargetCell.Row < 1 Then .IncrementTop 8.25 Cheers "SueJB" wrote in message ... 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 "macropod" wrote: 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) = "=" 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|Sheet|Comments," & vbCrLf & _ "then choose the required print option.", vbOKOnly End Sub Cheers "SueJB" wrote in message ... 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 "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 spreadsheet with formulae but also with values displayed to one d.p. and I'm currently beaten. Can anyone advise? Thanks SueJB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel does not displaying formatting correctly | Excel Discussion (Misc queries) | |||
Formatting Cell Using Formulae....advise please.... | Excel Discussion (Misc queries) | |||
Nested functions in conditional formatting formulae | Excel Discussion (Misc queries) | |||
Excel 2002: I get formulae displaying in cells rather than value | Excel Worksheet Functions | |||
Formulae, conditional formatting & macro security | Excel Discussion (Misc queries) |