Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

add-in macro not firing on key combination

excel 2003 add-in .xla

mRangeColumn is a public variable defind in earlier code

the sub "KeyHook"
will not initiate the macro mfSearchResult
when this key combination is chosen <shift> +<enter>

Application.OnKey "+~", "mfSearchResult"







Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal target As Range)
If xlApp.ActiveWorkbook.Name = wName$ And xlApp.ActiveSheet.Name = wNameSheet$ Then

On Error Resume Next
If target.Column = "" Then
If target.Column = mRangeColumn Then
        KeyHook
    Else
        KeyUnhook
    End If
    KeyUnhook
End If


End If
End Sub


Public Sub KeyHook()
        Application.OnKey "+~", "mfSearchResult"
End Sub
Public Sub KeyUnhook()
    Application.OnKey "+~"
End Sub


Public Function mfSearchResult()

' routine to run

End Function

Open in new window

Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

When you click shift  enter you want to fire

What about wName$ And  wNameSheet$ are these the same as the sheet name and workbook name in re case since you do not check case and finally, (for me)

    If target.Column = mRangeColumn Then
        KeyHook
    Else
        KeyUnhook
    End If
    KeyUnhook

i.e. after setting the key combo the last thing you do is unset it!

Chris
ASKER CERTIFIED SOLUTION
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Fordraiders

ASKER

CHRIS, its firing off to the keyhook, but just not firing off the function...???

If target.Column = ""    <--- a mistake in code...
chris, my bad...Application.OnKey "+~", "mfSearchResult"
to

xlApp.OnKey "+~", "mfSearchResult"
thanks
APologies for the delay but i've been out so thank you for the closure ... and when i mentioned the case of the names I should have suggested replacing:


    If xlApp.ActiveWorkbook.Name = wName$ And xlApp.ActiveSheet.Name = wNameSheet$ Then
with
    If lcase(xlApp.ActiveWorkbook.Name) = lcase(wName$) And lcase(xlApp.ActiveSheet.Name) = lcase(wNameSheet$) Then

A bit more processing but more resilient when it comes to typos in the caps for a workbook or string name

Chris