How to turn Path/String in Textbox into a hyperlink when Userform is submitted.

[Krosis]

Hi Guys,
New to Excel, VBA and userforms so please bare with me!

I currently have my userform setup whereby the user selects "Upload Feedback" which prompts the user to browse for a file and inputes the Path/String into a text box underneath (image below). My issue is that when the userform is submitted the path/string is displayed as text and not as a hyperlink. I am wanting to userform to convert said path into a hyperlink and display the name as "Feedback" instead of the path name if possible.

I have pasted the code im using below but please be mindful that all of the code has been aquired from youtube etc and tweaked to work on my userform.. its not pretty but it works and all suggestions are welcome!

Private Sub SubmitButton_Click()

Dim tbl As ListObject
Dim ws As Worksheet
Dim lrow As Range
Dim lrow2 As Long

Set tbl = Sheets("Task Check").ListObjects("Table1")

    If tbl.ListRows.Count > 0 Then

        Set lrow = tbl.ListRows(tbl.ListRows.Count).Range
        For col = 1 To lrow.Columns.Count
            If Trim(CStr(lrow.Cells(1, col).Value)) <> "" Then
                tbl.ListRows.Add
                Exit For
            End If
        Next
    End If

    lrow2 = tbl.ListRows.Count

     tbl.DataBodyRange(lrow2, 1).Value = DateBox.Value
     tbl.DataBodyRange(lrow2, 2).Value = CheckerBox.Value
     tbl.DataBodyRange(lrow2, 3).Value = CaseworkerBox.Value
     tbl.DataBodyRange(lrow2, 4).Value = TeamBox.Value
     tbl.DataBodyRange(lrow2, 5).Value = OutcomeBox.Value
     tbl.DataBodyRange(lrow2, 6).Value = TaskIDBox.Value
     tbl.DataBodyRange(lrow2, 7).Value = FeedbackBox.Value
     tbl.DataBodyRange(lrow2, 8).Value = CommentsBox.Value
Unload Me

Task_UF.Show
 
End Sub

 

[Rick Rothstein]

(Untested but I think it should work) Try using this line of code...

tbl.DataBodyRange(lrow2, 7).Formula = "=HYPERLINK(""" & FeedbackBox.Value & """,""Feedback"")"

 

[Krosis]

(Untested but I think it should work) Try using this line of code...

tbl.DataBodyRange(lrow2, 7).Formula = "=HYPERLINK(""" & FeedbackBox.Value & """,""Feedback"")"

Worked Perfectly! Thank you so much

 

[JEC]

This could be enough for your whole code.

Sub jec()
 With Sheets("Task Check").ListObjects("Table1")
    .ListRows.Add.Range = Array(DateBox.Value, CheckerBox.Value, CaseworkerBox.Value, TeamBox.Value, OutcomeBox.Value, TaskIDBox.Value, FeedbackBox.Value, CommentsBox.Value)
    .ListRows(.ListRows.Count).Range(1, 7).Formula = "=HYPERLINK(""" & FeedbackBox.Value & """,""Feedback"")"
 End With
End Sub

 

[Krosis]

Thanks for the input JEC however I dont have enough knowledge with coding to implement the above without screwing up what iv already got in place so Id rather not risk it!

After coming back to test everything is working I seem to have ran into another issue I hope you guys can help me solve.

The upload feedback button and Path field isnt a required field in my userform so it can be left blank by the user however when the data is transfered to the excel spreadsheet it is putting the blank entry as Feedback instead of leaving the field blank. When clicking on the cell it displays =HYPERLINK("","Feedback").

What would I need to edit to keep the cell blank if no feedback as been submitted?

Thanks again in advance!

 

[JEC]

If you try this code on a copy of your workbook.
Remove all the code you posted and replace it by the code below. Give it a try and just look what it does. I believe you will understand if you study this a while.

Sub jec()
 With Sheets("Task Check").ListObjects("Table1")
    .ListRows.Add.Range = Array(DateBox.Value, CheckerBox.Value, CaseworkerBox.Value, TeamBox.Value, OutcomeBox.Value, TaskIDBox.Value, FeedbackBox.Value, CommentsBox.Value)
    With .ListRows(.ListRows.Count).Range(1, 7)
       If .Value = "" Then Exit Sub
       .Formula = "=HYPERLINK(""" & FeedbackBox.Value & """,""Feedback"")"
    End With
 End With
End Sub

 

[Rick Rothstein]

Try changing the line of code I gave you to this instead...

If Len(FeedbackBox.Value) Then tbl.DataBodyRange(lrow2, 7).Formula = "=HYPERLINK(""" & FeedbackBox.Value & """,""Feedback"")"

 

[Krosis]

If you try this code on a copy of your workbook.
Remove all the code you posted and replace it by the code below. Give it a try and just look what it does. I believe you will understand if you study this a while.

Sub jec()
 With Sheets("Task Check").ListObjects("Table1")
    .ListRows.Add.Range = Array(DateBox.Value, CheckerBox.Value, CaseworkerBox.Value, TeamBox.Value, OutcomeBox.Value, TaskIDBox.Value, FeedbackBox.Value, CommentsBox.Value)
    With .ListRows(.ListRows.Count).Range(1, 7)
       If .Value = "" Then Exit Sub
       .Formula = "=HYPERLINK(""" & FeedbackBox.Value & """,""Feedback"")"
    End With
 End With
End Sub

Good shout on making a copy.. Honesly not sure why I didnt think of that lol. Did as instructed above and when pressing the submit the userform doesnt submit the data onto the spreadsheet.

Try changing the line of code I gave you to this instead...

If Len(FeedbackBox.Value) Then tbl.DataBodyRange(lrow2, 7).Formula = "=HYPERLINK(""" & FeedbackBox.Value & """,""Feedback"")"

Tried the above suggestion and the cell now contains the previous uploaded feedback instead of =HYPERLINK("","Feedback").

 

[Krosis]

Try changing the line of code I gave you to this instead...

If Len(FeedbackBox.Value) Then tbl.DataBodyRange(lrow2, 7).Formula = "=HYPERLINK(""" & FeedbackBox.Value & """,""Feedback"")"

Not sure what I did wrong the first time around (Copied and pasted) but it didnt work. Iv now gone back and typed "If Len(FeedbackBox.Value) Then" manually and it works like a charm!

Thanks again for the support Rick! Much appreciated