VBA - Do action if table changes

[cozzagiorgi]

Hi,

I am working on a (seemingly) simple macro wich should look if my "Tabelle1" is changed. If yes, it should do something. This is my macro, but I get a Runtime error 424, what am I doing wrong?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count <> 1 Then Exit Sub


    If Not Intersect(Target, ["Tabelle1"[]]) Is Nothing Then


        Application.EnableEvents = False
    Range("H2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("A2").Select
    Selection.End(xlDown).Select


End If


Application.EnableEvents = True


End Sub

Thanks for your help!

 

[My Aswer Is This]

I think your one line of code should look like this:

If Not Intersect(Target, Range("Table1")) Is Nothing Then

 

[cozzagiorgi]

I think your one line of code should look like this:

If Not Intersect(Target, Range("Table1")) Is Nothing Then

It was effectively that easy... Thank you!

A related question: how can I make this macro run in the background without seeing what is going on?

 

[My Aswer Is This]

Add this line of code to script near the top of script:

Application.ScreenUpdating = False

And this line near end of script:

Application.ScreenUpdating = True

 

[cozzagiorgi]

Add this line of code to script near the top of script:

Application.ScreenUpdating = False

And this line near end of script:

Application.ScreenUpdating = True

Great! Thank you for your fast answers!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Great! Thank you for your fast answers!

 

[cozzagiorgi]

OK, I think i got still something wrong with this code:



Private Sub Worksheet_Change(ByVal Target As Range)
'überwacht Spalte F und aktualisiert Spalte H wenn Änderungen vorgenommen werden




If Target.Count <> 1 Then Exit Sub


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


Application.EnableEvents = False

'Bildschirm nicht updaten
Application.ScreenUpdating = False
'Spalte H leeren, nach unten in der Tabelle springen, von oben bis unten alles auswählen und Spalte H mit Formel füllen
Range("H:H").ClearContents
Range("Tabelle1[[#Headers],[Projekt]]").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Select
Range(Selection, Selection.End(xlUp)).Select


Selection.FormulaR1C1 = "=IF(COUNTIF(R57C6:RC[-2],RC[-2])=1,RC[-2],"""")"


Range("Tabelle1[[#Headers],[Projekt]]").Select

'Bildschirm wieder updaten
Application.ScreenUpdating = True


End If


Application.EnableEvents = True


Call LeereZellenlöschen

End Sub

I want it to activate only if something in row F changes. But now it works with any changes on the whole sheet.

 

[My Aswer Is This]

This is now a completely different question.
Do you now have two worksheet change event script in the same sheet?

What does your second script suppose to do?

 

[cozzagiorgi]

You are right, it's a different question. I changed my sheet a little.
The only changes possible are now in range F:F all the rest of the code can be ignored for now, I think it works.
What I need to know is if this part of my code is correct to look at range F:F and do something if there are any changes.

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]
[COLOR=#333333]'überwacht Spalte F und aktualisiert Spalte H wenn Änderungen vorgenommen werden[/COLOR]




[COLOR=#333333]If Target.Count <> 1 Then Exit Sub[/COLOR]


[COLOR=#333333]If Not Intersect(Target, Range("F:F")) Is Nothing Then[/COLOR]


[COLOR=#333333]Application.EnableEvents = False[/COLOR]

 

[My Aswer Is This]

Not sure what your wanting this script to do?

And if your putting more the one of these type scripts in one sheet it gets more complicated.