Change Event needed

[marshyrob]

Hello

I have an Excel worksheet which has various columns. One is a column that has ID numbers in. Each ID number relates to a .txt file called the same as the ID number(which are located in the same folder). In the column next to this i want to place a hyperlink that references the .txt file matching the ID number. I have a lot of ID numbers so manually doing this is going to take forever and its also an on going project.

After lots of help from skie and mrmovie i have the following macro:

Sub MakeALink()
intEndRow = 300
For intRow = 2 To intEndRow
strCell = "D" + Trim(Str(intRow))
If Range(strCell) <> "" Then
strLink = "c:\temp" _
& Range(strCell).Value & ".txt"
ActiveSheet.Hyperlinks.Add Anchor:=Cells(intRow, "E"), _
Address:=strLink, TextToDisplay:="Click Here"
End If
Next
End Sub

This works well except i need it to add a hyperlink for every new cell i add an ID to (at present it will only do this everytime i run the macro and change the cell reference).

mrmovie suggested using the onchange option in excel but ive no idea how to use it. Im not very good with VB so any help would be appreciated.

Thanks

Rob

 

[shetlandbob]

Hit alt f11 to get to VBA mode, under microsoft excel objects select the sheet you data is on. Then rather than general select worksheet from the drop down list (this is macros associated with this sheet only). in the second drop down list select Change - this will create a sub routine that is activated when the worksheet changes. Paste your code in there.

Cheers

Robert Cumming

http://www.consultantengineer.co.uk

 

[marshyrob]

Hi Robert

Excellent it works a treat, thanks for your help!

Rob

 

[mrmovie]

hi marshyrob, the other thing i was hinting was changing your code to be more efficient...something like..(please excuse my lack of understanding of excel, i am sure someone can tidy it up some what)

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Column & ":" & Target.Row
Dim strValue As String
strValue = Cells(Target.Row, Target.Column).Value
If strValue <> "" Then
strLink = "c:\temp" _
& strValue & ".txt"
ActiveSheet.Hyperlinks.Add Anchor:=Cells(Target.Row, "E"), _
Address:=strLink, TextToDisplay:="Click Here"
End If

End Sub

'that way you are only updating the actual cell that has changed which will make things more efficient.
'i am sure my code falls flat on its face if one has changed a 'Range' but there you go

 

[marshyrob]

Thanks mrmovie

Ill give that a go when i have the time!

Thanks for your help in this.

Rob