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

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

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

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

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