+ Reply to Thread
Results 1 to 14 of 14

Macro to edit hyperlink text to display

  1. #1
    Registered User
    Join Date
    07-11-2019
    Location
    New York
    MS-Off Ver
    Windows 10
    Posts
    7

    Macro to edit hyperlink text to display

    I am looking for a macro that will edit a hyperlink if the text to display changes based on the criteria in the cell. The cell reference will return information from the text to display. Example: The hyperlink text to display in cell A1 may say John but if the name changes to Mary the macro will edit the hyperlink text to display to say Mary. There are over 100 hyperlinks and I do not want to manually edit the hyperlinks each time a cell value changes. Has anyone found a way to edit a hyperlink like this through a macro.

    Thank you

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    22,815

    Re: Macro to edit hyperlink text to display

    Hello qkxgm0,

    Welcome to the forum!

    Are you referring to hyperlink formulas or VBA hyperlinks?

    Please post a sample of the code you are using.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    07-11-2019
    Location
    New York
    MS-Off Ver
    Windows 10
    Posts
    7

    Re: Macro to edit hyperlink text to display

    Thank you for the welcome. I am referring to VBA hyperlinks and I don't presently have a code to edit the hyperlinks however the code I am using to return the original text to display is below. The problem is when the data changes in a certain cell, I need the hyperlink text to display to change also to match the gsource name in the cell below "C5"

    Please Login or Register  to view this content.
    Last edited by jeffreybrown; 07-11-2019 at 07:27 PM. Reason: Please use code tags!

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    22,815

    Re: Macro to edit hyperlink text to display

    Hello qkxgm0,

    Thanks for posting the code. Can you walk me through what you doing? Is GSource a Range address or value?

  5. #5
    Registered User
    Join Date
    07-11-2019
    Location
    New York
    MS-Off Ver
    Windows 10
    Posts
    7

    Re: Macro to edit hyperlink text to display

    I am trying to post the excel file so you can view what I am doing but it will not upload.

  6. #6
    Registered User
    Join Date
    07-11-2019
    Location
    New York
    MS-Off Ver
    Windows 10
    Posts
    7

    Re: Macro to edit hyperlink text to display

    The file did post. If you open it up and click on the link under unit name, i.e. Syracuse, the link will take you to a page with the Syracuse information on it. If the name Syracuse is exchanged with another name, I want the other name information to come up but I want a macro to be able to edit the link without me having to manually do control K.

  7. #7
    Forum Contributor
    Join Date
    03-08-2018
    Location
    Denmark
    MS-Off Ver
    2016 for Windows
    Posts
    194

    Re: Macro to edit hyperlink text to display

    Maybe this is what you are looking for:

    Vlookup and create a hyperlink to the first match
    When working with large datasets, you may often find yourself in a situation when you need to look up a specific value and return the corresponding data from another column. For this, you use either the VLOOKUP function or a more powerful INDEX MATCH combination.

    But what if you not only want to pull a matching value but also jump to the position of that value in the source dataset to have a look at other details in the same row? This can be done by using the Excel HYPERLINK function with some help from CELL, INDEX and MATCH.

    The generic formula to make a hyperlink to the first match is as follows:

    Please Login or Register  to view this content.
    This is the Link to the full artical:
    https://www.ablebits.com/office-addi...link-function/

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    22,815

    Re: Macro to edit hyperlink text to display

    Hello qkxgm0,

    In your macro you reference the worksheet named "Area Summary". However, in the workbook you posted there is no worksheet with that name.

  9. #9
    Registered User
    Join Date
    07-11-2019
    Location
    New York
    MS-Off Ver
    Windows 10
    Posts
    7

    Re: Macro to edit hyperlink text to display

    I took that worksheet out so that part of the code can be deleted, however the rest of the code works fine as it just passes over that part of the code. someone gave me a formula but it does not work for what I need as I need to edit the hyperlinks when the cell criteria changes. I know this is difficult as I have search high and low to try and find what I am looking for to no success. When I try and form it as a macro to edit, the macro will not record an edit to a hyperlink.

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    22,815

    Re: Macro to edit hyperlink text to display

    Hello qkxgm0,

    I am not sure which cell you are referring to changing. Is it C5 on the Trend sheet?

  11. #11
    Registered User
    Join Date
    07-11-2019
    Location
    New York
    MS-Off Ver
    Windows 10
    Posts
    7

    Re: Macro to edit hyperlink text to display

    Yes, if you click on each unit name like listed below, the hyperlink will take you to the trend chart and the name will change based on the name you click on. These name change as the sheet is updated and I have to manually edit the hyperlinks to change the text to display name. I am trying to find a way automate this task.


    Unit Name
    SYRACUSE PO
    TROY PO
    SYR-FRANKLIN SQUARE S
    WATERTOWN PO
    ALB-GMF STORE
    SYR-COLVIN STA
    ALB-CARRIER ANX
    LATHAM PO
    LIVERPOOL PO
    GLENS FALLS PO

  12. #12
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    22,815

    Re: Macro to edit hyperlink text to display

    Hello qkxgm0,

    Okay, I believe this macro will do what you need. Whenever a cell in column "F" of the Summary sheet is changed and the background of the cell is white, either the hyperlink text to display is changed or a new hyperlink will be added to cell. A blank cell will not create a hyperlink but remove it.

    NOTE: The macro will only do this for a single cell as the user types.

    Worksheet Change Event Code for Summary
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    07-11-2019
    Location
    New York
    MS-Off Ver
    Windows 10
    Posts
    7

    Re: Macro to edit hyperlink text to display

    Awesome, you are truly a genius. Thank you for taking the time to find an excellent solution to my problem.

  14. #14
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    22,815

    Re: Macro to edit hyperlink text to display

    Hello qkxgm0,

    Glad to help. If you don't have any other questions and don't need further help with this thread, please marked it Solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1