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

Status
Not open for further replies.

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
400
Office Version
  1. 2019
Platform
  1. Windows
Making a Listbox value activate the code.

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

VBA 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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,803
Office Version
  1. 365
Platform
  1. Windows
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.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,128,165
Messages
5,629,068
Members
416,363
Latest member
zaveedd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top