Use cell values to create hyperlinks using VBA Worksheet_Change Event

  • I have code that takes the value of a cells in a single column and then uses that value to create a hyperlink within that same cell. It works with the code below. However I'm at a loss to do the same thing with two different columns on the same worksheet. The code below converts the value in column A into a hyperlink. I want to do the same to column C on the same worksheet but with a different hyperlink. I've tried unsuccessfully by adding a new section of code with a new range for column C. I also attempted to add both columns in the code below with additional variables. I haven't done much VBA coding. Any assistance would be much appreciated.


  • CowFarmer

    Changed the title of the thread from “Use cell values to create hyperlinks using VBA” to “Use cell values to create hyperlinks using VBA Worksheet_Change Event”.
  • Ok, I think I may have figured it out for the most part. The links are immediately available in column A, however the column C links require clicking in the cell then off the cell to activate the link. What am I missing?


  • Hi CO,


    Not sure if this does what you are after, the first only works on the target, the second works through the range depending which range changed.


    OR

    HTH

    Justin

  • JD,


    Thanks for the help! I've tried both of your code examples. The first is giving me a Type Mismatch on line 6: str = Target.Value2.

    The second version works, however it loops through my entire worksheet column by column and applies link apath and cpath to every column (i.e. aPath applied to column 1,3,5,7 etc. and cPath to 2.4.6.8 etc).


    I only need to apply links to columns A and C. Also, my code example range starts a row 2 due to headers.

  • Hi CO,


    Not sure why the first does not work for you, it works fine here.


    The second I should have foreseen, sorry about that. I have rewritten to be more specific. If this one needs to be tailored further please upload an example of your data format. Really it is just about defining the range more precisely.


  • JD,


    Thanks for your help!

    I've attached an example spreadsheet. I've set it up similarly to how I have my actual document. The sheet I wish to apply the code to is the result of a query (in this case Sheet3).


    Your code above works on a standard table but results in a type mismatch error when refreshing the table constructed from a query on line 2: "If Target.Value2 = vbNullString Then GoTo Exit_Sub".


    Also, I'm trying to avoid applying the link to the first row since that's the header row. I tried starting the range at A2 and C2 but it also resulted in errors.

  • Hi CO,


    I cannot really help with the line 2 error without being able to replicate it, I do not know what would give an error when checking .value2, you could delete the line and test for blanks using the str = x.value2 then as below use if x = vbnullstring then goto Nxt_X... but it may give the same error.


    To start on row 2 just enter a goto into the loop:


    Code
    For Each x In rng
    If x.Row = 1 Then GoTo Nxt_X
    code here...
    Nxt_X:
    Next x

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!