Regular sub works, SelectionChange errors

[just_jon]

There is a simple explanation here, it just escapes me. The code below checks to see if the current selection is in E:E and is a constant. If so, then find a matching value on a second sheet and convert the cell to a hyperlink to that 2nd sheet's cell.

If I comment out the Private sub line and un-comment the next 2, it runs like a champ, but I get Runtime error 13, Type mismatch on the i = Application... line when run as an event macro.

What am I doing wrong?

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Excel.Range)
<SPAN style="color:#007F00">'Sub foo()</SPAN>
<SPAN style="color:#007F00">'Set Target = [E5]</SPAN>
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, [E:E].SpecialCells(xlCellTypeConstants)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> WS <SPAN style="color:#00007F">As</SPAN> Worksheet, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, CA <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> WS = Worksheets("Training Log")
    i = Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Match(Target.Value, WS.[D:D], 0))
    <SPAN style="color:#00007F">If</SPAN> (i) <SPAN style="color:#00007F">Then</SPAN>
        CA = Application.WorksheetFunction.Index(WS.[D:D], _
             Application.WorksheetFunction.Match(Target.Value, WS.[D:D], 0), 0).Address
        Target.Formula = "=HYPERLINK(""[" & ActiveWorkbook.Name & "]'Training Log'!" & CA & """,""" & CA & """)"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> WS = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

As always, thanks for looking!

 

[Greg Truby]

Jon,

That's an interesting situation. Question, what happens if you Dim i as a Variant instead of Boolean? Does it still blow up at the same spot?

 

[just_jon]

Jon,

That's an interesting situation. Question, what happens if you Dim i as a Variant instead of Boolean? Does it still blow up at the same spot?

'Fraid it does.

 

[Greg Truby]

I'm getting this to work just fine as an event handler - if I put in an On Error Resume Next in front of the SpecialCells call.

 

[just_jon]

Hmmmm.. OK, I added on error resume next, but now as an event it fills in more than it should.

Got a minute, Greg to test this w/ the same 5-cell data as I am using?

 

[Greg Truby]

Okay - "just fine" was an overstatement... If I enter an item on the list in column E that is on the list in D in Training Log, it puts in a nice hyperlink. But, if I put in a value that appears on the list in any other cell, the macro replaces that with a hyperlink if move off and then back in the cell, even though the cell is not in column E. If I change the test to:

If Not Intersect(Target, [E:E]) Is Nothing _
And Target.Value = Target.Formula Then

it works a lot better.

{Edit - the previous text didn't read very clearly}

 

[just_jon]

That's got it, Greg. Thank you! jon

 

[Greg Truby]

Great! Just curious, why did you opt to put in the address as the "friendly name" instead of:

Target.Formula = "=HYPERLINK(""[" & ActiveWorkbook.Name & "]'Training Log'!" & CA & """,""" & Target.Value & """)"

 

[just_jon]

It was the OP's request, as I understood it. Might be wrong, but it seemed pretty clear:

with the cell reference as a title

 

[Tom Urtis]

I answered a question last night like this; here's how I did it:
http://www.mrexcel.com/board2/viewtopic.php?p=486161&highlight=#486161