Auto create hyperlink based on 2 cell values

[verynewtovba]

Hello,


I was wondering if someone could help.


I have a macro which I use as a change record as follows:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If ActiveSheet.Name = "ChangeRecord" Then Exit Sub

Application.EnableEvents = False

UserName = Environ("USERNAME")

NewVal = Target.Value

Application.Undo

oldVal = Target.Value

lr = Sheets("ChangeRecord").Range("A" & Rows.Count).End(xlUp).Row + 1

Sheets("ChangeRecord").Range("A" & lr) = Now
Sheets("ChangeRecord").Range("B" & lr) = ActiveSheet.Name
Sheets("ChangeRecord").Range("C" & lr) = Target.Address
Sheets("ChangeRecord").Range("D" & lr) = oldVal
Sheets("ChangeRecord").Range("E" & lr) = NewVal
Sheets("ChangeRecord").Range("F" & lr) = UserName

Target = NewVal

Application.EnableEvents = True
End Sub


So, the changes are recorded in a separate sheet called ChangeRecord, and this is working just fine.


What I would like to do is also add a hyperlink in column G which takes me straight to the change record, so, if the cell value in column B is another worksheet named Scotland, and the cell value in column C that was changed is $A$21, then the hyperlink that is auto created in column G takes me to that cell on that worksheet.

The trouble I'm having is that the sheets in Column B could be any other sheet in the same workbook and the cell values in column C could be any cell value on the other sheets as recorded by the ChangeRecord macro, I hope this makes sense.


Any help would be greatly appreciated.
Best regards

 

[bobsan42]

Target.Parent.Name ' this should give you the sheet name
Target.Address ' this should give you the changed cell(s) address

Hyperlinks formulas to places in the document are a bit awkward though.
the format is something like:

=HYPERLINK("#'[COLOR=#ff0000][B]SheetNameHere[/B][/COLOR]'!"&CELL("address",[COLOR=#ff0000][B]CellAddressHere[/B][/COLOR]),"[COLOR=#ff0000][B]text To Display here[/B][/COLOR]")

or you can add a permanent Hyperlink:

Sheets("ChangeRecord").Range("F" & lr).Hyperlinks.Add Anchor:=Sheets("ChangeRecord").Range("F" & lr), Address:="", SubAddress:= _
        "'" & Target.Parent.Name & "'!" & target.Address, ScreenTip:="[COLOR=#ff0000]screen tip[/COLOR] [COLOR=#ff0000]if you like [/COLOR]", TextToDisplay:= _
        "[COLOR=#ff0000]The Text to be Displayed in cell[/COLOR]"

 

[verynewtovba]

Hi,
Many thanks, this works a treat, I modified it slightly for aesthetic purposes;

Sheets("ChangeRecord").Hyperlinks.Add _
Anchor:=Sheets("ChangeRecord").Range("G" & lr), _
Address:="", SubAddress:="" & Sh.Name & " - " & Target.Address, ScreenTip:=" Link to changed cell/cell range "

Best regards

 

[bobsan42]

Thanks for the feedback (not getting much these days ).
Glad I could help.