Ed, again, thanks for the help. No, I never recieved the files.

send them to:

I will follow your advice, and please let me know if there is anything from
our website I can send you for your help!

Brendan

"Ed Ferrero" wrote:

Hi brendan,

Did you get the files I sent you? They have the code and they do work. I
also changed the reporting page and sent it to you.

The Worksheet Change routine should go in the code pane for the 'Document
Control' sheet. Open the VB Editor, double click the 'Document Control'
sheet and add your code there.

Ed Ferrero
http://edferrero.m6.net/



"brendan" wrote in message
...
Ed,

I tweaked your code just slightly and got it to work! Awesome!!! thanks
so
much....but I still don't know where to put the worksheet change function.

Also, did you have an opportunity to view the reporting page?

thanks again!

"Ed Ferrero" wrote:

Have replied separately. The OP had a complex workbook that included both
email hyperlinks and other hyperlinks. So the code needed to be slightly
modified so that it only changed email hyperlinks.

Also, this code checks for the existance of a Part No in the email
subject
and changes it if required whilst leaving the rest of the subject
unchanged.

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet
Dim sSubjectOld As String
Dim sMsg As String

sSpace = " "
sMsg = " - Part No"

' obtain part number and description from document
sPart = Worksheets("Document Control").Range("PartNo")
sDescr = Worksheets("Document Control").Range("PartDescr")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
If Left(hLink.Address, 6) = "mailto" Then
sSubjectOld = StripOldPartNo(hLink.EmailSubject, sMsg)
hLink.EmailSubject = sSubjectOld & sMsg & sSpace & sPart & sSpace &
sDescr
End If
Next
Next
End Sub

Function StripOldPartNo(ByVal s As String, ByVal sPart As String) As
String
Dim i As Integer
i = InStr(1, s, sPart) - 1
If i < 0 Then i = Len(s)
StripOldPartNo = Left(s, i)
End Function

Finally, we can use a Worksheet Change event to run the code whenever the
Part No is changed by the user.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then ' part no has changed
Call EditHyperlinks
End If
End Sub

Ed Ferrero
http://edferrero.m6.net/

"brendan" wrote in message
...
Ed, I took you up on your offer and sent you my workbook. Any help
would
be
greatly appreciated!

thanks!

Brendan

"Ed Ferrero" wrote:

Hi brendan,

What error are you getting? What version of Excel do you use?
You can email the workbook to me if you like and I will try to sort it
out.

Ed Ferrero
http://edferrero.m6.net/

Ed thank you, but I am now getting an error with the line
"hLink.email......"

what's going on? I am not big on VBA so if there are variables that
I
need
to modify, please help!

thanks!

"Ed Ferrero" wrote:

HI brendan,

Run this macro on your workbook whenever the part number changes

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet

sSpace = "%20"

' change this to the range that contains the part number
sPart = ActiveSheet.Range("A1")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
hLink.EmailSubject = "Part" & sSpace & "No" & sSpace & sPart
Next
Next
End Sub

Ed Ferrero
http://edferrero.m6.net/

I currently have a workbook that I use to build a new part within
my
business. It contains about 12 worksheets and each work sheet
has 3
email
hyperlinks connected to it.

Each email hyperlink happens at the end of a specific task, 36 in
all.
and
in each subject line of the hyperlink I have the part number
listed.

However, when I use this workbook as a template, I want the
subject
line
to
of the hyperlink emails to reflect a new part number for the new
workbook.
I
would love to do a mass find and replace, but it doesn't work in
the
hyperlink - what can I do?

thanks for any help.

Brendan