Jagwarman
asked on
Where do I find the Trigger in a macro
I am using the below Macro provided by Gowflow whic automatically gets a file and updates it. However as there are 3 fields it updates the file 3 times and I only need the one update once C5, H5 and J5 have been entered. So it only needs to get the file and enter the details after J5. I am not sure where the 'trigger' is in the macro to do this so would appreciate help.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5")) Is Nothing Or Not Intersect(Target, Range("H5")) Is Nothing Or Not Intersect(Target, Range("J5")) Is Nothing Then
If Range("C5") <> "" And Range("H5") <> "" Then CreateLog ActiveSheet
ElseIf Not Intersect(Target, Range("P3")) Is Nothing Or Not Intersect(Target, Range("Q3")) Is Nothing Then
If Range("P3") <> "" And Range("Q3") <> "" Then UpdateLog ActiveSheet
End If
End Sub
Sub CreateLog(WS As Worksheet)
Dim MaxRow As Long
Dim oApp As Object
Dim WB As Workbook
Dim WSLog As Worksheet
Dim sFileName As String
Set oApp = CreateObject("Excel.application")
sFileName = ActiveWorkbook.Path & "\Sign-Off-Log.xlsx"
Set WB = oApp.Workbooks.Open(sFileName)
Set WSLog = WB.ActiveSheet
MaxRow = WSLog.Range("A" & WSLog.Rows.Count).End(xlUp).Row + 1
WSLog.Range("A" & MaxRow) = Now
WSLog.Range("B" & MaxRow) = WS.Range("C5")
WSLog.Range("C" & MaxRow) = WS.Range("H5")
Application.DisplayAlerts = False
WB.Close savechanges:=True
Application.DisplayAlerts = True
Set WB = Nothing
Set WSLog = Nothing
oApp.Quit
Set oApp = Nothing
End Sub
Sub UpdateLog(WS As Worksheet)
Dim MaxRow As Long
Dim oApp As Object
Dim WB As Workbook
Dim WSLog As Worksheet
Dim sFileName As String
Dim cCell As Range
Set oApp = CreateObject("Excel.application")
sFileName = ActiveWorkbook.Path & "\Sign-Off-Log.xlsx"
Set WB = oApp.Workbooks.Open(sFileName)
Set WSLog = WB.ActiveSheet
Set cCell = WSLog.Range("B:B").Find(what:=WS.Range("C5"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not cCell Is Nothing Then
WSLog.Range("D" & cCell.Row) = WS.Range("P3")
WSLog.Range("E" & cCell.Row) = WS.Range("Q3")
End If
Application.DisplayAlerts = False
WB.Close savechanges:=True
Application.DisplayAlerts = True
Set WB = Nothing
Set WSLog = Nothing
oApp.Quit
Set oApp = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
I would recommend that you use some other repository format for your log. Opening a workbook is relatively expensive.