Fordraiders
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"
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
CHRIS, its firing off to the keyhook, but just not firing off the function...???
ASKER
If target.Column = "" <--- a mistake in code...
ASKER
chris, my bad...Application.OnKey "+~", "mfSearchResult"
to
xlApp.OnKey "+~", "mfSearchResult"
to
xlApp.OnKey "+~", "mfSearchResult"
ASKER
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.Na me) = 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
If xlApp.ActiveWorkbook.Name = wName$ And xlApp.ActiveSheet.Name = wNameSheet$ Then
with
If lcase(xlApp.ActiveWorkbook
A bit more processing but more resilient when it comes to typos in the caps for a workbook or string name
Chris
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