Home |
Search |
Today's Posts |
#1
|
|||
|
|||
hyperlink email challenge
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 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 |
#5
|
|||
|
|||
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 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 |
#6
|
|||
|
|||
hyperlink email challenge
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 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 |
#7
|
|||
|
|||
hyperlink email challenge
Ed,
I greatly appreciate your help, but I think that I am doing something wrong. Being that I am a novice at this, pardon the ignorant questions. First, I took the macro you wrote, and copied and pasted it into the Macro, from the line "sub EditHyperlinks()" to the line "End Function" - and when I run this macro it comes up with the following error: compile error: syntax error - what's going on ? second - the the worksheet change event that you have listed below - I love the idea, but where do I place the code you wrote? thanks agian for all of the help! Brendan "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 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 |
#8
|
|||
|
|||
hyperlink email challenge
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 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 |
#9
|
|||
|
|||
hyperlink email challenge
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 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 |
#11
|
|||
|
|||
hyperlink email challenge
Ed, I put in the worksheet change where you mentioned and it worked AWESOME!!!
"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 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 |
#12
|
|||
|
|||
hyperlink email challenge
Hi brendan,
Glad to hear it. I have also re-sent the files to your private address. Ed, I put in the worksheet change where you mentioned and it worked AWESOME!!! "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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel email address hyperlink does not update | Excel Discussion (Misc queries) | |||
Email hyperlink changes not showing in excel | Excel Worksheet Functions | |||
Separating an email address embedded in a "mail to:" hyperlink | Excel Discussion (Misc queries) | |||
How to transfer email hyperlink to another cell | Excel Discussion (Misc queries) | |||
email address hyperlink | Excel Worksheet Functions |