I am using a listbox command to vlookup

[Darren Smith]

This code does work but when i try to use listbox4 Fill Details command it does not activate. Can anyone say why???

Private Sub ListBox4_Click()

    If ListBox4 = ("Fill Details") Then

    Application.ScreenUpdating = False
    
    Dim SrcOpen As Workbook
    Dim Des As Workbook
    Dim JCM As Worksheet
    Dim TGSR As Worksheet
    Dim FilePath As String
    Dim Filename As String
    Dim DesDataRange As Range
    Dim SrcDataRange As Range

    

    FilePath = "\\TGS-SRV01\Share\ShopFloor\PRODUCTION\JOB BOOK\"
    Filename = "JOB RECORD SHEET.xlsm"
    
   
    Set Des = Workbooks("Automated Cardworker.xlsm")
    Set JCM = Worksheets("Job Card Master")
    Set SrcOpen = Workbooks.Open(FilePath & Filename)
    Set TGSR = SrcOpen.Worksheets("TGS JOB RECORD")

    
    Set SrcDataRange = TGSR.Range("A2:AN9000")
    
    Set DesDataRange = JCM.Range("A2:Q299")
    


    JCM.Range("A4").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 40, 0)
      Range("A4").Select
    
    JCM.Range("C4").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 8, 0)
      Range("C4").Select
    
    JCM.Range("D4").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 33, 0)
      Range("D4").Select
    
    JCM.Range("F6").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 18, 0)
      Range("F6").Select
      
    JCM.Range("A8").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 2, 0)
      Range("A8").Select
      
    JCM.Range("C8").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 3, 0)
      Range("C8").Select
      
    JCM.Range("G8").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 5, 0)
      Range("G8").Select
      
    JCM.Range("K10").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 7, 0)
      Range("K10").Select
      
    JCM.Range("K8").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 4, 0)
      Range("K8").Select

    SrcOpen.Close
    
    Application.ScreenUpdating = True
    
   End If
            
    End Sub

 

[Darren Smith]

This code does work but when i try to use listbox4 Fill Details command it does not activate. Can anyone say why???

Private Sub ListBox4_Click()

    If ListBox4 = ("Fill Details") Then

    Application.ScreenUpdating = False
   
    Dim SrcOpen As Workbook
    Dim Des As Workbook
    Dim JCM As Worksheet
    Dim TGSR As Worksheet
    Dim FilePath As String
    Dim Filename As String
    Dim DesDataRange As Range
    Dim SrcDataRange As Range

   

    FilePath = "\\TGS-SRV01\Share\ShopFloor\PRODUCTION\JOB BOOK\"
    Filename = "JOB RECORD SHEET.xlsm"
   
  
    Set Des = Workbooks("Automated Cardworker.xlsm")
    Set JCM = Worksheets("Job Card Master")
    Set SrcOpen = Workbooks.Open(FilePath & Filename)
    Set TGSR = SrcOpen.Worksheets("TGS JOB RECORD")

   
    Set SrcDataRange = TGSR.Range("A2:AN9000")
   
    Set DesDataRange = JCM.Range("A2:Q299")
   


    JCM.Range("A4").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 40, 0)
      Range("A4").Select
   
    JCM.Range("C4").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 8, 0)
      Range("C4").Select
   
    JCM.Range("D4").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 33, 0)
      Range("D4").Select
   
    JCM.Range("F6").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 18, 0)
      Range("F6").Select
     
    JCM.Range("A8").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 2, 0)
      Range("A8").Select
     
    JCM.Range("C8").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 3, 0)
      Range("C8").Select
     
    JCM.Range("G8").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 5, 0)
      Range("G8").Select
     
    JCM.Range("K10").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 7, 0)
      Range("K10").Select
     
    JCM.Range("K8").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 4, 0)
      Range("K8").Select

    SrcOpen.Close
   
    Application.ScreenUpdating = True
   
   End If
           
    End Sub

 

[Darren Smith]

Sorry code is firing up now but the VLookup seems to of stopped working? Error says Unable to get the VLookup property of the WorksheetFunction Class
In the module it was working???