2

Hello Stack Overflow Community! I am a major Noob at VBA scripting and think I have an easy question? I have some simple code on my worksheet that calls a function every time a value is changed in a specific cell.

The Problem is, is that cell is a drop down menu and when you change the selection of the drop down to the same value it still runs the code..

To be clear, I want the code to run when the drop down changes. Just not when it "changes" to the same value. Pasting my code below. Let me know if you can help!

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$W$21" Then
        Call Financing
    End If
End Sub

I was thinking maybe add a .ignore or something maybe?

QHarr
  • 83,427
  • 12
  • 54
  • 101
ITMikeT
  • 23
  • 3
  • Duplicate of [this question](https://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba) – urdearboy Jun 30 '18 at 15:51

1 Answers1

0

Keep a static value of the previous value and compare before launching the Financing sub procedure.

Private Sub Worksheet_Change(ByVal Target As Range)
    static oldW1  as variant
    If Target.Address = "$W$21" Then
        if target.value <> oldW1 then
            oldW1 = Target.value
            Call Financing
        end if
    End If
End Sub

Static vars are 'remembered' by the sub procedure or function where they are declared. Normally, oldW1 would be 'forgotten' (and destructed) when the Worksheet_Change was completed and exited. However, with a static oldW1, the second time (and all subsequent times) the Worksheet_Change is entered it 'remembers' what the value of oldW1 was the last time it was used.

This does not mean that a static var is globally public; it is only available within the function or sub procedure where it was declared.