Concatenate Userform texts and insert as a link in a cell

Question

Concatenate Userform texts and insert as a link in a cell

Navigation

#1 by (0 votes)

0

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

Path to a certain folder (textbox)

The first 2 characters of a selected option (combobox)

Current date and time

What I basically did to get this was this:

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 run the first line, the link works perfectly for the folder, however, when I run the fourth one (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 do not understand the problem. Any idea?

excel vba userform

asked by anonymous 25.03.2017 / 15:50

1 answer

Association condition E as formatting between numerical intervals Error initiating activity on Api 17

score 0 · Accepted Answer

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 blurring. You should do something like this:

First enter the values in 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, like this:

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 & """)"

This should solve your problem!