Workbook Change

Hi guys,

I want to run a moudle every time a change someting in colom "D2:D35"

I used Worksheet_Change event but it runs every time I change something in the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Range("D2:D35")

Module4.Day_Hour

ActiveWorkbook.Save

End Sub

Hi,

By design it will be called everytime.

Target is passed ByVal and references the range that was changed, so setting it to reference something else is counterproductive. Instead, check whether Target references a range of interest and then conditionally process depending on that. You can use the Application.Intersect() method to perform the check.

Hope that helps...

Thank for the idea collin, ive got the sulosion

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Insect As Long

Set isect = Application.Intersect(Target, Range("d2:d35"))

If isect Is Nothing Then
End
Else
Module4.Day_Hour
ActiveWorkbook.Save
End If

End Sub

Well done, you're nearly there!:)

But there's a typo in your code which tells me that you're not using Option Explicit. I recommend you use it going forward.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Insect As Long

Set isect = Application.Intersect(Target, Range("d2:d35"))

If isect Is Nothing Then
End
Else
Module4.Day_Hour
ActiveWorkbook.Save
End If

End Sub

ISect is being implicitly declared as a Variant data type which is why your Application.Intersect() call isn't throwing a type mismatch error with the intended Insect Long data type variable.

Also, using the End statement is generally bad practice and is unnecessary here.

Taking all this into account your code should be:


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngInsect As Range

Set rngInsect = Application.Intersect(Target, Range("d2:d35"))

If Not rngInsect Is Nothing Then
Module4.day_hour
ActiveWorkbook.Save
End If

End Sub


Hope that helps...