VB: How to call another code for Follow HYPERLINK

Question

hi,  i have been working on a UDF for a custom hyperlink & have it working pretty well.  the only problem is the formula in cell is lower case instead of dsired Ucase:   clink("friendly name")

i am not sure how to call multple pages to open with a new customHYPELRINK()  being called:  CLINK()

able to have multiple pages open for an old method that uses:  goFHL()   (follow hyperlink,  sample at bottom / below)

would guesse the fix to be on how it is called?  or do i need a different goFHL()  /  don't think need a split,  the new custom hyperlink is to just have a friendly name.  thanks in advance.

i think the problem might just be in the section below marked with double lines / equal signs.

do not know if using the correct techniques, for just clicking on a cell in a column i have the hyperlink working.

i have code for when select a cell in a row (for specific column),  vb will activate.  i want to be able to call multiple copies of CLINK to open.  (each page will have a variation).  eg:  normal click / first page will be:  CLINK1,  others might be CLINK2  CLINK3..

guesse should inlude instructions:

'instructions:  1. call as:  =CustomHyperlink(A1)   change to:   call as:  =CustomHyperlink("friendly name")

'2. create hyperlink in cell: rc, hyperlink (pick any hyperlink valid or invalid) 

3. put friendly name in cell eg:  "T"  or:  =CustomHyperlink(Left(B5,1))
4. if result has some cell attached to end of hyperlink, rclick, hyperlink & remove eg:  #A1 from the end, before you paste it down a column

Function CLINK1(strFriendlyText As String) As Variant   'works for call as:  CLINK1("T")  or  CLINK1(LEFT(B9,1))

'Function CustomHyperlink(Term As String) As String    'original

    Dim B5 As String    'workcell shows:  BH:BH

    B5 = RANGE("B5")

    Dim rng As RANGE

    Set rng = Application.Caller

    CLINK1 = strFriendlyText    'yes, works

    'CustomHyperlink = Term    'original

    If rng.HYPERLINKS.Count > 0 Then

      rng.HYPERLINKS(1).Address = "http://www.google.com/search?q=" & Cells(ActiveCell.row, B5).Value  '<< eg:  works so can change the output with B5..

      'rng.HYPERLINKS(1).Address = "http://www.google.com/search?q=" & Term   'orig

    End If

End Function

i have setting for goOPEN0  to open 2 web page copies  (goal / what doing: have variatiion of same web page open up).  This is working for use with a =hyperlink.  need to modify it for using a logical / by VB custom hyperlink.  you might be able to skip ahead to the problem section below.

Private Sub Worksheet_SelectionChange(ByVal Target As RANGE)

Set myRange = Me.RANGE(J6).EntireColumn    'activates:

If Target.Cells.Count = 1 And Not Intersect(Target, myRange) Is Nothing And Left(Target.Formula, 6) = "=CLINK" Then  

    goOPEN0      '<<  ONLY 1 web page opening,  need to open 2 AND be able to manipulate with: CLINK,  the path this follows is a couple of items down..  sorry for the detail.  might just need a 1 line fix somewhere.  you can skip ahead

end if

end sub

Sub goOPEN0()       'web page quantity control

    Dim M3 As String

    M3 = RANGE("M3")

    If RANGE(M3).Value = 2 Then

      goOPEN2

    Else

      goOPEN1

    End If

    Application.EnableEvents = True             'EVENTS

End Sub

'==========    'PROBLEM AREA ??  going from using a HYPERLINK,  to using a UDF for a custom Hypelink:  CLINK()

Sub goOPEN2()  

    Application.EnableEvents = False          'EVENTS  need ck

    'Call CLINK

    'ActiveWorkbook.FollowHyperlink Address:=CLINK   'err:  argument not optional    '<<  NO, FIX <<

    goFHL ActiveCell.Formula, ",IF(P"         'sub2  HYPERLINK  (left of friendly name: "")    yes, original works for hyperlink

    goTIMER (1)

    'Call CLINK

    'ActiveWorkbook.FollowHyperlink Address:=CLINK

    goFHL ActiveCell.Formula, ",IF(P"         'sub2  HYPERLINK  (left of friendly name: "")

    'Application.EnableEvents = True          'EVENTS

End Sub

'==========

if this portion matters for:  FOLLOW HYPELRINK,  else is there just some other call i need to do?

Sub goFHL(strF As String, strDL As String)    '255 chars AS:  goFHL ActiveCell.Formula, ",friendlyname"

    Dim V As Variant

    If InStr(1, strF, "HYPERLINK") = 0 Or InStr(1, strF, strDL) = 0 Then

      Application.EnableEvents = True         'EVENTS

      Exit Sub

    End If

    V = split(strF, strDL)        'SPLIT NO LONGER NEEDED,  for my usage?

    On Error Resume Next

    ActiveWorkbook.FollowHyperlink Application.Evaluate(Replace(V(0), "HYPERLINK(", ""))  'intermit err: cannot follow link..

'HYPERLINK  (left of friendly name: ",eg comma anyname")

    Application.EnableEvents = True           'EVENTS

End Sub

Sub goTIMER(NumOfSeconds As Long) 'in (seconds eg: 0.5) as:  gotimer (1)  'seconds

    Application.wait now + NumOfSeconds / 86400#

    Application.EnableEvents = True           'EVENTS

End Sub

Answer 1

hi,  if i understand the problem well enough / not sure if this is the problem:

i currently have a script that splits hyperlinks so can follow the hyperlink thru vb. 

it seems that is not enough if want to manipulate the hyperlink as well

which to say:  want to put the hyperlink itself into code so can disect the web address & make different variations of the destination occur.

what trying to use is a udf for:   =CLINK("friendly name")    so the vb for calling a split hyperlink will not work.

the code for following a hyperlink by vb i have is:

goFHL ActiveCell.Formula, ",friendlyname"

that runs the macro:    (which will split off the friendly name:  ",IF(P612>0" etc,  in the next example below.

Sub goFHL(strF As String, strDL As String)

so by vb to run the hyperlink in a worksheet: 

=HYPERLINK($BM$4&BH9,IF(P612>0,"O","F"))

i would use this vb:

goFHL ActiveCell.Formula, ",IF(P"

==========

The question would then seem to be how to follow a hyperlink that has been put together by vb,  for example used is a custom hyperlink being called like:  function CLINK(strFriendlyname)

by the instructions for that code,  a logical? / in document? / hyperlink exists in the cell by right click cell, hyperlink,  (and per a NON-requirement:  select any hyperlink) - where the code will run any link item variables supplied.

QUESTION:

this method allows a click on the cell to follow the hyperlink.  i am not sure /  how to write code to inititate follow the hyperlink..  thru code.

    Call CLINK      'does not seem to work

if haven't said,  the code i have for following a custom hyperlink will allow me to make changes in any portions for eg:  LINK1 LINK2 etc below:

  link1 being:    "http://www.google.com/search?q="

  link2 being:    Cells(ActiveCell.row, B5).Value    '(i have these already working in my workbook)

'HOW DO I CALL THIS MACRO..  TO RUN IN ANOTHER VB?  thanks:

    Call CLINK      'does not seem to work

Function CLINK(strFriendlyText As String) As Variant  'i have this macro working

'Function CustomHyperlink(Term As String) As String  'orig

    Dim B5 As String    'workcell shows:  BH:BH

    B5 = RANGE("B5")

    Dim rng As RANGE

    Set rng = Application.Caller

    CustomHyperlink = strFriendlyText

    'CustomHyperlink = Term

    If rng.HYPERLINKS.Count > 0 Then

      rng.HYPERLINKS(1).Address = "http://www.google.com/search?q=" & Cells(ActiveCell.row, B5).Value  '<< my modification

      'rng.HYPERLINKS(1).Address = "http://www.google.com/search?q=" & Term   'orig

    End If

End Function

'instructions for the above:  1. call as:  =CLINK("friendly name")    original:  =CustomHyperlink(A1)

'2. create hyperlink in cell: rc, hyperlink (pick any hyperlink valid or invalid)

'3. put friendly name in cell eg:  "T"  or:  =CustomHyperlink(Left(H9,1))

'4. if result has some cell attached to end of hyperlink, rclick, hyperlink & remove eg:  #A1 at end of link, before you paste it down a column

'old: 3. put required search term in formula eg:  A1

'==========

if anyone needs the example i was given for calling a normal hyperlink,  it is:

Sub goFHL(strF As String, strDL As String)    'GOFHLXXX gofhlxxx  255 chars AS:  goFHL ActiveCell.Formula, ",friendlyname"

    Dim V As Variant

    If InStr(1, strF, "HYPERLINK") = 0 Or InStr(1, strF, strDL) = 0 Then

      Application.EnableEvents = True         'EVENTS

      Exit Sub

    End If

    V = split(strF, strDL)

    On Error Resume Next

    ActiveWorkbook.FollowHyperlink Application.Evaluate(Replace(V(0), "HYPERLINK(", ""))

    'HYPERLINK  (left of friendly name: ",eg comma anyname")

    Application.EnableEvents = True           'EVENTS

End Sub