CODEWITHSUNDEEP
vbaexcel
Kiirito
Kiirito

Reputation: 31

Saving value of a Cell

I would like to save a value of a cell in Excel .

Thats the code im using :

Dim oldval As Range
Sub Macro0()

Set oldval = ActiveCell

End Sub

Sub Macro2()

Dim y As Integer
Dim x As Variant

Dim rng2 As Range
Set rng2 = ActiveCell

Dim rng As Range
Set rng = ActiveCell

If rng.Column = 8 And rng.Value <> "" Then
    'Extract the number part of string
    x = Split(rng2.Value, "_")(0)
    y = Split(rng.Value, "_")(0)

    If y < 1 Or x = "" Then Exit Sub

    If x < y Then
        MsgBox "Attenzione, si sta decrementando di stato un fornitore !"
    Else
        MsgBox "Stato cambiato con successo"

End If
End If


End Sub

I need to save the value that was in the cell before it get changed. I tried using that code :

Private Sub Worksheet_Change(ByVal Target As Range)
Call Macro1
End Sub




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call Macro0
End Sub

But it doesnt work since im using a Macro that is not located on the Workbook. I need to do a control after i have saved the old value and check if its higher than the new value that has been insert. How can I save that old value? Thanks

EDIT : @Jeeped Thats how it looks the file File

Upvotes: 0

Views: 398

Answers (2)

Matteo NNZ
Matteo NNZ

Reputation: 12645

I think I have just now fully understood your need. Here is a work-around:

In your module, add the macro1() like this

Sub macro1(ByVal oldVal As String, ByVal newVal As String)
    MsgBox "The value was " & oldVal & ", but now is " & newVal
End Sub

In your Worksheet module, add the two following events + declarations

Dim oldVal As String, newVal As String 'the top declaration allows you to use the variables even when the macro/method of the worksheet has finished to run
Private Sub Worksheet_Change(ByVal Target As Range)
    newVal = Target.Value 'this will just take the current value of the cell
    Call macro1(oldVal,newVal) 'they are both passed to your procedure
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    oldVal = Target.Value 'this will record the value of the cell everytime you are selecting it
End Sub

Here's how it works, changing the content of the cell from "buongiorno" to "buonasera":

enter image description here

and

enter image description here

Of course this is an example, but you will just need to re-adapt the workaround on your code and it should work fine. Note: I've just figured out the messagebox is showing the message in a different language than what's coded, that happened because I've made a screenshot when I was still testing the method. Sorry about that, please suggest an edit if you think this could create confusion.

Upvotes: 1

Matteo NNZ
Matteo NNZ

Reputation: 12645

You will need to declare the variable at the project level. Here's a simple example:

Dim a As String
Sub macro1()
    a = Range("A1")
End Sub
Sub macro2()
    MsgBox a
End Sub

If you run the macro1, the variable a (which is declared at the top level) will store the value of the cell Range("A1"). Then, if you run macro2 in a second moment, the value will be still there.

You just need to declare your variable oldval on the top of your project and store your old value as with oldValue = ActiveCell.Value put just before you made your changes. The variable will hold the value even after getting out of the macro, so you can use the value to compare it at later time.

Upvotes: 1

Related Questions