Create Hyperlink in Column using VBA Code

Topic

New Reply

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

 

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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]])

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Any Update or Help on this !…

Reply | Quote