Hyperlinker
Hyperlinker
Hyperlinker
Hyperlinker
Hyperlinker
Hyperlinker Hyperlinker Hyperlinker Hyperlinker Hyperlinker Hyperlinker Hyperlinker Hyperlinker
Hyperlinker Hyperlinker
Hyperlinker
Go Back  Xtreme Visual Basic Talk > > > > Hyperlinker


Reply
 
Thread Tools Display Modes
  #1  
Old 01-27-2017, 01:48 PM
mogilshik's Avatar
mogilshik mogilshik is offline
Junior Contributor
 
Join Date: Aug 2003
Location: Saint-Petersburg
Posts: 366
Default Hyperlinker


Hello dear friends!
I wonder if you can help me create a small macro which will automatically place HYPERLINKS into cells which contain predefined KEY TEXT values – based on the existing list of KEY TEXT and HYPERLINK relationships. I need this macro to work on a single worksheet.
Here is an example:
Let’s say I have this text in a cell – “Mazda dealers“ or “Mazda cars“ or “Mazda contacts“ – each of these cells because each contains the key word Mazda would be assigned with the link MAZDA MOTOR CORPORATION GLOBAL WEBSITE
Do you think such macro can be made? I have attached the workbook with the example KEY words and text cells. The list of the TEXT values and the corresponding HYPERLINKS can be found in tab LINKS.
Thanking you,
Dorian
Attached Files
File Type: zip hyperlinker.zip (7.3 KB, 2 views)
__________________
Look past them as though there's something there which is distracting you. When they turn around to see what it is, hit them on the head with an esky lid. Pretend it fell from a tree, even if there are no trees around
"...21 Fun Things To Do Or Say When Your Being Yelled At.."
Reply With Quote
  #2  
Old 01-27-2017, 08:34 PM
Kluz's Avatar
KluzHyperlinker Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

Please try recording a macro of creating one of these hyperlinks and see if you can edit the code to do what you desire. If you have further questions about editing your code please post it.
__________________
No the other right mouse click
Reply With Quote
  #3  
Old 01-28-2017, 01:49 AM
mogilshik's Avatar
mogilshik mogilshik is offline
Junior Contributor
 
Join Date: Aug 2003
Location: Saint-Petersburg
Posts: 366
Default

Quote:
Originally Posted by Kluz View Post
Please try recording a macro of creating one of these hyperlinks and see if you can edit the code to do what you desire. If you have further questions about editing your code please post it.
Thank you - I wonder if there is a manual way to assign certain hyperlink to all cells containing a KEY STRING?
__________________
Look past them as though there's something there which is distracting you. When they turn around to see what it is, hit them on the head with an esky lid. Pretend it fell from a tree, even if there are no trees around
"...21 Fun Things To Do Or Say When Your Being Yelled At.."
Reply With Quote
  #4  
Old 01-30-2017, 07:59 AM
MPiHyperlinker MPi is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Dec 2001
Location: Quebec
Posts: 1,000
Default

You may try something like this

Code:
Sub HLinks()
    Dim I As Long, nRows As Long, nCols As Long
    Dim LinkLines As Long
    Dim strName As String, strLink As String
    Dim Cell As Range
    Dim Rng As Range
    
    nRows = Sheets("TEXT").Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row
    nCols = Sheets("TEXT").Cells.Find("*", Range("A1"), , , xlByColumns, xlPrevious).Column
    
    Set Rng = Sheets("TEXT").Range(Cells(1, 1), Cells(nRows, nCols))
    
    LinkLines = Sheets("LINKS").Cells(Rows.Count, "A").End(xlUp).Row
    
    For I = 1 To LinkLines
        strName = Sheets("LINKS").Range("A" & I)
        strLink = Sheets("LINKS").Range("B" & I)
        
        For Each Cell In Rng
            If InStr(1, LCase(Cell.Text), LCase(strName)) > 0 Then
                Sheets("TEXT").Hyperlinks.Add Anchor:=Cell, _
                    Address:=strLink, _
                    TextToDisplay:=Cell.Text
            End If
        Next
    Next
End Sub
__________________
MPi˛
Reply With Quote
  #5  
Old 02-22-2017, 07:30 AM
mogilshik's Avatar
mogilshik mogilshik is offline
Junior Contributor
 
Join Date: Aug 2003
Location: Saint-Petersburg
Posts: 366
Default

thank you very much! - let me check this!)
__________________
Look past them as though there's something there which is distracting you. When they turn around to see what it is, hit them on the head with an esky lid. Pretend it fell from a tree, even if there are no trees around
"...21 Fun Things To Do Or Say When Your Being Yelled At.."
Reply With Quote
Reply

Tags
text, key, macro, “mazda, cells, list, values, hyperlinks, words, contacts“, tab, links, dealers“, found, word, cars“, mazda, dorian, thanking, http://www.mazda.com, assigned, link, attached, workbook, automatically


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Hyperlinker
Hyperlinker
Hyperlinker Hyperlinker
Hyperlinker
Hyperlinker
Hyperlinker Hyperlinker Hyperlinker Hyperlinker Hyperlinker Hyperlinker Hyperlinker
Hyperlinker
Hyperlinker
 
Hyperlinker
Hyperlinker
 
-->