error trapping and navigating userform textboxes

[dandungan]

Hi Mr. Excel,

This is my first post here.

Using excel 2003 on windows XP pro, I am emulating an old Prodigy database. The database doesn't print from the network and the end users want to work from their work area instead of going to another area on an old computer to complete data entry.

The users want F2 to save the spreadsheet and open the switchboard form. The following code does not run when I press F2 with the userform, frmEntry, active. Are there any suggestions?

Thanks,

Dan

Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'Press [F2] to save
If KeyAscii = 113 Then
    ThisWorkbook.Save
    Me.txtEntryMonth.SetFocus
    frmSwitchboard.Show
    Unload frmEntry
End If

End Sub

 

[ChrisM]

Keypress only recognizes ANSI characters, and the function keys don't belong to that set.

You need to use the KeyDown event instead:

Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyF2 Then
    Label1.Caption = "Yes"
Else
    Label1.Caption = "other"
End If
End Sub

 

[dandungan]

Hi ChrisM,

After modifying your example, nothing happens when I press F2.

Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyF2 Then
    ThisWorkbook.Save
    Me.txtEntryMonth.SetFocus
    frmSwitchboard.Show
    Unload frmEntry
End If
End Sub

Thanks,

Dan

 

[ChrisM]

Mine definitely works, I tested it before posting it, so now we have to figure out what's different between your form and mine. My guess is that yours is full of controls (textboxes, etc), and your keypress is actually happening within those controls and not the "background" of the form itself. Can you try once more and click in the background area first, then hit F2?

 

[ChrisM]

Oh, found it in the helpfile: "The form or control with the focus receives all keystrokes. A form can have the focus only if it has no controls or all its visible controls are disabled." That's why mine worked and yours doesn't. Let me poke around to see if there is another event to tap in to, because otherwise you will have to write keydown events for all controls on the form.

 

[ChrisM]

Found this post on another board, basically says you either have to write the keypress for every control on your form, or create a custom class to keep from having to rewrite the same code:

Function Keys with Userforms

If you don't have a lot of controls it's probably easiest just to write the event for each one individually.

 

[dandungan]

Thank you ChrisM. I put the sub in a textbox and it does work. However other things went awry. Should a post a different question or continue here?

 

[ChrisM]

It's up to you, you can post here, what happened?

 

[dandungan]

I had other code to ensure the user enters a good value in the textbox txtJobNum

Private Sub txtEntryJobNum_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Mid(Me.txtEntryJobNum, 7, 1) = "-" And Len(Me.txtEntryJobNum) = 10 Then
       'skip
    Else
        MsgBox "Please enter a valid Job Number", vbRetryCancel, "The job number is incorrect."
        Cancel = True
    End If
End Sub

So this was firing every time I tried to unload frmEntry and show frmSwitchboard.

I took the code out of the textbox and moved it to a module where I'm moving the values to a spreadsheet.

Sub CopyToData()

Dim c As Range
Dim rPaste As Range
Dim wbPaste As Workbook
Dim rCopy As Range
With frmEntry

 'next empty cell in column A
Set c = Sheets("Invoice Data").Range("a65536").End(xlUp).Offset(1, 0)

Application.ScreenUpdating = False 'speed up, hide task
 

 'write userform entries to database

If Mid(.txtEntryJobNum.Value, 7, 1) = "-" And Len(.txtEntryJobNum.Value) = 10 Then
    c.Offset(0, 1).Value = .txtEntryJobNum.Value
Else
    .txtEntryJobNum.SetFocus
    MsgBox "Please correct the Job number"
    Exit Sub
End If
c.Offset(0, 0).Value = "'" & .txtEntryCode.Value
c.Offset(0, 2).Value = .txtEntryDate.Value
c.Offset(0, 3).Value = .txtEntryQtyShip.Value
c.Offset(0, 4).Value = .txtEntryUnitPrice.Value

'the math fails if the user doesn't enter a 0 in the additional charges textbox.
If .txtEntryAddCharges.Value = "" Then
    c.Offset(0, 5).Value = 0
Else
    c.Offset(0, 5).Value = .txtEntryAddCharges.Value
End If
c.Offset(0, 6).Value = .txtInvTotal.Value
c.Offset(0, 7).Value = .txtEntryMonth.Text

'clear the textboxes to prepare for a new entry
'Don't clear the Plating code, because the make several entries to one code.
'Me.txtEntryCode.Value = ""
.txtEntryJobNum.Value = ""
'Don't erase the date because they always to the data entry a day at a time.
'Me.txtEntryDate.Value = ""
.txtEntryQtyShip.Value = ""
.txtEntryUnitPrice.Value = ""
.txtEntryAddCharges.Value = ""
.txtInvTotal.Value = ""
'Dont erase the month
'Me.txtEntryMonth.Text = ""'
.txtEntryJobNum.SetFocus
Calculate

Application.ScreenUpdating = True 'return updating

End With
End Sub

I'm attempting to understand how to reduce data entry errors in my form design.

Thanks,

 

[ChrisM]

Not sure of your question? I prefer to test for errors when the user hits the final "submit" button. I don't like text boxes yelling at me when maybe I'm not ready to enter all the data yet. I wouldn't close the form without testing that all the data is good.