Concatenate Userform text and insert as link in a cell

Question

I’m trying to feed a table with 3 information inserted from the execution of a Userform and, at the end, make a direct hyperlink in the cell with the information inserted. The information is as follows::

1. Path to a particular folder (textbox)
2. The first 2 characters of a selected option (combobox)
3. Current date and time

What I did basically to get this was the following:

Cells(LRow, 1).Formula = "=Hyperlink(""H:\backup\test\"")"
Cells(LRow, 2).Value = UCase(Left(Me.Cbx_State.Value, 2))
Cells(LRow, 3).Value = Format(Now, "_yyyymmdd_hhmmss")
Cells(LRow, 1) = Cells(LRow, 1) & Cells(LRow, 2) & Cells(LRow, 3)

When I only run the first line, the link works perfectly for the folder, however, when rotating the fourth (where I concatenate the two other information, the link stops working.

I was hoping to get a link like this, for example:

H:\backup\test\CT-20170322_162111

I don’t understand the problem. Any idea?

Answer 1

The problem is that you are concatenating a formula with other texts and, as the formula transforms the selected content into a link, things are getting confused. You should do something like this:

First enter the values of column 2 and 3, and then mount the contents of column 1, which will be the link:

Cells(LRow, 2).Value = UCase(Left(Me.Cbx_State.Value, 2))
Cells(LRow, 3).Value = Format(Now, "_yyyymmdd_hhmmss")
Cells(LRow, 1).Formula = "=Hyperlink(""H:\backup\test\" & Cells(LRow, 2) & Cells(LRow, 3) & """)"

If you do not want such a large command line, declare a variable to replace the concatenated part, thus:

Cells(LRow, 2).Value = UCase(Left(Me.Cbx_State.Value, 2))
Cells(LRow, 3).Value = Format(Now, "_yyyymmdd_hhmmss")
Dim c As String: c = Cells(LRow, 2) & Cells(LRow, 3)
Cells(LRow, 1).Formula = "=Hyperlink(""H:\backup\test\" & c & """)"

That should solve your problem!