Combining VLOOKUPS With Hyperlinks in VBA Code?

Ben171

Board Regular
Joined
Jul 2, 2021
Messages
77
I have created a user entry form in Excel which then creates a database upon user input.

One of the tables i use for this is the following:

1625215219652.png


On my userform i use the following code to enter the Batch number, then automatically pull through the rest of the data from the table to this form.

VBA Code:
Private Sub BatchNo_AfterUpdate()
Dim Res As Variant
Dim MyTableArray As Range
Set MyTableArray = Sheets("SafetyValveData").Range("A:F")


'vlookup batch no, else error not found
Res = Application.VLookup(Me.BatchNo.Value, MyTableArray, 1, 0)
If IsError(Res) Then
   MsgBox "Batch Number not found"
   JobNo.Value = ""
   DateOfManufacture = ""
Else

Me.JobNo.Value = WorksheetFunction.VLookup(Me.BatchNo.Value, MyTableArray, 4, 0)
Me.DateOfManufacture.Value = WorksheetFunction.VLookup(Me.BatchNo.Value, MyTableArray, 5, 0)
Me.Certificate.Value = WorksheetFunction.VLookup(Me.BatchNo.Value, MyTableArray, 6, 0)

End If

    Exit Sub
End Sub

I then use the following code to transfer this information, to the excel database.

Code:
 Dim sh As Worksheet
    Dim iRow As Long
 
    Set sh = ThisWorkbook.Sheets("Database")
    
    
    If frmForm.txtRowNumber.Value = "" Then
    
        iRow = [Counta(Database!A:A)] + 1
    Else
    
        iRow = frmForm.txtRowNumber.Value
        
    End If
    
    'Duplicate checking for Works Order Number
    
    
    
    With sh
    
    'adding each row to database
        
        .Cells(iRow, 1) = frmForm.BatchNo.Value
        
        .Cells(iRow, 2) = frmForm.JobNo.Value
        
        .Cells(iRow, 3) = frmForm.DateOfManufacture.Value
        
        .Cells(iRow, 4) = frmForm.Certificate.Value
        
    End With

End Sub

However obviously the issue with this, is that it returns the correct value in the certificate field, however does not include the hyperlink to take me to where the certificate is stored.

Is there anyway i can do this?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,969
Messages
5,767,382
Members
425,410
Latest member
SmittyT

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