Unbind Application.OnKey 3

[ribsa3]

I'm trying to declare a keybinding to only work when manipulating a certain range of cells in a sheet. However, Application.OnKey seems to globally and permanently bind the key combination until the sheet is reopened.

With that, I might ask two questions:

I am monitoring a set of cells in the system subroutine Workbook_SheetChange with an If Target.Column is greater/less than a couple of values and Target.Cell is greater/less than a set of other values. This works fine, but is there a way to use an If statement to monitor changes in an entire range of cells other than:

If Selection.Column >= 2 And Selection.Column <= 6 And Selection.Row <= 105 And Selection.Row >= 10 Then

And then finally - what I am trying to accomplish is:

1: If the user selects a range of cells within the given range, write/update the range in text to Cells(2,2).

2: If the current target cell is within the defined range, pressing Shift F2 (+{F2}) executes a custom function. Being outside the range, Shift F2 should add a cell comment as per default. Here, I could optionally unbind Shift-F2 at the end of the custom function, and problem solved.

This was a bit of a rant, but I would greatly appreciate any creative suggestions

Kindly,
Bjoern Sandvik

 

[PHV]

You may take a look at the Intersect method.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Loomah]

Hi
Looks like the Intersect method, as PHV suggested, would be the way to go. But I would also strongly recommend using a named range rather than
Selection.Column >= 2 And Selection.Column <= 6 And Selection.Row <= 105 And Selection.Row >= 10

Eg
using a range named MyRng this is the type of code you could end up with

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("myrng")) Is Nothing Then
    MsgBox "Selection is in ""MyRng"""
    Application.OnKey "+{F2}", "MyProc"
Else
    MsgBox "Selection outside ""MyRng"""
    Application.OnKey "+{F2}"
End If
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[TonyJollans]

hI ribsa3,

Application.OnKey is a Method of the Application and, thus, affects the whole application ('globally'). This means that if the User switches workbooks the setting is still in effect (you could put some code in the activate/deactivate events to deal with this, but it seems a bit complex).

OnKey is not permanent, though; it lasts until it is reset. To reset a key (combination) to the default just issue the OnKey with a null second parameter ..

[blue]Application.OnKey "+{F2}"[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.