vb in excel - on data modified?

Question

Hello.
This is my first time here and I'm totaly not sure if I post this question in the right place (or even if this forums have a place for it) but I didn't found anything that sounds more apropriate so here goes.

I'm trying to capture the event when someone modify a cell data in an excel worksheet.
I managed so far to patch the following "macros":

---

Sub OnChange()
'
' yes Macro
' Macro recorded 01/02/2006 by David Alon
'
' Keyboard Shortcut: Ctrl+q
'
    ' total =
   
    If (ActiveSheet.Name = "test" And Cells(1, ActiveCell.Column).Value = "bool") Then
            Sheets("test").Range("A1:D100").AdvancedFilter Action:=xlFilterCopy, _
                CriteriaRange:=Sheets("yes").Range("H1:H2"), CopyToRange:=Sheets("yes").Range("A1:D100"), Unique:= _
                False
            Sheets("test").Range("A1:D23").AdvancedFilter Action:=xlFilterCopy, _
                CriteriaRange:=Sheets("no").Range("H1:H2"), CopyToRange:=Sheets("no").Range("A1:D100"), Unique:= _
                False
    End If
End Sub

   ' The Auto_Open name forces this macro to run every time
   ' the workbook containing this macro is opened.
   Sub Auto_Open()
   '  Every time a cell's value is changed,
   '  the OnChange macro runs.
      Application.OnEntry = "OnChange"
   End Sub

---

Most of this code is patched from several different sources that does different things (since I don't know vb at all) part of which are from msdn.

I managed to get it to call my macro whenever I manually write data into a cell in the column I want it to monitor.
What I couldn't do (and could find a solution in google) is to make it also run when someone copy\paste a cell into the column I want OR select a cell and "spread" it (if that's how it called, I'm translating it from hebrew) to different cells.

For example selecting cell C2, click on the tiny black square at the lower left corner of the cell and drag it untill cell C10 ==> C2-C10 contain the original value of C2.

The copy\paste is simple, select cell C2, press ctrl+c, select cell C3 press ctrl+v.

The only thing that works is when I manually type the info from C2 into C3.


I'll apretiate any input you guys have (including telling me something like "this is the wrong place, ask there...").

Also if you guys see a better way to acomplish what I want please let me know.

Basically my end goal is:

---

if a value in column "bool" sheet "test" is changed
    run advanced filter on sheet "test" and duplicate entries in sheets "yes" and "no" acording to "bool".

---

ThanX in advanced
daat99


P.S.S.
How can I do [code] tags here?

Answer 1

per the support engineer:

Solution

================

Would you please tell us where the other abort points are?

And you can try following things:

1.       Try to run the code on another computer(which also installed the windows XP SP2 and office 2000)

2.       Try to check the encoding of your code, some times, when you copy or paste the code in different editor some code may be damaged.

If it still has problems you can paste you code here, I will have a look for youJ

 

-brenda (ISV Buddy Team)