Creating "Add/Subtract" cells to modify locked cells...

Originally Posted by Nicholas

Alright, I hope this makes SOME sort of sense...

Basically what I am trying to do, is to create a locked cell, then add two cells underneath it, one which would be coded specifically for *ADDING* to the value of the locked cell, and one for *SUBTRACTING* from it.

Ideally, the way it would work, is that the user could type in an amount in the "add" cell, which would then populate into the locked cell, automatically, and immediately clearing the "add" cell. In order to subtract from the locked cell, they would have to use the "subtract" cell. Using the "add" cell again would (IDEALLY) add the NEW number they were inputting to the previous number which had already populated the locked cell.

In other words, inserting "10" into the "add" cell would then populate the locked cell with "10." - Inserting "10" into the "add" cell AGAIN would change the locked cell to "20." - The only way to subtract from the locked cell (ideally) would be to use the "subtract" cell.

If this makes any sense, and ANYONE knows of a way (complex or otherwise; though, I doubt sincerely it could possibly be otherwise), please let me know.

Thanks!!

On the worksheet tab, rightmouse and select View Code, paste this code into there

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer, iColumn As Integer
Dim sName As String
sName = ActiveSheet.Name
    If Not Intersect(Target, Range("B2:D2")) Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Application.Worksheets(sName).Protect Password:="password", Contents:=False
        iColumn = Target.Column
        If iColumn = 2 Then
            Range("C2").Value = Range("C2").Value + Range("B2").Value
            Range("B2").Value = ""
            ElseIf iColumn = 4 Then
            Range("C2").Value = Range("C2").Value - Range("D2").Value
            Range("D2").Value = ""
            End If
     End If
     Application.Worksheets(sName).Protect Password:="password", Contents:=True
     Application.ScreenUpdating = True
     Application.EnableEvents = True
End Sub

This assumes B2 = plus, C2 = locked number, D2 = minus
password = password (you will need to change both 'password' to your desired word).

hth
---

Originally Posted by Nicholas

Thank you, VERY much - that worked almost perfectly (aside from the sheet auto-locking after every cell entry), I simply removed the lock code, and put a manual lock on the sheet through the actual Excel UI. *BUT* - My next problem is how would I go about using this for multiple cells on the same sheet?

EG:

"C4 = SUM, C5 = +, E5 = -; C6 = SUM, C7 = +, E7 = -; etc., etc."

You would need to amend the

If Not Intersect(Target, Range("B2:D2")) Is Nothing Then

to

If Not Intersect(Target, Range("B2:E7")) Is Nothing Then

etc, and then setup seperate code sets for each 3-cell set.

Add

        iColumn = Target.Column
        iRow = Target.Row
        If iColumn = 2 and iRow = 2 Then

etc.


---

Originally Posted by Nicholas

Alright, I've got the multiple "+/-/=" cells working properly, as well as a "Date Entered" function working... *HOWEVER* I can't seem to figure out how to lock ALL these cells, except for the "Add to" and "Subtract from" cells! =\

I'm sure the bit of code that was posted first to auto-unlock and re-lock the cells before and after the code will work, but I'm not sure where exactly it belongs, now that there are multiple instances of the "+/-/=" cells, as well as there being the "Date Entered" auto-populating cells.

Basically, when I try to just lock the cells in the Excel UI, setting them as protected and then locking the sheet, the code has an error, because it's trying to modify cells that are locked, obviously... But how do I work around this, so the "+/-" cells stay unlocked, but EVERYTHING ELSE can be locked, and still populated with: What was entered in the "+" tab, What was entered in the "-" tab, and WHEN either tab was last modified.

Here's what the code looks like so far, lacking, obviously, the "unlocking/locking" code bits...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer, iColumn As Integer
Dim sName As String
sName = ActiveSheet.Name
    If Not Intersect(Target, Range("B5:E19")) Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        iColumn = Target.Column
        If iColumn = 2 Then
            Range("B5").Value = Range("B5").Value + Range("B12").Value
            Range("B12").Value = ""
            ElseIf iColumn = 3 Then
            Range("B5").Value = Range("B5").Value - Range("C12").Value
            Range("C12").Value = ""
            End If
        iColumn = Target.Column
        If iColumn = 2 Then
            Range("B7").Value = Range("B7").Value + Range("B14").Value
            Range("B14").Value = ""
            ElseIf iColumn = 3 Then
            Range("B7").Value = Range("B7").Value - Range("C14").Value
            Range("C14").Value = ""
            End If
        iColumn = Target.Column
        If iColumn = 2 Then
            Range("B9").Value = Range("B9").Value + Range("B16").Value
            Range("B16").Value = ""
            ElseIf iColumn = 3 Then
            Range("B9").Value = Range("B9").Value - Range("C16").Value
            Range("C16").Value = ""
            End If
        iColumn = Target.Column
        If iColumn = 4 Then
            Range("C5").Value = Range("C5").Value + Range("D12").Value
            Range("D12").Value = ""
            ElseIf iColumn = 5 Then
            Range("C5").Value = Range("C5").Value - Range("E12").Value
            Range("E12").Value = ""
            End If
        iColumn = Target.Column
        If iColumn = 4 Then
            Range("C7").Value = Range("C7").Value + Range("D14").Value
            Range("D14").Value = ""
            ElseIf iColumn = 5 Then
            Range("C7").Value = Range("C7").Value - Range("E14").Value
            Range("E14").Value = ""
            End If
        iColumn = Target.Column
        If iColumn = 4 Then
            Range("C9").Value = Range("C9").Value + Range("D16").Value
            Range("D16").Value = ""
            ElseIf iColumn = 5 Then
            Range("C9").Value = Range("C9").Value - Range("E16").Value
            Range("E16").Value = ""
            End If
     End If
     Application.ScreenUpdating = True
     Application.EnableEvents = True
If Target.Address = "$B$12" Then
Target.Offset(-7, 2) = Date
End If
If Target.Address = "$B$14" Then
Target.Offset(-7, 2) = Date
End If
If Target.Address = "$B$16" Then
Target.Offset(-7, 2) = Date
End If
If Target.Address = "$C$16" Then
Target.Offset(-7, 1) = Date
End If
If Target.Address = "$C$14" Then
Target.Offset(-7, 1) = Date
End If
If Target.Address = "$C$12" Then
Target.Offset(-7, 1) = Date
End If
If Target.Address = "$D$12" Then
Target.Offset(-7, 0) = Date
End If
If Target.Address = "$D$14" Then
Target.Offset(-7, 0) = Date
End If
If Target.Address = "$D$16" Then
Target.Offset(-7, 0) = Date
End If
If Target.Address = "$E$12" Then
Target.Offset(-7, -1) = Date
End If
If Target.Address = "$E$14" Then
Target.Offset(-7, -1) = Date
End If
If Target.Address = "$E$16" Then
Target.Offset(-7, -1) = Date
End If
End Sub

Any help would be greatly appreciated, they're imaginining, here at work, that I'm some "Excel Guru" while you all understand already that I am "Mediocre" at best.

Thanks again, in advance!

I like to keep it simple, (then I can understand it)

I used the rightmouse Format Cells on the worksheet, and removed those cells that I didn't want locked, in your case that should be every cell except the 'value' cells. Then just protect the sheet.

Where to, . . as soon as you detect a need, and re-protect just prior to exiting that, so I would unlock just before 'Application.EnableEvents = False' and re-apply just after the 'True'.

hth
---
added also I would probably go

If iColumn = 2 Then
            Range("B5").Value = Range("B5").Value + Range("B12").Value
            Range("B12").Value = ""
            Range("B7").Value = Range("B7").Value + Range("B14").Value
            Range("B14").Value = ""
            Range("B9").Value = Range("B9").Value + Range("B16").Value
            Range("B16").Value = ""
            ElseIf iColumn = 3 Then
            Range("B5").Value = Range("B5").Value - Range("C12").Value
            Range("C12").Value = ""
            Range("B7").Value = Range("B7").Value - Range("C14").Value
            Range("C14").Value = ""
            Range("B9").Value = Range("B9").Value - Range("C16").Value
            Range("C16").Value = ""
            Else If iColumn = 4 Then
            Range("C5").Value = Range("C5").Value + Range("D12").Value
            Range("D12").Value = ""
            Range("C7").Value = Range("C7").Value + Range("D14").Value
            Range("D14").Value = ""
            Range("C9").Value = Range("C9").Value + Range("D16").Value
            Range("D16").Value = ""
            ElseIf iColumn = 5 Then
            Range("C5").Value = Range("C5").Value - Range("E12").Value
            Range("E12").Value = ""
            Range("C7").Value = Range("C7").Value - Range("E14").Value
            Range("E14").Value = ""
            Range("C9").Value = Range("C9").Value - Range("E16").Value
            Range("E16").Value = ""
            End If
     End If

~~etc~~

- you're going to do the plus & minus, why bother re-testing.
you can replace the rest of that code with

offcol = 2
If Target.Address = "$C$16" or Target.Address = "$C$14" or Target.Address = "$C$12" Then
    offcol = 1
Else If Target.Address = "$D$12" or Target.Address = "$D$14" or Target.Address = "$D$16" Then
    offcol = 0
Else If Target.Address = "$E$12" or Target.Address = "$E$14" or Target.Address = "$E$16" Then
    offcol = -1
end if
Target.Offset(-7, offcol) = Date

or similar. - perhaps set offcol on Target.column = B C D or E to 2 1 0 -1 etc

hth
---