Solved: onkey event, toggle?

[mperrah]

I have this onkey event load when the work book opens.
The key event seem to slow down the application.
Also I have had a hard time targeting the range IO want the onkey event to be affected by.
I developed a way to replace the keys how I wan't, but
I would like to be able to toggle the sub on and off.
Does the onkey event have to stay on?
or can I use an If statement with a cell value on or off.

[VBA]
' this is on the workbook_open
Application.OnKey "1", "action_p"
Application.OnKey "2", "action_f"
Application.OnKey "3", "action_n"
Application.OnKey "4", "action_"
' this is in a standard module
Option Explicit
Sub action_p()
Dim onkeyset As Range
Set onkeyset = Worksheets("Data").Range("BM1")
If onkeyset.Value = "OFF" Then
Exit Sub
ElseIf onkeyset.Value = "ON" Then
If Selection.Count > 1 Then Exit Sub
Call insert_letter("p")
End If
End Sub
Sub action_f()
Dim onkeyset As Range
Set onkeyset = Worksheets("Data").Range("BM1")
If onkeyset.Value = "OFF" Then
Exit Sub
ElseIf onkeyset.Value = "ON" Then
If Selection.Count > 1 Then Exit Sub
If Selection.Count > 1 Then Exit Sub
Call insert_letter("f")
End If
End Sub
Sub action_n()
Dim onkeyset As Range
Set onkeyset = Worksheets("Data").Range("BM1")
If onkeyset.Value = "OFF" Then
Exit Sub
ElseIf onkeyset.Value = "ON" Then
If Selection.Count > 1 Then Exit Sub
If Selection.Count > 1 Then Exit Sub
Call insert_letter("n")
End If
End Sub
Sub action_()
Dim onkeyset As Range
Set onkeyset = Worksheets("Data").Range("BM1")
If onkeyset.Value = "OFF" Then
Exit Sub
ElseIf onkeyset.Value = "ON" Then
If Selection.Count > 1 Then Exit Sub
If Selection.Count > 1 Then Exit Sub
Call insert_letter("")
End If
End Sub
Sub insert_letter(vletter As String)
If ActiveSheet.Name = "Data" Then
' ' if not inside target - on target sheet
If ActiveCell.Column < 18 _
Or ActiveCell.Column > 44 _
Or ActiveCell.Row > 1000 Then
If vletter = "p" Then
vletter = "1"
ElseIf vletter = "f" Then
vletter = "2"
ElseIf vletter = "n" Then
vletter = "3"
ElseIf vletter = "" Then
vletter = "4"
End If
ActiveCell.Value = vletter
SendKeys "{F2}"
Else
' if not outside target
ActiveCell.Value = vletter
ActiveCell.Offset(, 1).Select
End If
Else
' if not active sheet data
If vletter = "p" Then
vletter = "1"
ElseIf vletter = "f" Then
vletter = "2"
ElseIf vletter = "n" Then
vletter = "3"
ElseIf vletter = "" Then
vletter = "4"
End If
ActiveCell.Value = vletter
SendKeys "{F2}"
End If
End Sub

[/VBA]
in worksheet("Data").range("BM1")
I have a datavalidation list with on or off

If I can move the workbook open to a standard module would help, but is there a way to speed up the functioning?

thanks in advance
Mark

[Simon Lloyd]

You use the function of the OnKey like this

Application.OnKey "1", "action_p"

Disable it like this

Application.OnKey "1", ""

and reset it like this

Application.OnKey "1"

or so i am led to believe, why not put the code in a standard module and use the Worksheet_Change to call the procedure?

[mperrah]

This is working, Thank you
I got the onkey from this forum and wasn't sure what I could change.
Would a case select work faster than and If statement?
If they type 1,2,3 or for I change to p,f,n or (space)
I use 4 if's to check what key was typed,
Do you think case select would work more quickly?

[VBA] If Target.Column = 65 _
And Target.Row = 1 Then
If Target.Value = "ON" Then
Application.OnKey "1", "action_p"
Application.OnKey "2", "action_f"
Application.OnKey "3", "action_n"
Application.OnKey "4", "action_"

ElseIf Target.Value = "OFF" Then

Application.OnKey "1"
Application.OnKey "2"
Application.OnKey "3"
Application.OnKey "4"
End If
End If[/VBA]

[Bob Phillips]

It is unlikley that there would be any discernible difference between If ... ElseIf ... End If, and Select Case ... Case ... End case, so it really comes down too a matter of style. Which do you prefere, which do you think reads better?

[mperrah]

thank you.
My app just seems to lag when I type within the target area, trying to utilize the onkey event.
would setting calculation to manual help in this?
It did wonders for other parts of my project.
Also, I would like the onkey event only captured within a specific target and only on one sheet.
The onkey event seems to affect the entire workbook.
I have the code working in the target area,
found a way to capture keys in other areas and replace correctly,
but can Excel only scan and capture onkey within a target?
Worksheets("Data").range("R3:AR1000")
is the only area I need this to work...
so typing out of this area, onkey should be normal - not even scanned
within the target - scan the letter and replace as needed...
any ideas
Mark

[mperrah]

Tested using calculation in manual, works great.
What calculations are blocked in this mode?
I have several formulae and sheet events.
They seem to work, just wondering what calculations are blocked?
Mark

[Bob Phillips]

They are all blocked, but I presume that you reset on exit, so they would then all calculate.

[mperrah]

I think I stumbled on to an ah ha moment
I could add the calulate control to any of the onchange events,
and make the parts of the project that don't require any calculations
run much much quicker.
Thanks xld
I have charts using sumproduct,
named ranges with if statements finding unique vales and more,
the don't need to calculate unless you are on that sheet,
and only if changes to the start and stop dates in a specific cell have been changed.
This should help tremendously...
Thanks again xld

Mark