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

 

[kcmuppet]

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...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!

Joe, I'm not sure what you mean by this. When I ran your code in an otherwise empty workbook, it all worked just fine. However, when I tried to add it to a sheet which already had some code on it, it didnt work. Is that what you mean? The sheet already has this code on it (which swaps a picture, depending on a lookup/cell validation):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$B$45" Then Range("L12").Value = Target.Value
End Sub
Private Sub Worksheet_Calculate()
    Dim oPic As Picture
       Me.Pictures.Visible = False
       With Range("K1")
           For Each oPic In Me.Pictures
               If oPic.Name = .Text Then
                   oPic.Visible = True
                   oPic.Top = .Top
                   oPic.Left = .Left
                   Exit For
               End If
           Next oPic
        End With
 End Sub

I tried adding it at the bottom, and also just inserting it after the first If statement, which I guess didnt work because the the two range conditions would conflict. However, it didn't "not let you change the value", it just didnt return to the formula, once the value was deleted or returned to zero.

(bandit, I've got your method working, thankyou. But I want to see if it can be done without using comments).

 

[bandit_1981]

Yeah i can understand that. I didnt want to use comments but i never came up with or found a way to accomplish the task without it.

 

[kcmuppet]

Hello again Bandit,

I'm struggling with this:

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.

Should this be in the code on the sheet or the module?

I've tried both, but I can't be getting the placement exactly right since when I do it, I can only get it to either protect all the cells or none.

 

[bandit_1981]

I would put the code in the worksshet selection change and worksheet change event.

 

[kcmuppet]

I would put the code in the worksshet selection change and worksheet change event.

Bear with me on this...
Putting it straight after the 'Private sub' and closing it just before the 'End Sub' doesnt seem to work

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "D21:D39,L21:L39" Then
    If Target.HasFormula = True Then
        OldValue = Target.Formula
    Else
        OldValue = ""
    End If
    OldAmt = Target.Value
    OldAddr = Target.Address
 
On Error Resume Next
        If Selection.Cells.Count > 1 Then
        Exit Sub
        End If
     Call Override(ActiveSheet.Name, Target.Address, OldValue, OldAddr, OldAmt)
End If
End Sub

What am I doing wrong?

 

[bandit_1981]

I would say it is the way you are using target.adress it doesnt return a value in the way you are trying to compare it. If you walk through the code you will see the value you get as you tab through the cells. It will return that cells address. IE D25. It wont konw that address is in the range you are telling it. You could use it like this

target.address = "D21" or target.address = "D22" etc.

 

[kcmuppet]

You could use it like this

target.address = "D21" or target.address = "D22" etc.

I'm not having much luck with that. I think maybe I'll need to use an intersect. Maybe something like this:

If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
    Exit Sub
End If

...I'll experiment & report back.

In the meantime, does your code disable undo in your whole workbook, by any chance?

 

[bandit_1981]

I dont konw anything about intersect but good luck and no it doesnt disable my undo. Do you have your sheets protected?

 

[kcmuppet]

Do you have your sheets protected?

They're not protected, though when I've finished building them, my intention is to protect some of them. Can the code cope with that?

Can the code in the module part go onto just the one sheet I want it to apply to? i.e be on the sheet instead of in a module?

Or can I change this...

ThisWorkbook.Worksheets(strSheet)

...to reflect just one named sheet?

 

[bandit_1981]

You can do either way it should work fine