Help Modifying Hyperlink Macro - microsoft.public.excel.programming

Hello, I currently have the following macro that converts text to a hyperlink. Sub CovertTxt2Hyperlink() Dim cCell As Range Dim strHLinkBase As String strHLinkBase = "http://grow.hp.com/Saba/loginAsUser.jsp?deepLinkName=CourseDetail&deepLinkParams=courseId=" For Each cCell In Selection.Cells If cCell.Hyperlinks.Count = 0 Then On Error Resume Next ActiveSheet.Hyperlinks.Add _ Anchor:=cCell, _ Address:=strHLinkBase & cCell.Value, _ TextToDisplay:=cCell.Value cCell.NumberFormat = "@" 'Format cell as text End If Next cCell End Sub It works great for the spreadsheets that I have used it on to date. However, I have received a new workbook that I will be receiving quarterly that will require modification to this macro to further automate the process of converting the cell content to a hyperlink. In this workbook I have 4 sheets; Welcome, Sales, Consulting & Suport. In the last 3 sheets there is a table that has a catalog id column beginning at D9. In the current macro I have to select the cells and then run the macro to convert the contents. I want to change the macro so that all of the cells in Column D, beginning at Row 9 in the Sales, Consulting & Support sheets will be converted when the macro is run. In addition to this change the macro needs to take several different Catalog id formats into consideration and apply a specific action. 1) Currently this macro works fine if the format is either; 1234, 12345, 00012345, 00123456 or 01234567. In the new macro I want the hyperlink to be the same as what it currently is written to create if it finds 1 of the 5 noted id formats. 2) Another format that is found in this column is a url. If the macro finds this format I need the url to be retained in the hyperlink. 3) The 3rd format is an id that begins with WBT. In this case I need the hyperlink to contain, http://grow.hp.com/Saba/Web/Main Additionally, in this case an additional enhancement would be to add a Comment to the cell with instructions on what to do once they are redirected to the url. 4) The final format is that the cell may contain the word Exam. In this case I don't want any hyperlink to be created. Any assistance in whole or in part would be greatly appreciated. Please let me know if any additional information is required. -- Regards, Chris

I forgot to note that this is for Excel 2003. -- Regards, Chris "eckert1961" wrote: > Hello, > > I currently have the following macro that converts text to a hyperlink. > > Sub CovertTxt2Hyperlink() > Dim cCell As Range > Dim strHLinkBase As String > > strHLinkBase = > "http://grow.hp.com/Saba/loginAsUser.jsp?deepLinkName=CourseDetail&deepLinkParams=courseId=" > > For Each cCell In Selection.Cells > > If cCell.Hyperlinks.Count = 0 Then > On Error Resume Next > ActiveSheet.Hyperlinks.Add _ > Anchor:=cCell, _ > Address:=strHLinkBase & cCell.Value, _ > TextToDisplay:=cCell.Value > cCell.NumberFormat = "@" 'Format cell as text > End If > Next cCell > End Sub > > It works great for the spreadsheets that I have used it on to date. However, > I have received a new workbook that I will be receiving quarterly that will > require modification to this macro to further automate the process of > converting the cell content to a hyperlink. > > In this workbook I have 4 sheets; Welcome, Sales, Consulting & Suport. In > the last 3 sheets there is a table that has a catalog id column beginning at > D9. In the current macro I have to select the cells and then run the macro to > convert the contents. I want to change the macro so that all of the cells in > Column D, beginning at Row 9 in the Sales, Consulting & Support sheets will > be converted when the macro is run. > > In addition to this change the macro needs to take several different Catalog > id formats into consideration and apply a specific action. > > 1) Currently this macro works fine if the format is either; 1234, 12345, > 00012345, 00123456 or 01234567. In the new macro I want the hyperlink to be > the same as what it currently is written to create if it finds 1 of the 5 > noted id formats. > > 2) Another format that is found in this column is a url. If the macro finds > this format I need the url to be retained in the hyperlink. > > 3) The 3rd format is an id that begins with WBT. In this case I need the > hyperlink to contain, http://grow.hp.com/Saba/Web/Main > Additionally, in this case an additional enhancement would be to add a > Comment to the cell with instructions on what to do once they are redirected > to the url. > > 4) The final format is that the cell may contain the word Exam. In this case > I don't want any hyperlink to be created. > > Any assistance in whole or in part would be greatly appreciated. Please let > me know if any additional information is required. > > -- > Regards, > Chris

Why not a double click event that uses a cell value instead. I do this with my menu sheet. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.DisplayAlerts = False Dim WantedSheet As String WantedSheet = Trim(ActiveCell.Value) If WantedSheet = "" Then Exit Sub On Error Resume Next If Sheets(WantedSheet) Is Nothing Then GetWorkbook ' calls another macro to do that Else Application.GoTo Sheets(WantedSheet).Range("a4") End If Application.DisplayAlerts = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message news:6D7234EF-C637-44DB-8D0A-91B8113FB353@microsoft.com... > Hello, > > I currently have the following macro that converts text to a hyperlink. > > Sub CovertTxt2Hyperlink() > Dim cCell As Range > Dim strHLinkBase As String > > strHLinkBase = > "http://grow.hp.com/Saba/loginAsUser.jsp?deepLinkName=CourseDetail&deepLinkParams=courseId=" > > For Each cCell In Selection.Cells > > If cCell.Hyperlinks.Count = 0 Then > On Error Resume Next > ActiveSheet.Hyperlinks.Add _ > Anchor:=cCell, _ > Address:=strHLinkBase & cCell.Value, _ > TextToDisplay:=cCell.Value > cCell.NumberFormat = "@" 'Format cell as text > End If > Next cCell > End Sub > > It works great for the spreadsheets that I have used it on to date. > However, > I have received a new workbook that I will be receiving quarterly that > will > require modification to this macro to further automate the process of > converting the cell content to a hyperlink. > > In this workbook I have 4 sheets; Welcome, Sales, Consulting & Suport. In > the last 3 sheets there is a table that has a catalog id column beginning > at > D9. In the current macro I have to select the cells and then run the macro > to > convert the contents. I want to change the macro so that all of the cells > in > Column D, beginning at Row 9 in the Sales, Consulting & Support sheets > will > be converted when the macro is run. > > In addition to this change the macro needs to take several different > Catalog > id formats into consideration and apply a specific action. > > 1) Currently this macro works fine if the format is either; 1234, 12345, > 00012345, 00123456 or 01234567. In the new macro I want the hyperlink to > be > the same as what it currently is written to create if it finds 1 of the 5 > noted id formats. > > 2) Another format that is found in this column is a url. If the macro > finds > this format I need the url to be retained in the hyperlink. > > 3) The 3rd format is an id that begins with WBT. In this case I need the > hyperlink to contain, http://grow.hp.com/Saba/Web/Main > Additionally, in this case an additional enhancement would be to add a > Comment to the cell with instructions on what to do once they are > redirected > to the url. > > 4) The final format is that the cell may contain the word Exam. In this > case > I don't want any hyperlink to be created. > > Any assistance in whole or in part would be greatly appreciated. Please > let > me know if any additional information is required. > > -- > Regards, > Chris

Thanks for the reply Don. Unfortunately, I'm not well versed enough in VB to fully understand how your code should be modified to accomplish what I need. If you wouldn't mind could you provide some additional information? Thanks. -- Regards, Chris "Don Guillett" wrote: > Why not a double click event that uses a cell value instead. I do this with > my menu sheet. > > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As > Boolean) > Application.DisplayAlerts = False > Dim WantedSheet As String > WantedSheet = Trim(ActiveCell.Value) > If WantedSheet = "" Then Exit Sub > On Error Resume Next > If Sheets(WantedSheet) Is Nothing Then > GetWorkbook ' calls another macro to do that > Else > Application.GoTo Sheets(WantedSheet).Range("a4") > End If > Application.DisplayAlerts = True > End Sub > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett@gmail.com > "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message > news:6D7234EF-C637-44DB-8D0A-91B8113FB353@microsoft.com... > > Hello, > > > > I currently have the following macro that converts text to a hyperlink. > > > > Sub CovertTxt2Hyperlink() > > Dim cCell As Range > > Dim strHLinkBase As String > > > > strHLinkBase = > > "http://grow.hp.com/Saba/loginAsUser.jsp?deepLinkName=CourseDetail&deepLinkParams=courseId=" > > > > For Each cCell In Selection.Cells > > > > If cCell.Hyperlinks.Count = 0 Then > > On Error Resume Next > > ActiveSheet.Hyperlinks.Add _ > > Anchor:=cCell, _ > > Address:=strHLinkBase & cCell.Value, _ > > TextToDisplay:=cCell.Value > > cCell.NumberFormat = "@" 'Format cell as text > > End If > > Next cCell > > End Sub > > > > It works great for the spreadsheets that I have used it on to date. > > However, > > I have received a new workbook that I will be receiving quarterly that > > will > > require modification to this macro to further automate the process of > > converting the cell content to a hyperlink. > > > > In this workbook I have 4 sheets; Welcome, Sales, Consulting & Suport. In > > the last 3 sheets there is a table that has a catalog id column beginning > > at > > D9. In the current macro I have to select the cells and then run the macro > > to > > convert the contents. I want to change the macro so that all of the cells > > in > > Column D, beginning at Row 9 in the Sales, Consulting & Support sheets > > will > > be converted when the macro is run. > > > > In addition to this change the macro needs to take several different > > Catalog > > id formats into consideration and apply a specific action. > > > > 1) Currently this macro works fine if the format is either; 1234, 12345, > > 00012345, 00123456 or 01234567. In the new macro I want the hyperlink to > > be > > the same as what it currently is written to create if it finds 1 of the 5 > > noted id formats. > > > > 2) Another format that is found in this column is a url. If the macro > > finds > > this format I need the url to be retained in the hyperlink. > > > > 3) The 3rd format is an id that begins with WBT. In this case I need the > > hyperlink to contain, http://grow.hp.com/Saba/Web/Main > > Additionally, in this case an additional enhancement would be to add a > > Comment to the cell with instructions on what to do once they are > > redirected > > to the url. > > > > 4) The final format is that the cell may contain the word Exam. In this > > case > > I don't want any hyperlink to be created. > > > > Any assistance in whole or in part would be greatly appreciated. Please > > let > > me know if any additional information is required. > > > > -- > > Regards, > > Chris > > . >

If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message news:7DD3CF45-18E0-4244-86CF-E95D7A681CCE@microsoft.com... > Thanks for the reply Don. Unfortunately, I'm not well versed enough in VB > to > fully understand how your code should be modified to accomplish what I > need. > If you wouldn't mind could you provide some additional information? > Thanks. > -- > Regards, > Chris > > > "Don Guillett" wrote: > >> Why not a double click event that uses a cell value instead. I do this >> with >> my menu sheet. >> >> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As >> Boolean) >> Application.DisplayAlerts = False >> Dim WantedSheet As String >> WantedSheet = Trim(ActiveCell.Value) >> If WantedSheet = "" Then Exit Sub >> On Error Resume Next >> If Sheets(WantedSheet) Is Nothing Then >> GetWorkbook ' calls another macro to do that >> Else >> Application.GoTo Sheets(WantedSheet).Range("a4") >> End If >> Application.DisplayAlerts = True >> End Sub >> -- >> Don Guillett >> Microsoft MVP Excel >> SalesAid Software >> dguillett@gmail.com >> "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message >> news:6D7234EF-C637-44DB-8D0A-91B8113FB353@microsoft.com... >> > Hello, >> > >> > I currently have the following macro that converts text to a hyperlink. >> > >> > Sub CovertTxt2Hyperlink() >> > Dim cCell As Range >> > Dim strHLinkBase As String >> > >> > strHLinkBase = >> > "http://grow.hp.com/Saba/loginAsUser.jsp?deepLinkName=CourseDetail&deepLinkParams=courseId=" >> > >> > For Each cCell In Selection.Cells >> > >> > If cCell.Hyperlinks.Count = 0 Then >> > On Error Resume Next >> > ActiveSheet.Hyperlinks.Add _ >> > Anchor:=cCell, _ >> > Address:=strHLinkBase & cCell.Value, _ >> > TextToDisplay:=cCell.Value >> > cCell.NumberFormat = "@" 'Format cell as text >> > End If >> > Next cCell >> > End Sub >> > >> > It works great for the spreadsheets that I have used it on to date. >> > However, >> > I have received a new workbook that I will be receiving quarterly that >> > will >> > require modification to this macro to further automate the process of >> > converting the cell content to a hyperlink. >> > >> > In this workbook I have 4 sheets; Welcome, Sales, Consulting & Suport. >> > In >> > the last 3 sheets there is a table that has a catalog id column >> > beginning >> > at >> > D9. In the current macro I have to select the cells and then run the >> > macro >> > to >> > convert the contents. I want to change the macro so that all of the >> > cells >> > in >> > Column D, beginning at Row 9 in the Sales, Consulting & Support sheets >> > will >> > be converted when the macro is run. >> > >> > In addition to this change the macro needs to take several different >> > Catalog >> > id formats into consideration and apply a specific action. >> > >> > 1) Currently this macro works fine if the format is either; 1234, >> > 12345, >> > 00012345, 00123456 or 01234567. In the new macro I want the hyperlink >> > to >> > be >> > the same as what it currently is written to create if it finds 1 of the >> > 5 >> > noted id formats. >> > >> > 2) Another format that is found in this column is a url. If the macro >> > finds >> > this format I need the url to be retained in the hyperlink. >> > >> > 3) The 3rd format is an id that begins with WBT. In this case I need >> > the >> > hyperlink to contain, http://grow.hp.com/Saba/Web/Main >> > Additionally, in this case an additional enhancement would be to add a >> > Comment to the cell with instructions on what to do once they are >> > redirected >> > to the url. >> > >> > 4) The final format is that the cell may contain the word Exam. In this >> > case >> > I don't want any hyperlink to be created. >> > >> > Any assistance in whole or in part would be greatly appreciated. Please >> > let >> > me know if any additional information is required. >> > >> > -- >> > Regards, >> > Chris >> >> . >>

Thanks Dan. I will put the file together and forward it to your email address. -- Regards, Chris "Don Guillett" wrote: > If desired, send your file to my address below. I will only look if: > 1. You send a copy of this message on an inserted sheet > 2. You give me the newsgroup and the subject line > 3. You send a clear explanation of what you want > 4. You send before/after examples and expected results. > > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett@gmail.com > "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message > news:7DD3CF45-18E0-4244-86CF-E95D7A681CCE@microsoft.com... > > Thanks for the reply Don. Unfortunately, I'm not well versed enough in VB > > to > > fully understand how your code should be modified to accomplish what I > > need. > > If you wouldn't mind could you provide some additional information? > > Thanks. > > -- > > Regards, > > Chris > > > > > > "Don Guillett" wrote: > > > >> Why not a double click event that uses a cell value instead. I do this > >> with > >> my menu sheet. > >> > >> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As > >> Boolean) > >> Application.DisplayAlerts = False > >> Dim WantedSheet As String > >> WantedSheet = Trim(ActiveCell.Value) > >> If WantedSheet = "" Then Exit Sub > >> On Error Resume Next > >> If Sheets(WantedSheet) Is Nothing Then > >> GetWorkbook ' calls another macro to do that > >> Else > >> Application.GoTo Sheets(WantedSheet).Range("a4") > >> End If > >> Application.DisplayAlerts = True > >> End Sub > >> -- > >> Don Guillett > >> Microsoft MVP Excel > >> SalesAid Software > >> dguillett@gmail.com > >> "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message > >> news:6D7234EF-C637-44DB-8D0A-91B8113FB353@microsoft.com... > >> > Hello, > >> > > >> > I currently have the following macro that converts text to a hyperlink. > >> > > >> > Sub CovertTxt2Hyperlink() > >> > Dim cCell As Range > >> > Dim strHLinkBase As String > >> > > >> > strHLinkBase = > >> > "http://grow.hp.com/Saba/loginAsUser.jsp?deepLinkName=CourseDetail&deepLinkParams=courseId=" > >> > > >> > For Each cCell In Selection.Cells > >> > > >> > If cCell.Hyperlinks.Count = 0 Then > >> > On Error Resume Next > >> > ActiveSheet.Hyperlinks.Add _ > >> > Anchor:=cCell, _ > >> > Address:=strHLinkBase & cCell.Value, _ > >> > TextToDisplay:=cCell.Value > >> > cCell.NumberFormat = "@" 'Format cell as text > >> > End If > >> > Next cCell > >> > End Sub > >> > > >> > It works great for the spreadsheets that I have used it on to date. > >> > However, > >> > I have received a new workbook that I will be receiving quarterly that > >> > will > >> > require modification to this macro to further automate the process of > >> > converting the cell content to a hyperlink. > >> > > >> > In this workbook I have 4 sheets; Welcome, Sales, Consulting & Suport. > >> > In > >> > the last 3 sheets there is a table that has a catalog id column > >> > beginning > >> > at > >> > D9. In the current macro I have to select the cells and then run the > >> > macro > >> > to > >> > convert the contents. I want to change the macro so that all of the > >> > cells > >> > in > >> > Column D, beginning at Row 9 in the Sales, Consulting & Support sheets > >> > will > >> > be converted when the macro is run. > >> > > >> > In addition to this change the macro needs to take several different > >> > Catalog > >> > id formats into consideration and apply a specific action. > >> > > >> > 1) Currently this macro works fine if the format is either; 1234, > >> > 12345, > >> > 00012345, 00123456 or 01234567. In the new macro I want the hyperlink > >> > to > >> > be > >> > the same as what it currently is written to create if it finds 1 of the > >> > 5 > >> > noted id formats. > >> > > >> > 2) Another format that is found in this column is a url. If the macro > >> > finds > >> > this format I need the url to be retained in the hyperlink. > >> > > >> > 3) The 3rd format is an id that begins with WBT. In this case I need > >> > the > >> > hyperlink to contain, http://grow.hp.com/Saba/Web/Main > >> > Additionally, in this case an additional enhancement would be to add a > >> > Comment to the cell with instructions on what to do once they are > >> > redirected > >> > to the url. > >> > > >> > 4) The final format is that the cell may contain the word Exam. In this > >> > case > >> > I don't want any hyperlink to be created. > >> > > >> > Any assistance in whole or in part would be greatly appreciated. Please > >> > let > >> > me know if any additional information is required. > >> > > >> > -- > >> > Regards, > >> > Chris > >> > >> . > >> > > . >

Hello Don, I just forwarded the email. My apologies for mistyping your name in my previous reply. -- Regards, Chris "eckert1961" wrote: > Thanks Don. I will put the file together and forward it to your email address. > -- > Regards, > Chris > > > "Don Guillett" wrote: > > > If desired, send your file to my address below. I will only look if: > > 1. You send a copy of this message on an inserted sheet > > 2. You give me the newsgroup and the subject line > > 3. You send a clear explanation of what you want > > 4. You send before/after examples and expected results. > > > > > > -- > > Don Guillett > > Microsoft MVP Excel > > SalesAid Software > > dguillett@gmail.com > > "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message > > news:7DD3CF45-18E0-4244-86CF-E95D7A681CCE@microsoft.com... > > > Thanks for the reply Don. Unfortunately, I'm not well versed enough in VB > > > to > > > fully understand how your code should be modified to accomplish what I > > > need. > > > If you wouldn't mind could you provide some additional information? > > > Thanks. > > > -- > > > Regards, > > > Chris > > > > > > > > > "Don Guillett" wrote: > > > > > >> Why not a double click event that uses a cell value instead. I do this > > >> with > > >> my menu sheet. > > >> > > >> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As > > >> Boolean) > > >> Application.DisplayAlerts = False > > >> Dim WantedSheet As String > > >> WantedSheet = Trim(ActiveCell.Value) > > >> If WantedSheet = "" Then Exit Sub > > >> On Error Resume Next > > >> If Sheets(WantedSheet) Is Nothing Then > > >> GetWorkbook ' calls another macro to do that > > >> Else > > >> Application.GoTo Sheets(WantedSheet).Range("a4") > > >> End If > > >> Application.DisplayAlerts = True > > >> End Sub > > >> -- > > >> Don Guillett > > >> Microsoft MVP Excel > > >> SalesAid Software > > >> dguillett@gmail.com > > >> "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message > > >> news:6D7234EF-C637-44DB-8D0A-91B8113FB353@microsoft.com... > > >> > Hello, > > >> > > > >> > I currently have the following macro that converts text to a hyperlink. > > >> > > > >> > Sub CovertTxt2Hyperlink() > > >> > Dim cCell As Range > > >> > Dim strHLinkBase As String > > >> > > > >> > strHLinkBase = > > >> > "http://grow.hp.com/Saba/loginAsUser.jsp?deepLinkName=CourseDetail&deepLinkParams=courseId=" > > >> > > > >> > For Each cCell In Selection.Cells > > >> > > > >> > If cCell.Hyperlinks.Count = 0 Then > > >> > On Error Resume Next > > >> > ActiveSheet.Hyperlinks.Add _ > > >> > Anchor:=cCell, _ > > >> > Address:=strHLinkBase & cCell.Value, _ > > >> > TextToDisplay:=cCell.Value > > >> > cCell.NumberFormat = "@" 'Format cell as text > > >> > End If > > >> > Next cCell > > >> > End Sub > > >> > > > >> > It works great for the spreadsheets that I have used it on to date. > > >> > However, > > >> > I have received a new workbook that I will be receiving quarterly that > > >> > will > > >> > require modification to this macro to further automate the process of > > >> > converting the cell content to a hyperlink. > > >> > > > >> > In this workbook I have 4 sheets; Welcome, Sales, Consulting & Suport. > > >> > In > > >> > the last 3 sheets there is a table that has a catalog id column > > >> > beginning > > >> > at > > >> > D9. In the current macro I have to select the cells and then run the > > >> > macro > > >> > to > > >> > convert the contents. I want to change the macro so that all of the > > >> > cells > > >> > in > > >> > Column D, beginning at Row 9 in the Sales, Consulting & Support sheets > > >> > will > > >> > be converted when the macro is run. > > >> > > > >> > In addition to this change the macro needs to take several different > > >> > Catalog > > >> > id formats into consideration and apply a specific action. > > >> > > > >> > 1) Currently this macro works fine if the format is either; 1234, > > >> > 12345, > > >> > 00012345, 00123456 or 01234567. In the new macro I want the hyperlink > > >> > to > > >> > be > > >> > the same as what it currently is written to create if it finds 1 of the > > >> > 5 > > >> > noted id formats. > > >> > > > >> > 2) Another format that is found in this column is a url. If the macro > > >> > finds > > >> > this format I need the url to be retained in the hyperlink. > > >> > > > >> > 3) The 3rd format is an id that begins with WBT. In this case I need > > >> > the > > >> > hyperlink to contain, http://grow.hp.com/Saba/Web/Main > > >> > Additionally, in this case an additional enhancement would be to add a > > >> > Comment to the cell with instructions on what to do once they are > > >> > redirected > > >> > to the url. > > >> > > > >> > 4) The final format is that the cell may contain the word Exam. In this > > >> > case > > >> > I don't want any hyperlink to be created. > > >> > > > >> > Any assistance in whole or in part would be greatly appreciated. Please > > >> > let > > >> > me know if any additional information is required. > > >> > > > >> > -- > > >> > Regards, > > >> > Chris > > >> > > >> . > > >> > > > > . > >

Basic idea presented in a worksheet event Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Column <> 4 Then Exit Sub mv = Target ActiveWorkbook.FollowHyperlink Address:= _ "http://finance.yahoo.com/q?s=" & mv End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message news:F7898F74-D2DD-4AED-B0B2-3A9CFF3214D0@microsoft.com... > Hello Don, > > I just forwarded the email. My apologies for mistyping your name in my > previous reply. > -- > Regards, > Chris > > > "eckert1961" wrote: > >> Thanks Don. I will put the file together and forward it to your email >> address. >> -- >> Regards, >> Chris >> >> >> "Don Guillett" wrote: >> >> > If desired, send your file to my address below. I will only look >> > if: >> > 1. You send a copy of this message on an inserted sheet >> > 2. You give me the newsgroup and the subject line >> > 3. You send a clear explanation of what you want >> > 4. You send before/after examples and expected results. >> > >> > >> > -- >> > Don Guillett >> > Microsoft MVP Excel >> > SalesAid Software >> > dguillett@gmail.com >> > "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message >> > news:7DD3CF45-18E0-4244-86CF-E95D7A681CCE@microsoft.com... >> > > Thanks for the reply Don. Unfortunately, I'm not well versed enough >> > > in VB >> > > to >> > > fully understand how your code should be modified to accomplish what >> > > I >> > > need. >> > > If you wouldn't mind could you provide some additional information? >> > > Thanks. >> > > -- >> > > Regards, >> > > Chris >> > > >> > > >> > > "Don Guillett" wrote: >> > > >> > >> Why not a double click event that uses a cell value instead. I do >> > >> this >> > >> with >> > >> my menu sheet. >> > >> >> > >> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, >> > >> Cancel As >> > >> Boolean) >> > >> Application.DisplayAlerts = False >> > >> Dim WantedSheet As String >> > >> WantedSheet = Trim(ActiveCell.Value) >> > >> If WantedSheet = "" Then Exit Sub >> > >> On Error Resume Next >> > >> If Sheets(WantedSheet) Is Nothing Then >> > >> GetWorkbook ' calls another macro to do that >> > >> Else >> > >> Application.GoTo Sheets(WantedSheet).Range("a4") >> > >> End If >> > >> Application.DisplayAlerts = True >> > >> End Sub >> > >> -- >> > >> Don Guillett >> > >> Microsoft MVP Excel >> > >> SalesAid Software >> > >> dguillett@gmail.com >> > >> "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message >> > >> news:6D7234EF-C637-44DB-8D0A-91B8113FB353@microsoft.com... >> > >> > Hello, >> > >> > >> > >> > I currently have the following macro that converts text to a >> > >> > hyperlink. >> > >> > >> > >> > Sub CovertTxt2Hyperlink() >> > >> > Dim cCell As Range >> > >> > Dim strHLinkBase As String >> > >> > >> > >> > strHLinkBase = >> > >> > "http://grow.hp.com/Saba/loginAsUser.jsp?deepLinkName=CourseDetail&deepLinkParams=courseId=" >> > >> > >> > >> > For Each cCell In Selection.Cells >> > >> > >> > >> > If cCell.Hyperlinks.Count = 0 Then >> > >> > On Error Resume Next >> > >> > ActiveSheet.Hyperlinks.Add _ >> > >> > Anchor:=cCell, _ >> > >> > Address:=strHLinkBase & cCell.Value, _ >> > >> > TextToDisplay:=cCell.Value >> > >> > cCell.NumberFormat = "@" 'Format cell as text >> > >> > End If >> > >> > Next cCell >> > >> > End Sub >> > >> > >> > >> > It works great for the spreadsheets that I have used it on to >> > >> > date. >> > >> > However, >> > >> > I have received a new workbook that I will be receiving quarterly >> > >> > that >> > >> > will >> > >> > require modification to this macro to further automate the process >> > >> > of >> > >> > converting the cell content to a hyperlink. >> > >> > >> > >> > In this workbook I have 4 sheets; Welcome, Sales, Consulting & >> > >> > Suport. >> > >> > In >> > >> > the last 3 sheets there is a table that has a catalog id column >> > >> > beginning >> > >> > at >> > >> > D9. In the current macro I have to select the cells and then run >> > >> > the >> > >> > macro >> > >> > to >> > >> > convert the contents. I want to change the macro so that all of >> > >> > the >> > >> > cells >> > >> > in >> > >> > Column D, beginning at Row 9 in the Sales, Consulting & Support >> > >> > sheets >> > >> > will >> > >> > be converted when the macro is run. >> > >> > >> > >> > In addition to this change the macro needs to take several >> > >> > different >> > >> > Catalog >> > >> > id formats into consideration and apply a specific action. >> > >> > >> > >> > 1) Currently this macro works fine if the format is either; 1234, >> > >> > 12345, >> > >> > 00012345, 00123456 or 01234567. In the new macro I want the >> > >> > hyperlink >> > >> > to >> > >> > be >> > >> > the same as what it currently is written to create if it finds 1 >> > >> > of the >> > >> > 5 >> > >> > noted id formats. >> > >> > >> > >> > 2) Another format that is found in this column is a url. If the >> > >> > macro >> > >> > finds >> > >> > this format I need the url to be retained in the hyperlink. >> > >> > >> > >> > 3) The 3rd format is an id that begins with WBT. In this case I >> > >> > need >> > >> > the >> > >> > hyperlink to contain, http://grow.hp.com/Saba/Web/Main >> > >> > Additionally, in this case an additional enhancement would be to >> > >> > add a >> > >> > Comment to the cell with instructions on what to do once they are >> > >> > redirected >> > >> > to the url. >> > >> > >> > >> > 4) The final format is that the cell may contain the word Exam. In >> > >> > this >> > >> > case >> > >> > I don't want any hyperlink to be created. >> > >> > >> > >> > Any assistance in whole or in part would be greatly appreciated. >> > >> > Please >> > >> > let >> > >> > me know if any additional information is required. >> > >> > >> > >> > -- >> > >> > Regards, >> > >> > Chris >> > >> >> > >> . >> > >> >> > >> > . >> >

Don's code worked brilliantly. -- Regards, Chris "Don Guillett" wrote: > Basic idea presented in a worksheet event > > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As > Boolean) > If Target.Column <> 4 Then Exit Sub > mv = Target > ActiveWorkbook.FollowHyperlink Address:= _ > "http://finance.yahoo.com/q?s=" & mv > End Sub > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett@gmail.com > "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message > news:F7898F74-D2DD-4AED-B0B2-3A9CFF3214D0@microsoft.com... > > Hello Don, > > > > I just forwarded the email. My apologies for mistyping your name in my > > previous reply. > > -- > > Regards, > > Chris > > > > > > "eckert1961" wrote: > > > >> Thanks Don. I will put the file together and forward it to your email > >> address. > >> -- > >> Regards, > >> Chris > >> > >> > >> "Don Guillett" wrote: > >> > >> > If desired, send your file to my address below. I will only look > >> > if: > >> > 1. You send a copy of this message on an inserted sheet > >> > 2. You give me the newsgroup and the subject line > >> > 3. You send a clear explanation of what you want > >> > 4. You send before/after examples and expected results. > >> > > >> > > >> > -- > >> > Don Guillett > >> > Microsoft MVP Excel > >> > SalesAid Software > >> > dguillett@gmail.com > >> > "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message > >> > news:7DD3CF45-18E0-4244-86CF-E95D7A681CCE@microsoft.com... > >> > > Thanks for the reply Don. Unfortunately, I'm not well versed enough > >> > > in VB > >> > > to > >> > > fully understand how your code should be modified to accomplish what > >> > > I > >> > > need. > >> > > If you wouldn't mind could you provide some additional information? > >> > > Thanks. > >> > > -- > >> > > Regards, > >> > > Chris > >> > > > >> > > > >> > > "Don Guillett" wrote: > >> > > > >> > >> Why not a double click event that uses a cell value instead. I do > >> > >> this > >> > >> with > >> > >> my menu sheet. > >> > >> > >> > >> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, > >> > >> Cancel As > >> > >> Boolean) > >> > >> Application.DisplayAlerts = False > >> > >> Dim WantedSheet As String > >> > >> WantedSheet = Trim(ActiveCell.Value) > >> > >> If WantedSheet = "" Then Exit Sub > >> > >> On Error Resume Next > >> > >> If Sheets(WantedSheet) Is Nothing Then > >> > >> GetWorkbook ' calls another macro to do that > >> > >> Else > >> > >> Application.GoTo Sheets(WantedSheet).Range("a4") > >> > >> End If > >> > >> Application.DisplayAlerts = True > >> > >> End Sub > >> > >> -- > >> > >> Don Guillett > >> > >> Microsoft MVP Excel > >> > >> SalesAid Software > >> > >> dguillett@gmail.com > >> > >> "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message > >> > >> news:6D7234EF-C637-44DB-8D0A-91B8113FB353@microsoft.com... > >> > >> > Hello, > >> > >> > > >> > >> > I currently have the following macro that converts text to a > >> > >> > hyperlink. > >> > >> > > >> > >> > Sub CovertTxt2Hyperlink() > >> > >> > Dim cCell As Range > >> > >> > Dim strHLinkBase As String > >> > >> > > >> > >> > strHLinkBase = > >> > >> > "http://grow.hp.com/Saba/loginAsUser.jsp?deepLinkName=CourseDetail&deepLinkParams=courseId=" > >> > >> > > >> > >> > For Each cCell In Selection.Cells > >> > >> > > >> > >> > If cCell.Hyperlinks.Count = 0 Then > >> > >> > On Error Resume Next > >> > >> > ActiveSheet.Hyperlinks.Add _ > >> > >> > Anchor:=cCell, _ > >> > >> > Address:=strHLinkBase & cCell.Value, _ > >> > >> > TextToDisplay:=cCell.Value > >> > >> > cCell.NumberFormat = "@" 'Format cell as text > >> > >> > End If > >> > >> > Next cCell > >> > >> > End Sub > >> > >> > > >> > >> > It works great for the spreadsheets that I have used it on to > >> > >> > date. > >> > >> > However, > >> > >> > I have received a new workbook that I will be receiving quarterly > >> > >> > that > >> > >> > will > >> > >> > require modification to this macro to further automate the process > >> > >> > of > >> > >> > converting the cell content to a hyperlink. > >> > >> > > >> > >> > In this workbook I have 4 sheets; Welcome, Sales, Consulting & > >> > >> > Suport. > >> > >> > In > >> > >> > the last 3 sheets there is a table that has a catalog id column > >> > >> > beginning > >> > >> > at > >> > >> > D9. In the current macro I have to select the cells and then run > >> > >> > the > >> > >> > macro > >> > >> > to > >> > >> > convert the contents. I want to change the macro so that all of > >> > >> > the > >> > >> > cells > >> > >> > in > >> > >> > Column D, beginning at Row 9 in the Sales, Consulting & Support > >> > >> > sheets > >> > >> > will > >> > >> > be converted when the macro is run. > >> > >> > > >> > >> > In addition to this change the macro needs to take several > >> > >> > different > >> > >> > Catalog > >> > >> > id formats into consideration and apply a specific action. > >> > >> > > >> > >> > 1) Currently this macro works fine if the format is either; 1234, > >> > >> > 12345, > >> > >> > 00012345, 00123456 or 01234567. In the new macro I want the > >> > >> > hyperlink > >> > >> > to > >> > >> > be > >> > >> > the same as what it currently is written to create if it finds 1 > >> > >> > of the > >> > >> > 5 > >> > >> > noted id formats. > >> > >> > > >> > >> > 2) Another format that is found in this column is a url. If the > >> > >> > macro > >> > >> > finds > >> > >> > this format I need the url to be retained in the hyperlink. > >> > >> > > >> > >> > 3) The 3rd format is an id that begins with WBT. In this case I > >> > >> > need > >> > >> > the > >> > >> > hyperlink to contain, http://grow.hp.com/Saba/Web/Main > >> > >> > Additionally, in this case an additional enhancement would be to > >> > >> > add a > >> > >> > Comment to the cell with instructions on what to do once they are > >> > >> > redirected > >> > >> > to the url. > >> > >> > > >> > >> > 4) The final format is that the cell may contain the word Exam. In > >> > >> > this > >> > >> > case > >> > >> > I don't want any hyperlink to be created. > >> > >> > > >> > >> > Any assistance in whole or in part would be greatly appreciated. > >> > >> > Please > >> > >> > let > >> > >> > me know if any additional information is required. > >> > >> > > >> > >> > -- > >> > >> > Regards, > >> > >> > Chris > >> > >> > >> > >> . > >> > >> > >> > > >> > . > >> > > > . >

Similiar Articles:

Help Modifying Hyperlink Macro - microsoft.public.excel ...
Hello, I currently have the following macro that converts text to a hyperlink. Sub CovertTxt2Hyperlink() Dim cCell As Range Dim strHLinkBase A...

How do I create a hyperlink with a macro? - microsoft.public.excel ...
This modified version of his sample code worked ok for me: Option ... Assign macro to a hyperlink - Excel Help Forum Hi, How do I assign a macro to a hyperlink?

macro for listing content of directory - microsoft.public.word.vba ...
I can't modify the code myself, Your help will be appreciated. ... Number of multimedia clips, Hyperlink base ... Directory Listing - I have a macro... - Free Excel Help ...

Can I mass-change macro paths of tool buttons? - microsoft.public ...
... If you want to learn about modifying the ribbon ... Help with follow hyperlink method - microsoft.public.access ... Outlook 2000 and 2002 Hyperlink and Macro Buttons Tools ...

Hyperlink and dialog box - microsoft.public.access
Can anyone help with this... I want double click on a hyperlink address box to ... Create hyperlink via macro? Word 2007 ... access.forms Create or modify a hyperlink When ...

How can I assign a macro to a button in all documents? - microsoft ...
A little help? Thanks! -- mlse ... selection of icons Word offers when you click Modify ... How to I assign a VB Macro to a hyperlink or can I? - microsoft ...

Insert hyperlink off and on ? - microsoft.public.outlook.general ...
Create hyperlink via macro? Word 2007 - microsoft ... I read on MSFT's help page that, "You ... file or Web page. You can also modify the formatting or remove that hyperlink.

Problem with a macro and rectangle - microsoft.public.excel ...
Create Hyperlink in VBA - microsoft.public ... Excel - How To Modify Size Of A Drawn ... the size of rectangle. i need help in ... I would like to run a macro that ...

Word & hyperlinks - microsoft.public.word.docmanagement ...
... there, I'm kind of a novice, so any help is appreciated. I'm trying to create a macro ... file or Web page. You can also modify the formatting or remove that hyperlink.

Accessing a linked file in a Word document using VBA - microsoft ...
... filename as well (interactively), you could modify the macro ... apartment.gif Now if you are asking about a hyperlink ... sorts of workarounds here and appreciate your help ...

Excel - Modifying Hyperlink Variable In Vba - Hi all I have ...
Modifying Hyperlink Variable In Vba - Hi all I have created a list of about 3000 ... Can anyone help me with a macro that would replace the first five characters ...

XL2000: How to Programmatically Modify Hyperlink Addresses on a ...
XL2000: How to Programmatically Modify Hyperlink Addresses ... Microsoft support engineers can help explain the ... On the Tools menu, point to Macro, and then click ...

Excel - Hyperlink Macro - Hi Everybody ... - Free Excel Help
Hyperlink Macro ... What i want to know is if i can modify this so ... I have a macro in excel to help users to print workbooks in a ...

Create or modify a hyperlink
You can also use this dialog box to modify the text that contains a hyperlink and to add a ScreenTip that appears when the user's pointer is over a hyperlink.

7/15/2012 4:39:14 AM