Trying to use listbox value to drive a VLookup. Can`t get it to run

[Darren Smith]

Making a Listbox value activate the code.

It opens the correct spreadsheet. But will not active code??

Option Explicit

Private Sub ListBox4_Click()

Application.ScreenUpdating = False
    
     On Error GoTo Error_Handler

   
    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
    Dim LastRow As Long
    
    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")
    
    LastRow = TGSR.Range("A2" & Rows.Count).End(xlUp).row

    
    Set SrcDataRange = TGSR.Range("A2" & LastRow)
    
    Set DesDataRange = JCM.Range("A2:Q299")
  
     
     If Body_And_Vehicle_Type_Form.ListBox4(1) = True Then

    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
      
If Err.Number = ("1004") Then
Error_Handler: MsgBox "Need to fill in JobCard No. in Job Card Master"
     End If
     
    End Sub

 

[Fluff]

Duplicate to: I am using a listbox command to vlookup

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.