0

My spreadsheet generates dynamic hyperlinks based om car license numbers. If I click them I got access to an internet database. Cell is built with 'Concatenate' function.

Sub Macro10()

ActiveSheet.Unprotect Password:=""
Range("a1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
ActiveSheet.Protect Password:=""
End Sub

If I run this macro on a fresh typed in cell, it works. If I run it based on the concatenate cell I receive message 'Subscript out of range error'. Even when I run it by using a cell reference in A1 like '=D15' I receive same message. Apparently cell content is not recognized as a hyperlink although the manual process does.

Also, when I record a macro based on steps I do manually, it shows similar message.

I have no idea to solve this problem. Maybe someone has ?

Thanks, Dennis

Added:

Link building:

HYPERLINK(CONCATENATE("ovi.rdw.nl/… link") Cell G11 contains a car license number like 49-KNL-6.

Note: please hover the ovi link to see exact link building. Sorry, don't know how to write better.

As I said before, within the spreadsheet link works fine, but I cannot have it work properly through a macro. I need this macro as it should temporarily remove sheets password.

Dennis
  • 11
  • 4
  • You need real hyperlink in the cell. What is the formula you are using to build the cell ?? – Gary's Student Nov 17 '16 at 16:59
  • I use next : =HYPERLINK(CONCATENATE("https://ovi.rdw.nl/default.aspx?license=";SUBSTITUTE(G11;"-";""));"RDW link") Cell G11 contains a car license number like 49-KNL-6. As I said before, within the spreadsheet link works fine, but I cannot have it work properly through a macro. I need this macro as it should temporarily remove sheets password. – Dennis Nov 17 '16 at 20:33
  • This will create a Hyperlink, but not a Hyperlink Object.....thus VBA does not "see" it. – Gary's Student Nov 17 '16 at 20:36
  • OK, I understand. So how should I create the Object ? – Dennis Nov 17 '16 at 20:43
  • Meanwhile I learned that many people have struggled with this issue, sadly without success. I will overcome the issue by unlocking this result cell in the spreadsheet so it's clickable. I will hide formula and in case somebody occasionally erases content I will include a 'copy formula' macro. – Dennis Nov 20 '16 at 14:42
  • .........................Good luck! – Gary's Student Nov 20 '16 at 14:44

0 Answers0