Formula if value is deleted?

[kcmuppet]

Hello everybody, I'd greatly appreciate some help

I’d ideally like to have some cells that can have a formula which can be overwritten with a manual entry, but that reverts back to the formula if the value is deleted/cleared.

E.g, in the following…

‘Product’, in column A, is a text, entered with a list validation
‘Unit cost’, in column B, is derived from A1 with a vlookup
‘Quantity’, in column C, is manually entered, and
‘Price’, in column D, is B*C

…I would really like to have it so I can either generate B and D using manual entries in A and C, or overtype them, but also have them revert back to the formula if A is deleted/cleared.

I’ve been searching around the forum here, and I wondering if there is some kind of worksheet event that could be used to trigger the reverting.

Should it be along these lines?:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$D$1" Then
    If Target.Value =0 Then
         Range("D1").Value = B1 * C1
    End If
End If
End Sub

(You can see I’m a bit clueless with coding, but hopefully there’s enough to get the gist of what I’m trying to do)

There’s a sort of similar discussion here:
http://www.mrexcel.com/board2/viewt...rder=asc&highlight=overwrite+formula&start=10
...but I don’t really understand it, since it sort of goes in a different direction.

Any help much appreciated

 

[Joe Was]

You did good and are nearly there!

Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address <> "$D$1" Then Exit Sub

If (Target.Value = 0 Or Target.Value = xlNull) Then _
Target.FormulaR1C1 = "=RC[-1]*RC[-2]"
End Sub


This code will work the 1st time you change D1 to Blank or Zero, but from then on the Event will not let you change the value to Blank or Zero, because when that happens it will allways get the formula =C1*B1, which should return a value!

So if you do want the Cell D1 to sometimes show a Blank or Zero you need to run the code from a Sub not an event!

 

[bandit_1981]

How i do mine is when the value is changed from a formula i place that formula in the cells comments. Then when they delete the value i put it back in. This allows it to work on all formula on any sheet its dynamic
Oh and change the font colors to whatever you using.

Here is what i am using i hope i didnt leave out something you need to make it work.

Place this code on each sheet:

Public OldValue As Variant
Public OldAddr As Variant
Public OldAmt As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.HasFormula = True Then
        OldValue = Target.Formula
    Else
        OldValue = ""
    End If
    OldAmt = Target.Value
    OldAddr = Target.Address

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
    
    If Selection.Cells.Count > 1 Then
        Exit Sub
    End If
    Call Override(ActiveSheet.Name, Target.Address, OldValue, OldAddr, OldAmt)

End Sub

Place this code in a module

Sub Override(ByVal strSheet As String, ByVal strTarget As String, ByVal strOldValue As String, ByVal strOldAddr As String, ByVal strOldAmt As String)
On Error Resume Next

Dim curComment As String
Dim currFormula As String
Dim str As String
Dim AdjField As String
Dim AdjCol As String

    currFormula = strOldValue
    If strOldValue <> "" Then
        If ThisWorkbook.Worksheets(strSheet).Range(strOldAddr).HasFormula = False And ThisWorkbook.Worksheets(strSheet).Range(strOldAddr).NoteText = "" Then
            If Val(ThisWorkbook.Worksheets(strSheet).Range(strTarget)) = Val(strOldAmt) Or Val(ThisWorkbook.Worksheets(strSheet).Range(strTarget)) = 0 Then
              ThisWorkbook.Worksheets(strSheet).Range(strOldAddr).Formula = currFormula
            End If
        End If
    End If
    
    If strOldAddr <> "" Then
        If ThisWorkbook.Worksheets(strSheet).Range(strTarget).HasFormula = False And currFormula <> "" And Val(ThisWorkbook.Worksheets(strSheet).Range(strTarget)) > 0 Then
            ThisWorkbook.Worksheets(strSheet).Range(strOldAddr).AddComment (currFormula)
            'changing background so they know they overrode it
            With ThisWorkbook.Worksheets(strSheet).Range(strOldAddr).Font
                .ColorIndex = 50
            End With
            AdjField = Val(Mid(strOldAddr, InStr(2, strOldAddr, "$") + 1))
            AdjCol = Val(Asc(Replace(Mid(strOldAddr, 1, InStr(2, strOldAddr, "$")), "$", ""))) - 1
            ThisWorkbook.Worksheets(strSheet).Range("$" & Chr(AdjCol) & "$" & AdjField).ClearContents
        End If
        If ThisWorkbook.Worksheets(strSheet).Range(strTarget).HasFormula = False And ThisWorkbook.Worksheets(strSheet).Range(strOldAddr).NoteText <> "" And Val(ThisWorkbook.Worksheets(strSheet).Range(strOldAddr).Value) = 0 Then
            str = ThisWorkbook.Worksheets(strSheet).Range(strTarget).NoteText
            ThisWorkbook.Worksheets(strSheet).Range(strOldAddr).Formula = str
            ThisWorkbook.Worksheets(strSheet).Range(strOldAddr).Comment.Delete
            'changing it back
            With ThisWorkbook.Worksheets(strSheet).Range(strOldAddr).Font
                .ColorIndex = 9
            End With
        End If
    End If

End Sub

 

[kcmuppet]

Thanks very much, I'll try it (tomorrow) and get back to you to let you know the outcome.

 

[bandit_1981]

If you dont want them to see the comments use this.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub

Private Sub Workbook_Open()
Application.DisplayCommentIndicator = xlNoIndicator
End Sub

 

[kcmuppet]

Good morning,

Joe, I think your code will be just what I need for column D.

This code will work the 1st time you change D1 to Blank or Zero, but from then on the Event will not let you change the value to Blank or Zero, because when that happens it will allways get the formula =C1*B1, which should return a value!

What I’ve got is an invoice sheet that gets its values transferred to a row in another sheet when a macro is run (thanks to some help from others here). The plan is for another macro / ‘New Invoice’ button to clear the sheet, including the cells we are talking about. So as it happens, keeping the formula is OK because when the values in column D are actually deleted, so will those in B & C.

The overwriting in the meantime is so that the default values can be over-ridden – i.e. the unit costs can be changed in the cell, or the unit cost can be omitted/deleted and the price entered manually.

So for column B, it looks as to be more tricky since B is generated with a lookup from column A. When I tried applying the code 'as is' to a cell with a vlookup it seems to get stuck in a loop, so I need to understand it some more…

If Target.Address<> "$D$1" Then Exit Sub

Presumably, this bit just says don’t apply the code to anything other than cell D1? So, if I wanted to extend the code to apply to the ranges in the example below I would have to change “$D$1” to “B2:B5,D2:D5”? Do I also have to change Target.Address to Target.Range (bear with me, I told you I’m a coding dunce)

If (Target.Value = 0 Or Target.Value = xlNull) Then _

Presumably, this bit just says if the cell value is zero or empty, then do the next line. What’s the significance of the null thing by the way (searching xlNull in VBA help doesn’t come up with anything)?

Target.FormulaR1C1 = "=RC[-1]*RC[-2]"

This bit must be the heart of it, but I don’t think I really understand it. It seems to be referring to the formula in Row 1 Column 1. Why would that be? And then I don’t understand the rest.

In case it helps, here’s the big picture:

code test2.xls
	A	B	C	D	E	F	G
1	Type	Unitscost	Quantity	Price		RateLookuptable:
2	Item4	700	2	1400		Item1	1500
3	Item2	650	2	1300		Item2	650
4	Item3	550	2	1100		Item3	550
5	Item1	1500	2	3000		Item4	700
Sheet2

Sorry for the long-winded email!

 

[kcmuppet]

Hello bandit_1981,

As you'll see from the last post, my coding competence is very low. I tried a simple cut & paste of your code into a new workbook, with just this in it:

code test.xls
	A	B	C	D
1	Type	Unitscost	Quantity	Price
2	1	5	5	25.00
Sheet1

...but something I didn't expect happens:
When I change D2 manually it does indeed keep the value and store the old function in comment (ingenious), but it also deletes the contents of C2. Similarly, If I manually change B2, A2 is deleted. I'm guessing it's something to do with the AdjField/AdjCol thing(?), but I'm not proficient enough to work out most of what's going on in the code. Is there something I would need to change to stop it doing that?

 

[bandit_1981]

Yeah i forgot to take this part out. Delete this and it should be fine

            AdjField = Val(Mid(strOldAddr, InStr(2, strOldAddr, "$") + 1))
            AdjCol = Val(Asc(Replace(Mid(strOldAddr, 1, InStr(2, strOldAddr, "$")), "$", ""))) - 1
            ThisWorkbook.Worksheets(strSheet).Range("$" & Chr(AdjCol) & "$" & AdjField).ClearContents

 

[kcmuppet]

Yeah i forgot to take this part out. Delete this and it should be fine

Indeed it does, thankyou. What would I need to change to limit it to say just B2:B5 and D2:D5?

Thanks

 

[bandit_1981]

If you only want that code to run on those cells you might try using target.addess.

IE:
if target.address = "B2" then
override code here
end if

Cant say if that will work but it should. I wrote teh code to allow all forumlas on my sheet to be overrode.