Worksheet_Change Event for Visible Changes Only

[Travis]

When using a worksheet_change event-
Simply activating the target cell fires the code, even if nothing changes.
Can the code below me modified to only fire when there is a visible change.
so if A1 was 15 and I key into A1 and retype 15...then nothing happens.
(ie. can you test to see if the new value = the old value)

This is in Sheet1

Sub worksheet_change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:B2")) Is Nothing Then
    Range("C" & Target.Row) = "test"
    Else
    'Nothing to Do
    End If
End Sub

 

[TommyGun]

I'm sure there is a better method than this (this code limits itself to single cell changes), but at least this is a work-around for what you are seeing...

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Excel.Range)
    <SPAN style="color:#00007F">Dim</SPAN> tmp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> chk <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>

    <SPAN style="color:#00007F">If</SPAN> Target.Cells = 1 <SPAN style="color:#00007F">Then</SPAN>
        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>

        tmp = Target.Value
            Application.Undo
        chk = Target.Value

        Target = tmp

        <SPAN style="color:#00007F">If</SPAN> tmp = chk <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#007F00">'do nothing</SPAN>
        <SPAN style="color:#00007F">Else</SPAN>
            [C1] = "test"
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Travis]

Thanks Tommy...thinking outside the box

one issue though

If I put a formula in the cell, this wipes it out because you set it back to the value. I was able to get passed this with

... tgt = Target.Formula
    tmp = Target.Value
    Application.Undo
    chk = Target.Value
    Target = tgt
    If tmp = chk Then ...

The new problem found in testing, is if I have a bad formual that returns a #Value or #N/A (ie =1+abc) then I get an error on the chk=Target.value.
which makes sense. So I added an On Error Resume Next, which bypasses that but dosnt update [C]

Any way around this?

 

[Travis]

actually I think Tommy's way will always work if I just change .value to .formula

Anybody see a potential failure with this

 

[TommyGun]

I'm not sure of any issues with that. Like I said, the code was just a framework to get you started. Good job on figuring out the holes!

 

[Travis]

Thanks for you help!