Link to home
Start Free TrialLog in
Avatar of Kevin Hyde
Kevin Hyde

asked on

Text box keydown event does not work

I open workbook A this opens userform
On userform I enter in text box value this works ok and opens workbook B import data from workbook B to workbook A. Workbook B then closes next textbox on userform values accepted when enter key pressed nothing happens.
Excel 2010
If userform loaded from vba project this works ok, please advise what is wrong .
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Hi Kevin,

First text box doesn't have drop down key. If you are referring to Combo Box or List Box, then it would be helpful, if you provide sample workbook or UserForm & Upload code.
Avatar of Kevin Hyde
Kevin Hyde

ASKER

Text box keydown event using enter key from keyboard if I put a command button on and copy text this work ok
Still we need to see the code
Private Sub txtDateOfVisit_KeyDown(ByVal keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If keycode = 13 Then 'enter key pressed
        If IsNumeric(Format(txtDateOfVisit.Value, "ddmmyyyy")) Then
            Worksheets("tblServiceSheet").Select
            Worksheets("tblServiceSheet").Activate
            keycode = 0
            ActiveSheet.Range("J4") = txtDateOfVisit.Value
            ServiceSheetNumber txtDateOfVisit.Value 'Service Sheet Number Sub Procedure
        Else
            MsgBox "Date Format Incorrect"
        End If
        GetFocus 0 ' Sub Procedure
    End If
End Sub
Why Select & Activate together, try changing as below:
Private Sub txtDateOfVisit_KeyDown(ByVal keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If keycode = 13 Then 'enter key pressed
        If IsNumeric(Format(txtDateOfVisit.Value, "ddmmyyyy")) Then
            Worksheets("tblServiceSheet").Activate
            keycode = 0
            ActiveSheet.Range("J4") = txtDateOfVisit.Value
            ServiceSheetNumber txtDateOfVisit.Value 'Service Sheet Number Sub Procedure
        Else
            MsgBox "Date Format Incorrect"
        End If
        GetFocus 0 ' Sub Procedure
    End If
End Sub

Open in new window

Have tried the changing still not working
the text box code which is used before is in this textbox

Private Sub txtSerialNo_KeyDown(ByVal keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim LastRow As Long
Dim X As Long
Dim RowFound As Integer
Dim TextValue As Long
Dim SerialNo As Long
Dim MyPath As String
Dim strMachineGeneration As String
Dim wbServiceSheet As Workbook

    If keycode = 13 Then
        MultiPage1.Value = 0
        Set wbServiceSheet = ActiveWorkbook
        ThisWorkbook.Worksheets("tblServiceSheet").Select
        ThisWorkbook.Worksheets("tblServiceSheet").Activate
        MyPath = ActiveSheet.Range("Q4").Value
        SerialNo = txtSerialNo.Value
        Module1.strPmcOption = "Service Sheet"
        On Error GoTo ErrorHandler
Line1:
       
        Workbooks.Open Filename:=MyPath & "Machine Database.xlsm"
       
        Workbooks("Machine Database").Worksheets("tblMachineList").Select
        Workbooks("Machine Database").Worksheets("tblMachineList").Activate
               
        LastRow = Cells(Rows.Count, "d").End(xlUp).Row
               
        For X = 2 To LastRow
            If Cells(X, "d") = SerialNo Then
            RowFound = X
            End If
        Next X
       
       
        If RowFound = 0 Then
            'if No Record Open Database Form And Fill Machine Details
            Application.Run "'Machine database.xlsm'!MacAddMachineDetails", "Service Sheet", txtSerialNo.Value
           
            'After Machine Details Added To Machine Database.
            ' Close Machine Database Then Goto Line1 To Search For Machine Details.
            Workbooks("Machine Database").Close savechanges:=True
           
            GoTo Line1:
        Else
            FindMachineDetails (RowFound)
        End If
             
        FillCustomerContactCombo
       
        Application.EnableEvents = True
       
        Windows("Service Sheet.xlsm").Activate
        wbServiceSheet.Activate
        keycode = 0
        GetFocus 0 ' Sub Procedure

    End If
   

    Exit Sub
   
ErrorHandler:
    FindFile MyPath
    ThisWorkbook.Worksheets("tblServiceSheet").Range("Q4").Value = MyPath
    GoTo Line1
End Sub
Try this:
Private Sub txtDateOfVisit_KeyDown(ByVal keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, "j").End(xlUp).Row
For X = 2 To LastRow
    If keycode = 13 Then 'enter key pressed
        If IsNumeric(Format(txtDateOfVisit.Value, "ddmmyyyy")) Then
            Worksheets("tblServiceSheet").Activate
            ActiveSheet.Range("J4") = txtDateOfVisit.Value
            ServiceSheetNumber txtDateOfVisit.Value 'Service Sheet Number Sub Procedure
        Else
            MsgBox "Date Format Incorrect"
        End If
        GetFocus 0 ' Sub Procedure
    End If
Next X
End Sub

Open in new window

Still not working
Is your below code working for another textbox?
Private Sub txtSerialNo_KeyDown(ByVal keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim LastRow As Long
Dim X As Long
Dim RowFound As Integer
Dim TextValue As Long
Dim SerialNo As Long
Dim MyPath As String
Dim strMachineGeneration As String
Dim wbServiceSheet As Workbook

    If keycode = 13 Then
        MultiPage1.Value = 0
        Set wbServiceSheet = ActiveWorkbook
        ThisWorkbook.Worksheets("tblServiceSheet").Select
        ThisWorkbook.Worksheets("tblServiceSheet").Activate
        MyPath = ActiveSheet.Range("Q4").Value
        SerialNo = txtSerialNo.Value
        Module1.strPmcOption = "Service Sheet"
        On Error GoTo ErrorHandler
Line1:
        
        Workbooks.Open Filename:=MyPath & "Machine Database.xlsm"
        
        Workbooks("Machine Database").Worksheets("tblMachineList").Select
        Workbooks("Machine Database").Worksheets("tblMachineList").Activate
               
        LastRow = Cells(Rows.Count, "d").End(xlUp).Row
                
        For X = 2 To LastRow
            If Cells(X, "d") = SerialNo Then
            RowFound = X
            End If
        Next X
        
        
        If RowFound = 0 Then
            'if No Record Open Database Form And Fill Machine Details
            Application.Run "'Machine database.xlsm'!MacAddMachineDetails", "Service Sheet", txtSerialNo.Value
            
            'After Machine Details Added To Machine Database.
            ' Close Machine Database Then Goto Line1 To Search For Machine Details.
            Workbooks("Machine Database").Close savechanges:=True
            
            GoTo Line1:
        Else
            FindMachineDetails (RowFound)
        End If
             
        FillCustomerContactCombo
        
        Application.EnableEvents = True
        
        Windows("Service Sheet.xlsm").Activate
        wbServiceSheet.Activate
        keycode = 0
        GetFocus 0 ' Sub Procedure

    End If
    

    Exit Sub
    
ErrorHandler:
    FindFile MyPath
    ThisWorkbook.Worksheets("tblServiceSheet").Range("Q4").Value = MyPath
    GoTo Line1
End Sub

Open in new window

yes but this code is used before the problem
I tried the textdate code first and worksok till this code is used
the txtSerialNo textbox is used first
then the txtDate is used next
Try this:
Private Sub txtDateOfVisit_KeyDown(ByVal keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If keycode = 13 Then 'enter key pressed
        If IsNumeric(Format(txtDateOfVisit.Text, "ddmmyyyy")) Then
            Worksheets("tblServiceSheet").Activate
            keycode = 0
            ActiveSheet.Range("J4").Value = txtDateOfVisit.Text
            ServiceSheetNumber txtDateOfVisit.Text 'Service Sheet Number Sub Procedure
        Else
            MsgBox "Date Format Incorrect"
        End If
        GetFocus 0 ' Sub Procedure
    End If
End Sub

Open in new window

No points expected, but instead of

If keycode = 13 Then 'enter key pressed

Open in new window


you can avoid using "magic numbers" and use self-documenting built in values like this.

If keycode = vbKeyReturn Then

Open in new window

Try this:
Private Sub txtDateOfVisit_KeyDown(ByVal keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If keycode = vbKeyReturn Then 'enter key pressed
        If IsDate(txtDateOfVisit.Value) = False Then
            txtDateOfVisit.Value = ""
        ElseIf IsNumeric(Format(txtDateOfVisit.Value, "ddmmyy")) Then
            Worksheets("tblServiceSheet").Select
            Worksheets("tblServiceSheet").Activate
            ActiveSheet.Range("J4") = txtDateOfVisit.Value
            keycode = 0
            GetFocus 0 ' Sub Procedure
        End If
    End If
End Sub

Open in new window

Martin Sir,

Probably you are right. I have used your suggestion. Lets see what Kevin replies.
vbkeyreturn still does not work
I tried to run this from vbaprooject design view and it works ok
When you say it does not work, what do you mean?

Click in the lefthand margin on line 2. That will place a dark red circle there. Run the code. Does the code stop at line 2?
Add a line after line 2 that says

DoEvents

Open in new window

have tried to test fault you need to close excel completely then re open
added DoEvents  still same problem
When you enter the date in textbox with correct format and you press enter key nothing happens
Please attach a workbook that demonstrates the problem.
Please forgive me if you've done all or part of this before, but please list step by step, in detail, the actions you take that cause the problem to happen.
Open Service sheet.xlsm file
userform opens
type serial number in txtSeriaNo press enter this works OK
type in date in txtDate press enter but no event, if you run from design form this works OK
Customer-Contacts-Database.xlsm
Machine-Database.xlsm
Service-Sheet.xlsm
Can you give me a valid "M/C Serial Number" or tell me where to find them?
9612
The valid serial numbers are entered in machine database tblMachineList
When I open the workbook, type in 9612, press enter, add a date like 03/01/2017 in the userform "Date" field and press enter, the keycode in this code
Private Sub txtDateOfVisit_KeyDown(ByVal keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If keycode = vbKeyReturn Then 'enter key pressed
        If IsDate(txtDateOfVisit.Value) = False Then
            txtDateOfVisit.Value = ""
        ElseIf IsNumeric(Format(txtDateOfVisit.Value, "ddmmyy")) Then
            Worksheets("tblServiceSheet").Select
            Worksheets("tblServiceSheet").Activate
            ActiveSheet.Range("J4") = txtDateOfVisit.Value
            keycode = 0
            GetFocus 0 ' Sub Procedure
        End If
    End If

Open in new window

is recognized as Enter, and range F4 is updated.
If you go from the developer it works for me also
If you open excel file and run as normal this is where I get a problem, it appears after txtserialno event has been used the txtdate and all other textbox eps keydown event does not work
It works perfectly for me without going to the developer area.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you martin I will try
Out of curiosity, which version of Excel are you using?
Hello martin I am using excel 2010
Making new file and exporting all forms and module to new file has worked thank you martin
Thank you martin this now works ok
ou're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016