News, tips, advice, support for Windows, Office, PCs & more
Home icon Home icon Home icon Email icon RSS icon

We're community supported and proud of it!

  • Create Hyperlink in Column using VBA Code

    Home Forums AskWoody support Productivity software by function MS Excel and spreadsheet help Create Hyperlink in Column using VBA Code

    • This topic has 6 replies, 2 voices, and was last updated 1 month ago by anonymous.
    Viewing 5 reply threads
    • Author
      Posts
      • #2357528
        anonymous
        Guest

        Hello Everyone!

        I am trying to create auto hyperlink from sheet1 to sheet2 using VBA code

        condition is as follow:-

        cell in column J may have value “WON” / “LOST” / ” Active”.

        i.e if sheet1, cell in column J=”won” then create hyperlink in corresponding cell in column M to sheet2.

         

        Any Help on this will be highly appreciated.

        Thank u in advance

         

      • #2357561
        RetiredGeek
        AskWoody MVP

        This should do the trick:

        Private Sub Worksheet_Change(ByVal Target As Range)
        
        '*** Limiting the Worksheet_Change event to a firing when a single cell is changed
        
           Dim isect As Range
           
           Set isect = Application.Intersect(Range("J:J"), Target)
           
           If isect Is Nothing Then
           
             MsgBox "Ranges do not intersect" '*** Message can be eliminated if desired!
             
           Else
           
             '***Prevent following code from refiring Change Event ***
             Application.EnableEvents = False
          
             If UCase(Target.Text) = "WON" Then
        
               ActiveSheet.Hyperlinks.Add Anchor:= _
                               Target, Address:="", SubAddress:="Sheet2!" & _
                               Target.Offset(0, 3).Address(, , xlA1), TextToDisplay:=Target.Text
               
             Else
             
              Target.Hyperlinks.Delete   '*** Clear the HyperLink
              
             End If  '*** If UCase(Target.Text)
                              
             Application.EnableEvents = True '*** Reset Events ***
            
           End If   '***    If isect Is Nothing Then
        
        End Sub  'End WorkSheet_Change
        

        Note: this code needs to be placed in the Sheet1 Module!

        HTH :cool:

        May the Forces of good computing be with you!

        RG

        PowerShell & VBA Rule!
        Computer Specs

        • #2357763
          anonymous
          Guest

          Hello RG,

          Thanks for the prompt response on my query..

          The code worked fine.. but what I want is to have hyperlink in column M when any corresponding data in column J have “won” text selected.

      • #2357796
        Paul T
        AskWoody MVP

        Target.Offset(0, 3) will move the column in Sheet2 by 3 places, e.g. from J to M, while keeping the row number the same. Is that what you see?

        cheers, Paul

      • #2357815
        anonymous
        Guest

        Thanks RG

        That worked well..

        One more question is can we use “Match” function in hyperlink address?

        how to Use below formula in vba to get autohyperlink
        HYPERLINK("#Sheet2!E"&MATCH([@[Reference Number]],Sheet2!E:E,0),"Link to "&[@[Reference Number]])
      • #2357960
        RetiredGeek
        AskWoody MVP

        Not exactly sure what you are trying to do?

        Where do you want the hyperlink placed?

        What do you want the hyperlink to reference?

        Why did the original solution not meet your needs?

        May the Forces of good computing be with you!

        RG

        PowerShell & VBA Rule!
        Computer Specs

      • #2358090
        anonymous
        Guest

        Sorry RG, I am not well versed with Macro coding, But trying my bit.

         

        What I want to do is : (1). If value in cell J2 is “WON” then, create hyperlink in column N2. (2). But the hyperlink should be vlookup and match the same value Column O in sheet XYZ and Text to be displayed in column N should (“to link to & the text in column M”).

         

        Right now, I have used this formula to achieve the results in Column N -(“HYPERLINK(“#’XYZ’!O”&MATCH(ABC!$M2,XYZ!O:O,0),”Link to “&ABC!$M2)”).

        But I want to do it using VBA Macro. And evrytime new data is added it should automatically do the job in new added rows as well.

         

        For Better understanding I am attaching sample file link for your reference.

        Thank you in advance

        NJ

        • #2359371
          anonymous
          Guest

          Any Update or Help on this !…

    Viewing 5 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, no politics or religion.

    Reply To: Create Hyperlink in Column using VBA Code

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.