PDA

View Full Version : VBA to check if cell changed



Me00550
02-19-2020, 08:07 AM
I am trying to run a macro when user clicks button it checks if a cell value changed, if so run code to something

however my code stops on line 3 (first if statement) gives me run-time error ' 424' object required



Private Sub DONE_Click() Dim cell As Range
If Not Intersect(Target, Range("B6")) Is Nothing Then
For Each cell In Target
If cell.Value <> "" And cell.Value <> prevValue Then


'Consildates loose parts list by hiding cells containing zero value
For a = 8 To 298
If Worksheets("LoosePartsList").Cells(a, 2).Value = "0" Then
Worksheets("LoosePartsList").Rows(a).Hidden = True
End If
Next
'Saves file in current folder file is in with new file name
Dim FName As String
Dim currentpath As String
FName = Worksheets("LoosePartsList").Range("A1")
currentpath = ThisWorkbook.Path & "\" & FName
ActiveWorkbook.SaveAs FileName:=currentpath


Else
MsgBox "Please change the revision level and click ALL CALCUATIONS DONE again", , "REVISION LEVEL NOT CHANGED"
End If
Next cell
End If

Jan Karel Pieterse
02-19-2020, 08:53 AM
Target is a variable that normally is defined in e.g. the Worksheet_Change routine (it is passed as an argument to the routine). In your sample code, Target is undefined and thus empty. Therefore the For loop fails as there is nothing to loop through.