Hyperlink customization in excel

Asked

Viewed 743 times

2

Does anyone know if there is a way that I can create a hyperlink in an excel cell where the value specified in this cell is passed to the id value of a URL? Example:

Cell with value: 100531, when clicking on it call a url http://test/test/view.php? id=VALORDACELULA where VALORDACELULA is the value that is in the cell that was clicked?

Thanks in advance.

excel vba

  • You will create hyperlinks in cells one by one?

  • It can be, as long as I take the value of the cell, in this case I can do it once and drag it down that will apply to all others

  • I prepared an answer then. But maybe it is easier for you to create the hyperlinks on your own, using VBA as well. Take a look at Hyperlinks.Add.

1 answer

2


Do the following:

  1. In the cell where the value is, right-click and select "Hyperlink".

  2. In the hyperlinks window, select the "Put This Document" button and enter the address of the cell itself where the value is. Click OK. This hyperlink will do nothing because it leads to the cell where it was clicked.

inserir a descrição da imagem aqui

  1. Open the Excel VBA code window (ALT+F11) and add the following code to the spreadsheet:
Private Sub Worksheet_FollowHyperlink(ByVal oTarget As Hyperlink)
    Dim sURL As String
    sURL = "http://teste/teste/view.php?id=" & ActiveCell.Value
    ActiveWorkbook.FollowHyperlink sURL
End Sub

Note that this code captures the event of the specific spreadsheet (to Worksheet, that is, the tab) and not the whole file. If you have more than one tab (Plan1, Plan2, Plan3, etc) and want to do the same on it, you need to duplicate that code.

  • It is not taking the value but the position, when I click goes to id=Plan1! E2

  • Because you didn’t set the link to go on the right spreadsheet (see the image of item 2) and, mainly, you didn’t put the code on the right spreadsheet (see the comment at the end of the reply I just edited).

  • I didn’t understand what you meant: I didn’t post any image of non-existent url. You put the E2 reference, but of which spreadsheet? If it went to ORDERS! E2 and it wasn’t meant to go, it’s because you put E2 in the wrong spreadsheet.

  • I did exactly as in the image, I put in reference of the cell the position E2. I don’t have more than one tab, until I renamed it to orders, yet I click it goes to ORDERS! E2

  • But the cell you put the link in is at E2?

  • By the way, what do you mean by "will"? Excel, or the URL? Because I don’t know if you noticed, but I did a patch in the code to get the cell value and not the link name. It was right at the beginning, maybe you took the previous version of the code?

  • 2

    I’m sorry I hadn’t noticed, it’s just this, it worked :) thank you.

  • Not at all. But see that it took me longer to understand what you were talking about than to make an example. rs Do not take offense to the comment, but in the future try to be clearer in the explanations. "He will" or "he is not picking up" are very vague expressions.

  • right, I will pay attention to this, just a doubt, has as the moment I type the value in the cell, already set the respective value to it in the url, or I will have to do procedure 2 for each cell manually ?

  • There’s no way. That’s why my first question for you was if you were going to create the links manually, and you said yes. You can even do another procedure (on a button, for example) that creates the hyperlinks manually, but it would be for the values that are already in the cells. If you want something more convoluted, you will need to implement a form for the user to type outside the cell.

  • Anyway, this site is not a forum. If you have more questions, open another question. And if you’re going to open up another question about the same "subject," I suggest you don’t provide a partial solution of your own and ask someone to help you make it right, but explain the problem and let the community offer a suitable solution (that is, explain what your spreadsheet is about, who the users are, if they will enter the values and want to automatically go to the links in each one, or you can do this in a centralized way (a window that the user click and select the cell to then go to the site)?

Show 6 more comments

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.