Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using numeric pad - key in vba
I have the following procedure that is supposed to take the text value from a cell, convert it to date and when the user presses the + or - key on the numeric keypad adds or subtracts one day then updates the cell. The + sign on the keypad works but the minus does not (it just updates the worksheet cell with the - sign. Here is what I have so far: Private Sub txtDate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Dim dtDateShown As Date Dim dtNewDate As Date dtDateShown = CDate(txtDate.Text) dtDateShown = Format(dtDateShown, "mm/dd") If KeyAscii = 43 Then '+ key pressed dtNewDate = dtDateShown + 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") End If If KeyAscii = 45 Then 'this was the minus key also tried vbKeySubtract dtNewDate = dtDateShown - 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using numeric pad - key in vba
Try the below.....Note the addition of KeyAscii = 0....... If KeyAscii = 43 Then '+ key pressed dtNewDate = dtDateShown + 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") KeyAscii = 0 End If If KeyAscii = 45 Then 'this was the minus key also tried vbKeySubtract dtNewDate = dtDateShown - 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") KeyAscii = 0 End If If this post helps click Yes --------------- Jacob Skaria "Billy B" wrote: I have the following procedure that is supposed to take the text value from a cell, convert it to date and when the user presses the + or - key on the numeric keypad adds or subtracts one day then updates the cell. The + sign on the keypad works but the minus does not (it just updates the worksheet cell with the - sign. Here is what I have so far: Private Sub txtDate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Dim dtDateShown As Date Dim dtNewDate As Date dtDateShown = CDate(txtDate.Text) dtDateShown = Format(dtDateShown, "mm/dd") If KeyAscii = 43 Then '+ key pressed dtNewDate = dtDateShown + 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") End If If KeyAscii = 45 Then 'this was the minus key also tried vbKeySubtract dtNewDate = dtDateShown - 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using numeric pad - key in vba
Hi I set up a Userform with two textboxes named as in your code, and it was working perfectly as you wanted. Try to set a Stop statement after the Dim statements, and then step through the code and see what happens. (Also check the KeyAscii value by holding the mouse pointer over the variable) Hopes this helps. .... Per "Billy B" skrev i meddelelsen ... I have the following procedure that is supposed to take the text value from a cell, convert it to date and when the user presses the + or - key on the numeric keypad adds or subtracts one day then updates the cell. The + sign on the keypad works but the minus does not (it just updates the worksheet cell with the - sign. Here is what I have so far: Private Sub txtDate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Dim dtDateShown As Date Dim dtNewDate As Date dtDateShown = CDate(txtDate.Text) dtDateShown = Format(dtDateShown, "mm/dd") If KeyAscii = 43 Then '+ key pressed dtNewDate = dtDateShown + 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") End If If KeyAscii = 45 Then 'this was the minus key also tried vbKeySubtract dtNewDate = dtDateShown - 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using numeric pad - key in vba
Your code is working for me. Try the keydown event with keycodes 107 and 109... Private Sub txtDate_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below.....Note the addition of KeyAscii = 0....... If KeyAscii = 43 Then '+ key pressed dtNewDate = dtDateShown + 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") KeyAscii = 0 End If If KeyAscii = 45 Then 'this was the minus key also tried vbKeySubtract dtNewDate = dtDateShown - 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") KeyAscii = 0 End If If this post helps click Yes --------------- Jacob Skaria "Billy B" wrote: I have the following procedure that is supposed to take the text value from a cell, convert it to date and when the user presses the + or - key on the numeric keypad adds or subtracts one day then updates the cell. The + sign on the keypad works but the minus does not (it just updates the worksheet cell with the - sign. Here is what I have so far: Private Sub txtDate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Dim dtDateShown As Date Dim dtNewDate As Date dtDateShown = CDate(txtDate.Text) dtDateShown = Format(dtDateShown, "mm/dd") If KeyAscii = 43 Then '+ key pressed dtNewDate = dtDateShown + 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") End If If KeyAscii = 45 Then 'this was the minus key also tried vbKeySubtract dtNewDate = dtDateShown - 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using numeric pad - key in vba
Just a side note... you do not have to convert dtNewDate (which is declared as Date) to a String value inside the Format function... that just requires the Format function to convert it back to a Date internally so it can apply the "mm/dd" format to it (the mm and dd work with Dates, not Strings). -- Rick (MVP - Excel) "Billy B" wrote in message ... I have the following procedure that is supposed to take the text value from a cell, convert it to date and when the user presses the + or - key on the numeric keypad adds or subtracts one day then updates the cell. The + sign on the keypad works but the minus does not (it just updates the worksheet cell with the - sign. Here is what I have so far: Private Sub txtDate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Dim dtDateShown As Date Dim dtNewDate As Date dtDateShown = CDate(txtDate.Text) dtDateShown = Format(dtDateShown, "mm/dd") If KeyAscii = 43 Then '+ key pressed dtNewDate = dtDateShown + 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") End If If KeyAscii = 45 Then 'this was the minus key also tried vbKeySubtract dtNewDate = dtDateShown - 1 dtNewDate = Format(dtNewDate, "mm/dd") txtDate.Text = Format(CStr(dtNewDate), "mm/dd") End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Return Numeric Label based on (Numeric Value) Criterion | Excel Worksheet Functions | |||
Find Numeric Criterion in Column & Return the Numeric Value from Row above | Excel Worksheet Functions | |||
Numeric in Text to convert back to the form of Numeric for VLookup Purposes | Excel Discussion (Misc queries) | |||
Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions | |||
Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions |