mperrah
11-17-2007, 10:53 AM
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.
' 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
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
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.
' 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
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