KeyDown event not quite working for me

[cburdick]

I?ve created a doubly entry form in excel and I want to disable the copy features in one of the text boxes. I have finally gotten close with this code:

Private Sub txtFileNum_KeyDown(ByVal vbKeyC As MSForms.ReturnInteger, ByVal fmCtrlMask As Integer)

MsgBox ("No Cheating!")

End Sub

Now, when I hit ctrl and any other key, the message pops up and effectively stops the user from copying. The problem is that when ANY key is pressed the message pops up. You type a number or letter, the message pops up, and when you hit ok, the keystroke appears.

My question is how can I edit my code so that the message only pops up with ctrl c?

If it helps, I?ve also tried:
Private Sub txtFileNum_KeyPress(keyascii As Integer)
If keyascii = 3 Then
MsgBox ("No Cheating!")
End If
End Sub

With that I get a compile error: 'Procedure declaration does not match description of event or procedure having same name' which according to Microsoft, is a bug on their end.


Thanks bunches =)
Christina

[Charlize]

When you hit control key, message will show up. You don't have to hit control in a textbox so we are assuming they are going to hit c afterwards.
[VBA]Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 17 Then
MsgBox ("no copying allowed")
End If
End Sub[/VBA]
Charlize

[cburdick]

Excelent! Works perfectly and covers any other ctrl+key they they might use. Thank you very much =)

Christina

[Charlize]

Maybe another possibility ...
[vba]Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "^{s}"
End Sub
Private Sub Workbook_Open()
'Show_box_that_copying_isnot_allowed in a normal module
'and is just showing a msgbox that copying isn't allowed
'This is for the whole application in excel
Application.OnKey "^{s}", "Show_box_that_copying_isnot_allowed"
End Sub[/vba]
Charlize

[mperrah]

Charlize,
I have a sheet were I enter 3 letters only (p, f, or n)
I enter one per cell. I have to hit enter or right arrow each time for the next cell to be entered.
How can I use .offset after one of these three keystrokes are made.
ie. i type p and before space or anything else
the selection is offset(0, 1) ?

Mark

[Charlize]

Try this one. If you still need a small letter (f, n, p) as the first one, use shift and one of those letters or F2 and you are in edit mode.

[mperrah]

Charlize,
You rock

I modified to capture Upper case inputs too.

One question, how can I limit the range this affects?

Could I move this to a sheet procedure and set the target range?
I'm working in R3:AR1000 on sheet("Data")

Mark

[Charlize]

This one for data and range A1:C6. Take a look at intersect for another way to select a range where it would apply to.

[mperrah]

Charlize,
You are awesome...
I modified this to get the range I was trying for.
Thanks so so much.

Mark,

[VBA]Sub insert_letter(vletter As String)
If ActiveSheet.Name = "Data" Then
'a1:c6 - or try something with intersect
If ActiveCell.Column < 18 Or ActiveCell.Column > 44 _
Or ActiveCell.Row > 1000 Then
ActiveCell.Value = vletter
SendKeys "{F2}"
Else
ActiveCell.Value = vletter
ActiveCell.Offset(, 1).Select
End If
Else
ActiveCell.Value = vletter
SendKeys "{F2}"
End If
End Sub[/VBA]

[mperrah]

I changed the keys that trigger the key event to keep the keys closer together for one-hand operation.
I'm using 1, 2, 3, 4

I'm noticing that entering these keys anywhere on any sheet,
will fire the sub.
Within the target range the offset fires correctly,
but is there a way to let the onkey event fire
only within a specified range R3:AR1000.

so if I type 1 in cell Q3 I get "1" not "p"

[VBA]
' in thisworkbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "1"
Application.OnKey "2"
Application.OnKey "3"
Application.OnKey "4"
End Sub
Private Sub Workbook_Open()
Application.OnKey "1", "action_p"
Application.OnKey "2", "action_f"
Application.OnKey "3", "action_n"
Application.OnKey "4", "action_"
End Sub

' in standard module
Option Explicit
Sub action_p()
If Selection.Count > 1 Then Exit Sub
Call insert_letter("p")
End Sub
Sub action_f()
If Selection.Count > 1 Then Exit Sub
Call insert_letter("f")
End Sub
Sub action_n()
If Selection.Count > 1 Then Exit Sub
Call insert_letter("n")
End Sub
Sub action_()
If Selection.Count > 1 Then Exit Sub
Call insert_letter("")
End Sub
Sub insert_letter(vletter As String)
If ActiveSheet.Name = "Data" Then
'a1:c6 - or try something with intersect
If ActiveCell.Column > 18 _
Or ActiveCell.Column < 44 _
Or ActiveCell.Row < 1000 Then
ActiveCell.Value = vletter
SendKeys "{F2}"
Else
ActiveCell.Value = vletter
ActiveCell.Offset(, 1).Select
End If
Else
ActiveCell.Value = vletter
SendKeys "{F2}"
End If
End Sub

[/VBA]

[mperrah]

I got it.
I wasn't sure where the sub was capturing the onkey,
so I tried changing different parts till this worked.
I commented to help understand what was happening...
Hope this helps someone else too.
[VBA]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]

The thisworkbook on load and beforeclose codes stayed the same..
Mark