Excel VBA - Userform Textbox problem

Discussion in 'Microsoft Excel Programming' started by thesteelmaker, Mar 20, 2004.

  1. The code at the bottom gives me a textbox, formatted to 1 dp. Som
    vailidation to stop "text" enteries and some validation to give
    positive number.

    All is ok so far.

    On doing some testing, the text box lets me do some strange things
    that i do not want, i.e.:

    Enter into text box

    6.6.6 this changes to 0.3
    6 7 changes to 38174.0
    5/8/0 changes to 36743.0

    I take it the last two are accepted as dates.

    why?

    These are errors that i dont really want.

    Any suggestions would be helpful.

    Thanks

    Code=================================
    Private Sub TextBox1_BeforeUpdate(ByVal Cancel A
    MSForms.ReturnBoolean)

    ' Set number format
    TextBox1.Text = Format$(frmTest.TextBox1.Text, "####0.0")

    ' Check the value entered is numeric
    If Not IsNumeric(TextBox1.Value) Then
    MsgBox "You entered a non-numeric value, try again."
    vbExclamation, "Error"
    TextBox1.SetFocus
    TextBox1.SelStart = 0
    TextBox1.SelLength = Len(TextBox1.Text)
    Cancel = True
    Else

    ' Check the value is positive
    If (TextBox1.Text < 0) Then
    MsgBox "You inserted a negative number, try again."
    vbExclamation, "Error"
    TextBox1.SetFocus
    TextBox1.SelStart = 0
    TextBox1.SelLength = Len(TextBox1.Text)
    Cancel = True
    End If
    End If
    End Sub

    Private Sub UserForm_Initialize()
    TextBox1.Text = ""
    TextBox2.Text = ""
    TextBox1.SetFocus
    End Su

    --
    Message posted from http://www.ExcelForum.com
     
    thesteelmaker, Mar 20, 2004
    #1
    1. Advertisements

  2. thesteelmaker

    Harald Staff Guest

    Hi

    You don't need all that. If positive decimal numbers are the only entries
    allowed then use simply

    Private Sub TextBox1_KeyDown(ByVal KeyCode As _
    MSForms.ReturnInteger, ByVal Shift As Integer)
    If Shift = 2 Then
    If KeyCode = 86 Then KeyCode = 0
    End If
    End Sub

    Private Sub TextBox1_KeyPress(ByVal KeyAscii _
    As MSForms.ReturnInteger)
    Select Case KeyAscii
    Case 46
    If InStr(TextBox1.Text, ".") > 0 Then _
    KeyAscii = 0
    Case 48 To 57
    Case Else
    KeyAscii = 0
    End Select
    End Sub

    Now it's impossible to type anything unwanted in there and you don't have to
    annoy your users with modal textboxes popping up telling them how stupid
    they are. They hate that, and after a while they hate the application
    too -trust me on this.

    HTH. Best wishes Harald

    "thesteelmaker >" <<> skrev i
    melding news:...
    > The code at the bottom gives me a textbox, formatted to 1 dp. Some
    > vailidation to stop "text" enteries and some validation to give a
    > positive number.
    >
    > All is ok so far.
    >
    > On doing some testing, the text box lets me do some strange things,
    > that i do not want, i.e.:
    >
    > Enter into text box
    >
    > 6.6.6 this changes to 0.3
    > 6 7 changes to 38174.0
    > 5/8/0 changes to 36743.0
    >
    > I take it the last two are accepted as dates.
    >
    > why?
    >
    > These are errors that i dont really want.
    >
    > Any suggestions would be helpful.
    >
    > Thanks
    >
    > Code=================================
    > Private Sub TextBox1_BeforeUpdate(ByVal Cancel As
    > MSForms.ReturnBoolean)
    >
    > ' Set number format
    > TextBox1.Text = Format$(frmTest.TextBox1.Text, "####0.0")
    >
    > ' Check the value entered is numeric
    > If Not IsNumeric(TextBox1.Value) Then
    > MsgBox "You entered a non-numeric value, try again.",
    > vbExclamation, "Error"
    > TextBox1.SetFocus
    > TextBox1.SelStart = 0
    > TextBox1.SelLength = Len(TextBox1.Text)
    > Cancel = True
    > Else
    >
    > ' Check the value is positive
    > If (TextBox1.Text < 0) Then
    > MsgBox "You inserted a negative number, try again.",
    > vbExclamation, "Error"
    > TextBox1.SetFocus
    > TextBox1.SelStart = 0
    > TextBox1.SelLength = Len(TextBox1.Text)
    > Cancel = True
    > End If
    > End If
    > End Sub
    >
    > Private Sub UserForm_Initialize()
    > TextBox1.Text = ""
    > TextBox2.Text = ""
    > TextBox1.SetFocus
    > End Sub
    >
    >
    > ---
    > Message posted from http://www.ExcelForum.com/
    >
     
    Harald Staff, Mar 20, 2004
    #2
    1. Advertisements

  3. Just to add to Harald's suggestion:

    This portion:

    If Shift = 2 Then
    If KeyCode = 86 Then KeyCode = 0
    End If

    Stops ctrl-V (pasting into the textbox).


    Harald Staff wrote:
    >
    > Hi
    >
    > You don't need all that. If positive decimal numbers are the only entries
    > allowed then use simply
    >
    > Private Sub TextBox1_KeyDown(ByVal KeyCode As _
    > MSForms.ReturnInteger, ByVal Shift As Integer)
    > If Shift = 2 Then
    > If KeyCode = 86 Then KeyCode = 0
    > End If
    > End Sub
    >
    > Private Sub TextBox1_KeyPress(ByVal KeyAscii _
    > As MSForms.ReturnInteger)
    > Select Case KeyAscii
    > Case 46
    > If InStr(TextBox1.Text, ".") > 0 Then _
    > KeyAscii = 0
    > Case 48 To 57
    > Case Else
    > KeyAscii = 0
    > End Select
    > End Sub
    >
    > Now it's impossible to type anything unwanted in there and you don't have to
    > annoy your users with modal textboxes popping up telling them how stupid
    > they are. They hate that, and after a while they hate the application
    > too -trust me on this.
    >
    > HTH. Best wishes Harald
    >
    > "thesteelmaker >" <<> skrev i
    > melding news:...
    > > The code at the bottom gives me a textbox, formatted to 1 dp. Some
    > > vailidation to stop "text" enteries and some validation to give a
    > > positive number.
    > >
    > > All is ok so far.
    > >
    > > On doing some testing, the text box lets me do some strange things,
    > > that i do not want, i.e.:
    > >
    > > Enter into text box
    > >
    > > 6.6.6 this changes to 0.3
    > > 6 7 changes to 38174.0
    > > 5/8/0 changes to 36743.0
    > >
    > > I take it the last two are accepted as dates.
    > >
    > > why?
    > >
    > > These are errors that i dont really want.
    > >
    > > Any suggestions would be helpful.
    > >
    > > Thanks
    > >
    > > Code=================================
    > > Private Sub TextBox1_BeforeUpdate(ByVal Cancel As
    > > MSForms.ReturnBoolean)
    > >
    > > ' Set number format
    > > TextBox1.Text = Format$(frmTest.TextBox1.Text, "####0.0")
    > >
    > > ' Check the value entered is numeric
    > > If Not IsNumeric(TextBox1.Value) Then
    > > MsgBox "You entered a non-numeric value, try again.",
    > > vbExclamation, "Error"
    > > TextBox1.SetFocus
    > > TextBox1.SelStart = 0
    > > TextBox1.SelLength = Len(TextBox1.Text)
    > > Cancel = True
    > > Else
    > >
    > > ' Check the value is positive
    > > If (TextBox1.Text < 0) Then
    > > MsgBox "You inserted a negative number, try again.",
    > > vbExclamation, "Error"
    > > TextBox1.SetFocus
    > > TextBox1.SelStart = 0
    > > TextBox1.SelLength = Len(TextBox1.Text)
    > > Cancel = True
    > > End If
    > > End If
    > > End Sub
    > >
    > > Private Sub UserForm_Initialize()
    > > TextBox1.Text = ""
    > > TextBox2.Text = ""
    > > TextBox1.SetFocus
    > > End Sub
    > >
    > >
    > > ---
    > > Message posted from http://www.ExcelForum.com/
    > >


    --

    Dave Peterson
     
    Dave Peterson, Mar 20, 2004
    #3
  4. Thank you very much.

    I'm very impressed.

    I've not come across any reference to keycodes, or anything in yo
    coding.

    Could you explain a bit more.

    Thanks.

    Private Sub TextBox1_KeyDown(ByVal KeyCode As _
    MSForms.ReturnInteger, ByVal Shift As Integer)
    If Shift = 2 Then
    If KeyCode = 86 Then KeyCode = 0
    End If
    End Sub

    Private Sub TextBox1_KeyPress(ByVal KeyAscii _
    As MSForms.ReturnInteger)
    Select Case KeyAscii
    Case 46
    If InStr(TextBox1.Text, ".") > 0 Then _
    KeyAscii = 0
    Case 48 To 57
    Case Else
    KeyAscii = 0
    End Select
    End Su

    --
    Message posted from http://www.ExcelForum.com
     
    thesteelmaker, Mar 20, 2004
    #4
  5. Try adding this to a test form:

    Option Explicit
    Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
    ByVal Shift As Integer)
    MsgBox KeyCode
    End Sub

    And you'll see what each keystroke represents.
    (hit ctrl and don't let go. Then hit V and you'll see the 86.)

    You can search VBA's help for "keycode constants" and see vbKeyV and how it
    represents 86.



    "thesteelmaker <" wrote:
    >
    > Thank you very much.
    >
    > I'm very impressed.
    >
    > I've not come across any reference to keycodes, or anything in you
    > coding.
    >
    > Could you explain a bit more.
    >
    > Thanks.
    >
    > Private Sub TextBox1_KeyDown(ByVal KeyCode As _
    > MSForms.ReturnInteger, ByVal Shift As Integer)
    > If Shift = 2 Then
    > If KeyCode = 86 Then KeyCode = 0
    > End If
    > End Sub
    >
    > Private Sub TextBox1_KeyPress(ByVal KeyAscii _
    > As MSForms.ReturnInteger)
    > Select Case KeyAscii
    > Case 46
    > If InStr(TextBox1.Text, ".") > 0 Then _
    > KeyAscii = 0
    > Case 48 To 57
    > Case Else
    > KeyAscii = 0
    > End Select
    > End Sub
    >
    > ---
    > Message posted from http://www.ExcelForum.com/


    --

    Dave Peterson
     
    Dave Peterson, Mar 20, 2004
    #5
  6. thesteelmaker

    Jon Peltier Guest

    Well, isn't this nice. This morning I was wondering how I was going to
    tell whether the user typed or pasted his information into the textbox!
    Thanks Dave and Harald.

    (An additional coincidence is the OP's handle, given that my real
    technical training is in metallurgy.)

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    http://PeltierTech.com/Excel/Charts/
    _______

    Dave Peterson wrote:

    > Try adding this to a test form:
    >
    > Option Explicit
    > Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
    > ByVal Shift As Integer)
    > MsgBox KeyCode
    > End Sub
    >
    > And you'll see what each keystroke represents.
    > (hit ctrl and don't let go. Then hit V and you'll see the 86.)
    >
    > You can search VBA's help for "keycode constants" and see vbKeyV and how it
    > represents 86.
    >
    >
    >
    > "thesteelmaker <" wrote:
    >
    >>Thank you very much.
    >>
    >>I'm very impressed.
    >>
    >>I've not come across any reference to keycodes, or anything in you
    >>coding.
    >>
    >>Could you explain a bit more.
    >>
    >>Thanks.
    >>
    >>Private Sub TextBox1_KeyDown(ByVal KeyCode As _
    >>MSForms.ReturnInteger, ByVal Shift As Integer)
    >>If Shift = 2 Then
    >>If KeyCode = 86 Then KeyCode = 0
    >>End If
    >>End Sub
    >>
    >>Private Sub TextBox1_KeyPress(ByVal KeyAscii _
    >>As MSForms.ReturnInteger)
    >>Select Case KeyAscii
    >>Case 46
    >>If InStr(TextBox1.Text, ".") > 0 Then _
    >>KeyAscii = 0
    >>Case 48 To 57
    >>Case Else
    >>KeyAscii = 0
    >>End Select
    >>End Sub
    >>
    >>---
    >>Message posted from http://www.ExcelForum.com/

    >
    >
     
    Jon Peltier, Mar 21, 2004
    #6
  7. thesteelmaker

    Harald Staff Guest

    Hi Guys

    Just adding a few minor things.

    Jon: The paste code is pretty raw as you noticed. My real life code for
    those events is
    - cancel
    - read the clipboard content
    - if acceptable then paste it in, or paste the "acceptable part" in
    but it's pretty large and confusing, so I added the cancel part only for
    demonstration.

    Steelmaker: Many built-in events provide variables that it's code can
    validate and change.For example in ThisWorkbook:

    Private Sub Workbook_BeforeSave(ByVal _
    SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    End Sub

    Now this workbook will never save because we cancel it. Not useful as is,
    but add a condition or two to see if the save location is ok, if this and
    that is completely filled out, ...

    TextBox1_KeyDown uses Keycode, the number of the keyboard key pressed. "a"
    and "A" is the same key, number 65, the diference is Shift, which is 0 for a
    and 1 for A. Ctrl is 2. A very useful keycode to trap is 13, the Enter key,
    usually meaning that the entry is finished so go to next field or click the
    OK button or something.

    TextBox1_KeyPress uses KeyAscii, the resulting ascii value for the entry,
    where A is 65 and a is 97. For testing and development I've found that
    putting the value in question up in the form caption and/or in the immediate
    window is helpful:

    Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Me.Caption = KeyAscii
    Debug.Print KeyAscii
    End Sub

    HTH. Best wishes Harald

    "Jon Peltier" <> skrev i melding
    news:...
    > Well, isn't this nice. This morning I was wondering how I was going to
    > tell whether the user typed or pasted his information into the textbox!
    > Thanks Dave and Harald.
    >
    > (An additional coincidence is the OP's handle, given that my real
    > technical training is in metallurgy.)
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > http://PeltierTech.com/Excel/Charts/
    > _______
    >
    > Dave Peterson wrote:
    >
    > > Try adding this to a test form:
    > >
    > > Option Explicit
    > > Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
    > > ByVal Shift As Integer)
    > > MsgBox KeyCode
    > > End Sub
    > >
    > > And you'll see what each keystroke represents.
    > > (hit ctrl and don't let go. Then hit V and you'll see the 86.)
    > >
    > > You can search VBA's help for "keycode constants" and see vbKeyV and how

    it
    > > represents 86.
    > >
    > >
    > >
    > > "thesteelmaker <" wrote:
    > >
    > >>Thank you very much.
    > >>
    > >>I'm very impressed.
    > >>
    > >>I've not come across any reference to keycodes, or anything in you
    > >>coding.
    > >>
    > >>Could you explain a bit more.
    > >>
    > >>Thanks.
    > >>
    > >>Private Sub TextBox1_KeyDown(ByVal KeyCode As _
    > >>MSForms.ReturnInteger, ByVal Shift As Integer)
    > >>If Shift = 2 Then
    > >>If KeyCode = 86 Then KeyCode = 0
    > >>End If
    > >>End Sub
    > >>
    > >>Private Sub TextBox1_KeyPress(ByVal KeyAscii _
    > >>As MSForms.ReturnInteger)
    > >>Select Case KeyAscii
    > >>Case 46
    > >>If InStr(TextBox1.Text, ".") > 0 Then _
    > >>KeyAscii = 0
    > >>Case 48 To 57
    > >>Case Else
    > >>KeyAscii = 0
    > >>End Select
    > >>End Sub
    > >>
    > >>---
    > >>Message posted from http://www.ExcelForum.com/

    > >
    > >

    >
     
    Harald Staff, Mar 21, 2004
    #7
  8. thesteelmaker

    Jon Peltier Guest

    Harald old pal -

    How do you read the clipboard content? I was letting the paste occur
    into a textbox, then I parsed that to turn garbage into data. But
    reading the clipboard content sounds much more elegant.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    http://PeltierTech.com/Excel/Charts/
    _______

    Harald Staff wrote:

    > Hi Guys
    >
    > Just adding a few minor things.
    >
    > Jon: The paste code is pretty raw as you noticed. My real life code for
    > those events is
    > - cancel
    > - read the clipboard content
    > - if acceptable then paste it in, or paste the "acceptable part" in
    > but it's pretty large and confusing, so I added the cancel part only for
    > demonstration.
    >
    > Steelmaker: Many built-in events provide variables that it's code can
    > validate and change.For example in ThisWorkbook:
    >
    > Private Sub Workbook_BeforeSave(ByVal _
    > SaveAsUI As Boolean, Cancel As Boolean)
    > Cancel = True
    > End Sub
    >
    > Now this workbook will never save because we cancel it. Not useful as is,
    > but add a condition or two to see if the save location is ok, if this and
    > that is completely filled out, ...
    >
    > TextBox1_KeyDown uses Keycode, the number of the keyboard key pressed. "a"
    > and "A" is the same key, number 65, the diference is Shift, which is 0 for a
    > and 1 for A. Ctrl is 2. A very useful keycode to trap is 13, the Enter key,
    > usually meaning that the entry is finished so go to next field or click the
    > OK button or something.
    >
    > TextBox1_KeyPress uses KeyAscii, the resulting ascii value for the entry,
    > where A is 65 and a is 97. For testing and development I've found that
    > putting the value in question up in the form caption and/or in the immediate
    > window is helpful:
    >
    > Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    > Me.Caption = KeyAscii
    > Debug.Print KeyAscii
    > End Sub
    >
    > HTH. Best wishes Harald
    >
    > "Jon Peltier" <> skrev i melding
    > news:...
    >
    >>Well, isn't this nice. This morning I was wondering how I was going to
    >>tell whether the user typed or pasted his information into the textbox!
    >>Thanks Dave and Harald.
    >>
    >>(An additional coincidence is the OP's handle, given that my real
    >>technical training is in metallurgy.)
    >>
    >>- Jon
    >>-------
    >>Jon Peltier, Microsoft Excel MVP
    >>Peltier Technical Services
    >>http://PeltierTech.com/Excel/Charts/
    >>_______
    >>
    >>Dave Peterson wrote:
    >>
    >>
    >>>Try adding this to a test form:
    >>>
    >>>Option Explicit
    >>>Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
    >>> ByVal Shift As Integer)
    >>> MsgBox KeyCode
    >>>End Sub
    >>>
    >>>And you'll see what each keystroke represents.
    >>>(hit ctrl and don't let go. Then hit V and you'll see the 86.)
    >>>
    >>>You can search VBA's help for "keycode constants" and see vbKeyV and how

    >
    > it
    >
    >>>represents 86.
    >>>
    >>>
    >>>
    >>>"thesteelmaker <" wrote:
    >>>
    >>>
    >>>>Thank you very much.
    >>>>
    >>>>I'm very impressed.
    >>>>
    >>>>I've not come across any reference to keycodes, or anything in you
    >>>>coding.
    >>>>
    >>>>Could you explain a bit more.
    >>>>
    >>>>Thanks.
    >>>>
    >>>>Private Sub TextBox1_KeyDown(ByVal KeyCode As _
    >>>>MSForms.ReturnInteger, ByVal Shift As Integer)
    >>>>If Shift = 2 Then
    >>>>If KeyCode = 86 Then KeyCode = 0
    >>>>End If
    >>>>End Sub
    >>>>
    >>>>Private Sub TextBox1_KeyPress(ByVal KeyAscii _
    >>>>As MSForms.ReturnInteger)
    >>>>Select Case KeyAscii
    >>>>Case 46
    >>>>If InStr(TextBox1.Text, ".") > 0 Then _
    >>>>KeyAscii = 0
    >>>>Case 48 To 57
    >>>>Case Else
    >>>>KeyAscii = 0
    >>>>End Select
    >>>>End Sub
    >>>>
    >>>>---
    >>>>Message posted from http://www.ExcelForum.com/
    >>>
    >>>

    >
    >
     
    Jon Peltier, Mar 23, 2004
    #8
  9. Until Harald returns, you could review Chip Pearson's site:
    http://www.cpearson.com/excel/clipboar.htm



    Jon Peltier wrote:
    >
    > Harald old pal -
    >
    > How do you read the clipboard content? I was letting the paste occur
    > into a textbox, then I parsed that to turn garbage into data. But
    > reading the clipboard content sounds much more elegant.
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > http://PeltierTech.com/Excel/Charts/
    > _______
    >
    > Harald Staff wrote:
    >
    > > Hi Guys
    > >
    > > Just adding a few minor things.
    > >
    > > Jon: The paste code is pretty raw as you noticed. My real life code for
    > > those events is
    > > - cancel
    > > - read the clipboard content
    > > - if acceptable then paste it in, or paste the "acceptable part" in
    > > but it's pretty large and confusing, so I added the cancel part only for
    > > demonstration.
    > >
    > > Steelmaker: Many built-in events provide variables that it's code can
    > > validate and change.For example in ThisWorkbook:
    > >
    > > Private Sub Workbook_BeforeSave(ByVal _
    > > SaveAsUI As Boolean, Cancel As Boolean)
    > > Cancel = True
    > > End Sub
    > >
    > > Now this workbook will never save because we cancel it. Not useful as is,
    > > but add a condition or two to see if the save location is ok, if this and
    > > that is completely filled out, ...
    > >
    > > TextBox1_KeyDown uses Keycode, the number of the keyboard key pressed. "a"
    > > and "A" is the same key, number 65, the diference is Shift, which is 0 for a
    > > and 1 for A. Ctrl is 2. A very useful keycode to trap is 13, the Enter key,
    > > usually meaning that the entry is finished so go to next field or click the
    > > OK button or something.
    > >
    > > TextBox1_KeyPress uses KeyAscii, the resulting ascii value for the entry,
    > > where A is 65 and a is 97. For testing and development I've found that
    > > putting the value in question up in the form caption and/or in the immediate
    > > window is helpful:
    > >
    > > Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    > > Me.Caption = KeyAscii
    > > Debug.Print KeyAscii
    > > End Sub
    > >
    > > HTH. Best wishes Harald
    > >
    > > "Jon Peltier" <> skrev i melding
    > > news:...
    > >
    > >>Well, isn't this nice. This morning I was wondering how I was going to
    > >>tell whether the user typed or pasted his information into the textbox!
    > >>Thanks Dave and Harald.
    > >>
    > >>(An additional coincidence is the OP's handle, given that my real
    > >>technical training is in metallurgy.)
    > >>
    > >>- Jon
    > >>-------
    > >>Jon Peltier, Microsoft Excel MVP
    > >>Peltier Technical Services
    > >>http://PeltierTech.com/Excel/Charts/
    > >>_______
    > >>
    > >>Dave Peterson wrote:
    > >>
    > >>
    > >>>Try adding this to a test form:
    > >>>
    > >>>Option Explicit
    > >>>Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
    > >>> ByVal Shift As Integer)
    > >>> MsgBox KeyCode
    > >>>End Sub
    > >>>
    > >>>And you'll see what each keystroke represents.
    > >>>(hit ctrl and don't let go. Then hit V and you'll see the 86.)
    > >>>
    > >>>You can search VBA's help for "keycode constants" and see vbKeyV and how

    > >
    > > it
    > >
    > >>>represents 86.
    > >>>
    > >>>
    > >>>
    > >>>"thesteelmaker <" wrote:
    > >>>
    > >>>
    > >>>>Thank you very much.
    > >>>>
    > >>>>I'm very impressed.
    > >>>>
    > >>>>I've not come across any reference to keycodes, or anything in you
    > >>>>coding.
    > >>>>
    > >>>>Could you explain a bit more.
    > >>>>
    > >>>>Thanks.
    > >>>>
    > >>>>Private Sub TextBox1_KeyDown(ByVal KeyCode As _
    > >>>>MSForms.ReturnInteger, ByVal Shift As Integer)
    > >>>>If Shift = 2 Then
    > >>>>If KeyCode = 86 Then KeyCode = 0
    > >>>>End If
    > >>>>End Sub
    > >>>>
    > >>>>Private Sub TextBox1_KeyPress(ByVal KeyAscii _
    > >>>>As MSForms.ReturnInteger)
    > >>>>Select Case KeyAscii
    > >>>>Case 46
    > >>>>If InStr(TextBox1.Text, ".") > 0 Then _
    > >>>>KeyAscii = 0
    > >>>>Case 48 To 57
    > >>>>Case Else
    > >>>>KeyAscii = 0
    > >>>>End Select
    > >>>>End Sub
    > >>>>
    > >>>>---
    > >>>>Message posted from http://www.ExcelForum.com/
    > >>>
    > >>>

    > >
    > >


    --

    Dave Peterson
     
    Dave Peterson, Mar 23, 2004
    #9
  10. thesteelmaker

    Jon Peltier Guest

    Thanks, Dave. I thought it was going to be worse than that, API calls
    and the like. Also, thanks, Chip. I should have thought of visiting this
    online encyclopedia.

    - Jon

    Dave Peterson wrote:

    > Until Harald returns, you could review Chip Pearson's site:
    > http://www.cpearson.com/excel/clipboar.htm
    >
    >
    >
    > Jon Peltier wrote:
    >
    >>Harald old pal -
    >>
    >>How do you read the clipboard content? I was letting the paste occur
    >>into a textbox, then I parsed that to turn garbage into data. But
    >>reading the clipboard content sounds much more elegant.
    >>
    >>- Jon
    >>-------
    >>Jon Peltier, Microsoft Excel MVP
    >>Peltier Technical Services
    >>http://PeltierTech.com/Excel/Charts/
    >>_______
    >>
    >>Harald Staff wrote:
    >>
    >>
    >>>Hi Guys
    >>>
    >>>Just adding a few minor things.
    >>>
    >>>Jon: The paste code is pretty raw as you noticed. My real life code for
    >>>those events is
    >>>- cancel
    >>>- read the clipboard content
    >>>- if acceptable then paste it in, or paste the "acceptable part" in
    >>>but it's pretty large and confusing, so I added the cancel part only for
    >>>demonstration.
    >>>
    >>>Steelmaker: Many built-in events provide variables that it's code can
    >>>validate and change.For example in ThisWorkbook:
    >>>
    >>>Private Sub Workbook_BeforeSave(ByVal _
    >>> SaveAsUI As Boolean, Cancel As Boolean)
    >>>Cancel = True
    >>>End Sub
    >>>
    >>>Now this workbook will never save because we cancel it. Not useful as is,
    >>>but add a condition or two to see if the save location is ok, if this and
    >>>that is completely filled out, ...
    >>>
    >>>TextBox1_KeyDown uses Keycode, the number of the keyboard key pressed. "a"
    >>>and "A" is the same key, number 65, the diference is Shift, which is 0 for a
    >>>and 1 for A. Ctrl is 2. A very useful keycode to trap is 13, the Enter key,
    >>>usually meaning that the entry is finished so go to next field or click the
    >>>OK button or something.
    >>>
    >>>TextBox1_KeyPress uses KeyAscii, the resulting ascii value for the entry,
    >>>where A is 65 and a is 97. For testing and development I've found that
    >>>putting the value in question up in the form caption and/or in the immediate
    >>>window is helpful:
    >>>
    >>>Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    >>>Me.Caption = KeyAscii
    >>>Debug.Print KeyAscii
    >>>End Sub
    >>>
    >>>HTH. Best wishes Harald
    >>>
    >>>"Jon Peltier" <> skrev i melding
    >>>news:...
    >>>
    >>>
    >>>>Well, isn't this nice. This morning I was wondering how I was going to
    >>>>tell whether the user typed or pasted his information into the textbox!
    >>>>Thanks Dave and Harald.
    >>>>
    >>>>(An additional coincidence is the OP's handle, given that my real
    >>>>technical training is in metallurgy.)
    >>>>
    >>>>- Jon
    >>>>-------
    >>>>Jon Peltier, Microsoft Excel MVP
    >>>>Peltier Technical Services
    >>>>http://PeltierTech.com/Excel/Charts/
    >>>>_______
    >>>>
    >>>>Dave Peterson wrote:
    >>>>
    >>>>
    >>>>
    >>>>>Try adding this to a test form:
    >>>>>
    >>>>>Option Explicit
    >>>>>Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
    >>>>> ByVal Shift As Integer)
    >>>>> MsgBox KeyCode
    >>>>>End Sub
    >>>>>
    >>>>>And you'll see what each keystroke represents.
    >>>>>(hit ctrl and don't let go. Then hit V and you'll see the 86.)
    >>>>>
    >>>>>You can search VBA's help for "keycode constants" and see vbKeyV and how
    >>>
    >>>it
    >>>
    >>>
    >>>>>represents 86.
    >>>>>
    >>>>>
    >>>>>
    >>>>>"thesteelmaker <" wrote:
    >>>>>
    >>>>>
    >>>>>
    >>>>>>Thank you very much.
    >>>>>>
    >>>>>>I'm very impressed.
    >>>>>>
    >>>>>>I've not come across any reference to keycodes, or anything in you
    >>>>>>coding.
    >>>>>>
    >>>>>>Could you explain a bit more.
    >>>>>>
    >>>>>>Thanks.
    >>>>>>
    >>>>>>Private Sub TextBox1_KeyDown(ByVal KeyCode As _
    >>>>>>MSForms.ReturnInteger, ByVal Shift As Integer)
    >>>>>>If Shift = 2 Then
    >>>>>>If KeyCode = 86 Then KeyCode = 0
    >>>>>>End If
    >>>>>>End Sub
    >>>>>>
    >>>>>>Private Sub TextBox1_KeyPress(ByVal KeyAscii _
    >>>>>>As MSForms.ReturnInteger)
    >>>>>>Select Case KeyAscii
    >>>>>>Case 46
    >>>>>>If InStr(TextBox1.Text, ".") > 0 Then _
    >>>>>>KeyAscii = 0
    >>>>>>Case 48 To 57
    >>>>>>Case Else
    >>>>>>KeyAscii = 0
    >>>>>>End Select
    >>>>>>End Sub
    >>>>>>
    >>>>>>---
    >>>>>>Message posted from http://www.ExcelForum.com/
    >>>>>
    >>>>>
    >>>

    >
     
    Jon Peltier, Mar 23, 2004
    #10
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dan E
    Replies:
    1
    Views:
    607
    John Wilson
    Jul 28, 2003
  2. Thunder5

    Excel VBA - Userform Checkbox/Textbox Problem

    Thunder5, Feb 16, 2004, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    2,217
    Bob Phillips
    Feb 16, 2004
  3. thesteelmaker

    Excel VBA - Userform textbox formatting

    thesteelmaker, Mar 2, 2004, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    4,338
    Harald Staff
    Mar 3, 2004
  4. GregJG

    fill userform textbox from userform listbox clik event

    GregJG, Dec 7, 2008, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    381
    GregJG
    Dec 7, 2008
  5. targante

    Userform Textbox to Worksheet Textbox

    targante, Apr 30, 2009, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    435
    Conan Kelly
    May 2, 2009
Loading...

Share This Page