I have a table of numbers and formulas for each row, column, and for the table as a whole. The table is part of a report – the output of the application. The user wants to exclude certain numbers from the calculations after reviewing them. These numbers would be outliers and would skew the results. The calculations are AVERAGE and STDEV functions. If a number is excluded, it needs to still be shown on the reports, but with a strikethrough format.
The obvious course is to modify the formula when the user has identified a cell to exclude. With formulas for every row, column, and for the whole table, that’s a pretty big job. An easier way is to change the numbers to text. Both AVERAGE and STDEV ignore text, so this would have the effect of excluding the numbers from the formulas without having to change the formulas. I started with something like this:
If .Font.Strikethrough Then
.Value = CDbl(.Value)
.Font.Strikethrough = False
Else
.Value = “‘” & .Value
.Font.Strikethrough = True
End If
End With
This is in the worksheet’s BeforeDoubleClick event. I use the strikethrough property to determine if the number has already been excluded. The user can double click the number to toggle between inclusion and exclusion. Excluded numbers have an apostrophe put in front of them (making them text) and the font is changed to strikethrough. Included numbers are changed back to a Double (using CDbl) and the strikethrough is removed.
Incidentally, not every number can be excluded. I’ve applied a particular style to those numbers that can be excluded and I limit the event like this:
A new wrinkle appeared. Now some of the numbers are actually formulas. That complicates the above code snippet a little.
If .HasFormula Then
lStart = 2
Else
lStart = 1
End If
If .Font.Strikethrough Then
.Formula = “=” & Mid(.Formula, Len(“=TEXT()”), _
Len(.Formula) – Len(“=TEXT()”,”) – Len(.NumberFormat))
.Font.Strikethrough = False
Else
.Formula = “=TEXT(“ & Mid(.Formula, lStart, Len(.Formula)) & _
“,”“” & .NumberFormat & “”“)”
.Font.Strikethrough = True
End If
End With
Instead of putting an apostrophe in front of the value to make it text, I surround it with the TEXT function. This has the added benefit of keeping the same number format applied to the text as was applied to the number. When a number is excluded (the Else part), I start with “=TEXT(“. Then I repeat the existing formula, removing the equal sign if there was one (Mid(.Formula, lStart, Len(.Formula))). The suffix to this string manipulation is the existing NumberFormat surrounded by double quotes.
When a number is included, the TEXT portion of the formula is removed. The Mid function starts at Len(“=TEXT()”), which is a verbose way of saying 7. The length of Mid is the length of the formula, minus the length the text function (including parentheses, the comma that separates the number format argument, and the quotes that surround the number format), minus the length of the numberformat.
This has the strange side effect of converting a number like 3 into a formula like =3 when it’s toggled. I can’t think of any ill effects of that, but there may be.
DK,
I did something similar for a nebulus project. The client wanted a toggle to show averages with zeros and without zeros. I took the cheap way out. When averages without zeros was required, I simply tacked a single quote (‘) to the front of each zero. This turns the number to text, thus removing it from the average calculation. To include zeros againg, I removed the single quote.
I imagine you could have done the same thing here.
Shoot, I didn’t read carfully enough. I guess did do the same thing here.
Duh.
it should be noted that if you change the value of a cell that is toggled-off (strikethrough is active), attempting to double click the cell again to toggle will cause an “Invalid procedure call or argument” error message. Also – at the end of the code, you should include “Cancel = True” to cancel the double click action after the code has executed. Lastly – consider enclosing this code in an if statement along the lines of
If .Value “” Then
so that the custom action on double click will only execute if a cell already contains a value (double clicking blank cells will not execute the code)
Ken
I’d like to work on it a bit, can you please send me your code so that I don’t start from scratch
thanks Olivier
This is a good example of where I would try to avoid using VBA, because this code makes it hard for someone else to see what’s going on (remembering that most people don’t know any VBA at all).
You can do it easily enough without VBA by setting up a second “shadow” table identical to the first, which has formulae which simply equal the values in the first table. The user deletes the unwanted cells in the shadow table, and the original table has conditional formatting which strikes through entries which don’t match the values in the shadow table. The AVERAGE & STDDEV calculations are based on the values in the shadow table. A text box explains what is happening. The original table is protected, the shadow table is not.
Interesting implementation, Dick. I would have taken a different approach since I am always leery about trying to change a cell. In this case the first twist you ran into was a formula. But, what if that was an array formula? Or, worse, an array formula that spanned multiple cells?
An alternative that might be worth considering is to use a named formula that includes/excludes cells as required. You can still use the Strikethrough to visually show cells excluded from the calculation but other than that make no changes to the cell.
The code below goes into the workbook code module. Also required are the functions from Two new range functions: Union and Subtract It’s tested with array formulas, regular formulas, and constants.
Function getName(WS As Worksheet, whatName As String) As Name
On Error Resume Next
Set getName = WS.Names(whatName)
On Error GoTo 0
If getName Is Nothing Then
Static OneTimeWarning As Boolean
If Not OneTimeWarning Then
MsgBox “Capability requires use of the named range InclCells”
OneTimeWarning = True
End If
End If
End Function
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Dim aCell As Range
For Each aCell In Target
If aCell.Style = “DoubleClickTrap” Then
Dim WS As Worksheet, aName As Name
Set WS = Sh
Set aName = getName(WS, “InclCells”)
If Not aName Is Nothing Then
If aCell.Font.Strikethrough Then
aName.RefersTo = _
“=” & Union(WS.Range(aName.RefersTo), aCell).Address
Else
aName.RefersTo = _
“=” & Subtract(WS.Range(aName.RefersTo), aCell).Address
End If
aCell.Font.Strikethrough = Not aCell.Font.Strikethrough
End If
Cancel = True
End If
Next aCell
End Sub
I’ve also done this sort of thing before. I use a shadow range like Dermot and a BeforeDoubleClick event handler. My shadow range is unprotected, but it contains 0s or 1s and is hidden. The event handler checks whether the double click occurred in a cell in the same row as the range of selectable values, and if so it toggles the state of the corresponding cell in the shadow range. It uses conditional formatting in the original range based on the state of the corresponding cell in the shadow range. Summary statistics become conditional. In my experience, it’s easier and safer to change a few summary formulas than cell contents for many individual values.
An non-VBA alternative involves putting a Forms checkbox to the left of each cell in the range of selectable values, then set each checkbox’s LinkedCell to the corresponding cell in the shadow range. Conditional formatting then becomes optional since the checkbox would provide a visual indication of which values would be included or not, and the summary formulas would still be conditional. A bit more work to set up, but much of it could be automated with macros in a different workbook, so not distributed with the report workbook.
I’ve done this with checkboxes, too. If the value is False, you can adjust the format in the cell so it’s obvious the value is excluded, via strikethrough, font color, or other conditional format. It’s much easier, and does not rely upon VBA and worksheet events.
1500.000 0.000 400 0 1900 3.2900 3.18 6042.00
800.000 0.000 0 -300 500 0.9100 0.93 465.00
1000.000 0.000 700 0 1700 1.5000 1.55 2635.00
1560.000 0.000 640 0 2200 1.1200 1.18 2596.00
0.000 0.000 800 0 800 3.1900 3.40 2720.00
Hello Dick
It is me again with some trivial problem for you but a frustration for me.
What is wrong with the above thery are suppose to be numbers but they are not and I cannot convert them?
Regards
If there was as formula I would store it as a comment and the convert the cell contents to a value.
If the user clicked again the Contents of the comment viz the formula would come back in to the cell.
If the cell has no comment the Target.comment.Text would return “Nothing” and hence would not effect the contents…
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.HasFormula Then
Target.AddComment (Target.Formula)
End If
If Application.WorksheetFunction.Count(Target) = 0 Then
Selection.Value = Selection.Value * 1
Target.Value = Target.Comment.Text
Target.Comment.Delete
Selection.Font.Strikethrough = False
Else
Selection.Value = “‘” & Selection.Value
Selection.Font.Strikethrough = True
End If
End Sub
hi,
i have a spreadsheet that extracts a trial balance from my data sheet and adds all the like expenses together to give me a total using sumif function.
what i would like to do is doubleclick on a Trial Balance expense and get a list of same items on a seperate sheet like say if i doubleclick on PURCHASES it would give me a list of all the items entered i.e list all the purchases for the year.
IS THERE A VBA FUNCTION OR MACRO THAT I CAN BUILD.
MANY THANKS