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 .
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 .
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
ASKER
Private Sub txtDateOfVisit_KeyDown(ByV al keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
If keycode = 13 Then 'enter key pressed
If IsNumeric(Format(txtDateOf Visit.Valu e, "ddmmyyyy")) Then
Worksheets("tblServiceShee t").Select
Worksheets("tblServiceShee t").Activa te
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
If keycode = 13 Then 'enter key pressed
If IsNumeric(Format(txtDateOf
Worksheets("tblServiceShee
Worksheets("tblServiceShee
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
ASKER
Have tried the changing still not working
ASKER
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("t blServiceS heet").Sel ect
ThisWorkbook.Worksheets("t blServiceS heet").Act ivate
MyPath = ActiveSheet.Range("Q4").Va lue
SerialNo = txtSerialNo.Value
Module1.strPmcOption = "Service Sheet"
On Error GoTo ErrorHandler
Line1:
Workbooks.Open Filename:=MyPath & "Machine Database.xlsm"
Workbooks("Machine Database").Worksheets("tbl MachineLis t").Select
Workbooks("Machine Database").Worksheets("tbl MachineLis t").Activa te
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'!MacAddMachi neDetails" , "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("t blServiceS heet").Ran ge("Q4").V alue = MyPath
GoTo Line1
End Sub
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("t
ThisWorkbook.Worksheets("t
MyPath = ActiveSheet.Range("Q4").Va
SerialNo = txtSerialNo.Value
Module1.strPmcOption = "Service Sheet"
On Error GoTo ErrorHandler
Line1:
Workbooks.Open Filename:=MyPath & "Machine Database.xlsm"
Workbooks("Machine Database").Worksheets("tbl
Workbooks("Machine Database").Worksheets("tbl
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'!MacAddMachi
'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("t
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
ASKER
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
ASKER
yes but this code is used before the problem
I tried the textdate code first and worksok till this code is used
I tried the textdate code first and worksok till this code is used
ASKER
the txtSerialNo textbox is used first
then the txtDate is used next
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
No points expected, but instead of
you can avoid using "magic numbers" and use self-documenting built in values like this.
If keycode = 13 Then 'enter key pressed
you can avoid using "magic numbers" and use self-documenting built in values like this.
If keycode = vbKeyReturn Then
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
Martin Sir,
Probably you are right. I have used your suggestion. Lets see what Kevin replies.
Probably you are right. I have used your suggestion. Lets see what Kevin replies.
ASKER
vbkeyreturn still does not work
ASKER
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?
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
ASKER
have tried to test fault you need to close excel completely then re open
ASKER
added DoEvents still same problem
ASKER
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.
ASKER
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
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?
ASKER
9612
ASKER
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
is recognized as Enter, and range F4 is updated.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you martin I will try
Out of curiosity, which version of Excel are you using?
ASKER
Hello martin I am using excel 2010
Making new file and exporting all forms and module to new file has worked thank you martin
Making new file and exporting all forms and module to new file has worked thank you martin
ASKER
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
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
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.