Update another Workbook from a diffrent workbook.

[Karlos588]

Afternoon All, I currently have a Macro as a Worksheet change, that when a cell in a set column changes it open another workbook and marks the matching number down as "Invoiced"
How ever, I now need to change this from a worksheet change into a button click. How do I go about this I've tried a few things. but nothing is working.
I need to change the Target into the actual Column, but I'm unsure how to go about this.

Below is my Macro as worksheet change.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

Dim TrackerWb As Workbook
Dim InvListWb As Workbook
Dim TrackerWs As Worksheet
Dim searchRegion As Range
Dim InterlabRef As Range

Set InvListWb = ActiveWorkbook
Set InterlabRef = Target

If Target.Column = 14 Then

    Set TrackerWb = Workbooks.Open("W:\WCL\Certification\Finance\Interlab Tracker.xlsm")
    Set TrackerWs = TrackerWb.Sheets("Invoices")

    Set searchRegion = TrackerWs.Range("K1:K2000")
    Set cell = searchRegion.Find(what:=InterlabRef, LookAt:=xlWhole, SearchFormat:=False)

    If cell Is Nothing Then
TrackerWb.Close False
Exit Sub
Else
cell.Offset(0, 2).Value = "Invoiced"
TrackerWb.Save
TrackerWb.Close
    End If


End If

Application.ScreenUpdating = True

End Sub

Thanks in Advance!

Karl

 

[jkpieterse]

Assuming you intend to have the button work on the active cell you can simply replace Target with ActiveCell. Then put the button in place and move the code to the button's associated macro.

 

[Karlos588]

I basically need the button to open the second workbook and match the Interlab Ref, any that match it needs to mark as "Invoiced"

 

[jkpieterse]

Open your VBA Editor, Insert a new Module (Insert, Module). Paste in the code below. Then go to your Excel file. From the Developer tab of the ribbon, insert a button from the Forms section (NOT ActiveX!). RIght-click the button and choose Assign Macro. Enter the macro name "UpdateTracker" (without the quotes) into the box and click OK.

Sub UpdateTracker()

Application.ScreenUpdating = False

Dim TrackerWb As Workbook
Dim InvListWb As Workbook
Dim TrackerWs As Worksheet
Dim searchRegion As Range
Dim InterlabRef As Range

Set InvListWb = ActiveWorkbook
Set InterlabRef = ActiveCell

If Activecell.Column = 14 Then

    Set TrackerWb = Workbooks.Open("W:\WCL\Certification\Finance\Interlab Tracker.xlsm")
    Set TrackerWs = TrackerWb.Sheets("Invoices")

    Set searchRegion = TrackerWs.Range("K1:K2000")
    Set cell = searchRegion.Find(what:=InterlabRef, LookAt:=xlWhole, SearchFormat:=False)

    If cell Is Nothing Then
TrackerWb.Close False
Exit Sub
Else
cell.Offset(0, 2).Value = "Invoiced"
TrackerWb.Save
TrackerWb.Close
    End If


End If

Application.ScreenUpdating = True

End Sub

 

[Karlos588]

Would this work if more than one cell had changed?

 

[jkpieterse]

No, this will just work with whatever is the active cell.