I want to implement a barcode finder in excel, so I have item names and barcodes in separate sheets depending on what group an item belongs to. The user clicks on a radio button (selecting Group 1, Group 2, or whatever) and a list box's fill range is set to one of the dynamic name ranges I have defined.

enter image description here

The user then click on a button in order to copy the barcode for the corrseponding item. Here is the macro associated with the button:

Sub Get_Barcode()
    Dim objData As New DataObject
    Dim barcode As String
    On Error GoTo Errorcatch
    barcode = Application.WorksheetFunction.VLookup(ListBox1.Text, ListBox1.ListFillRange, 1, False)
    objData.SetText barcode
    MsgBox "Barcode " & barcode & " copied to clipboard"
MsgBox Err.Description
End Sub

The problem is that it displays a Unable to get the VLookup property of the WorksheetFunction class and I don't know what I am doing wrong ...


Solved it.

Changed corresponding line in original code with following:

barcode=Application.VLookup(ListBox1.Text,ActiveWorkbook.Names(ListBox1.ListFillRange).RefersToRange, 2, False)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.