Combining VLOOKUPS With Hyperlinks in VBA Code?

[Ben171]

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:

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.

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.

 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?