Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formating TextBox on User Form (2003 SP2)

    Good afternoon

    in a UserForm I have 2 buttons one to add stock and one to remove stock in order to make calculations I need numeric values so after reading up I realise that I needed to add .text to this part of my code .Cells(r, c) = Me.TxtQuantity <font color=red>.Text </font color=red>, so far so good, if possible though I would like to do 2 things

    1. Whole numbers only

    How could I only have whole numbers input because there will never be a part of a box

    2. Negative numbers

    In my remove stock code I would like it to always interpret the whole number as a negative

    I realise that I could just tell people to use the - for 2. above but I wanted to 'lock' the text cell to use numbers only and have experimented with this

    Private Sub TxtQuantity_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 TxtQuantity_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

    Which makes the text box only accept numbers but if I try to type in, for example, -10 on the Remove Stock form it will not accept it because the - is not a number

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formating TextBox on User Form (2003 SP2)

    I don't understand why you refer to TextBox1 in the KeyDown event procedure of TxtQuantity, and I don't understand why you allow the decimal point under some conditions if you want to enter only whole numbers.

    You could allow a minus sign like this (45 is the ASCII code for the minus sign):

    Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    Case 45, 48 To 57
    Case Else
    KeyAscii = 0
    End Select
    End Sub

    But this would allow the user to enter 34-45 as well as -12.

    You could use the Before Update and After Update events of the text box instead:

    Private Sub txtQuantity_AfterUpdate()
    Me.txtQuantity = Val(Me.txtQuantity)
    End Sub

    Private Sub txtQuantity_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsNumeric(Me.txtQuantity) Then
    Cancel = True
    MsgBox "Enter a valid number or face a cruel death!", vbCritical
    ElseIf Not Fix(Val(Me.txtQuantity)) = Val(Me.txtQuantity) Then
    Cancel = True
    MsgBox "Whole numbers only, you nitwit!", vbExclamation
    End If
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formating TextBox on User Form (2003 SP2)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>I love how "user friendly" your messages are... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formating TextBox on User Form (2003 SP2)

    Hi Hans

    Thanks for that, like Steve said the user friendly messages are good, I will leave them in as my drivers thinks I am a reincarnated pirate captain anyway!!

    I forgot to take out the reference to textbox1 to tell you the truth when I went through doing some renaming and as it had not errored it went unotticed

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formating TextBox on User Form (2003 SP2)

    You might try to be more creative with your insults. Here is a "Pirate Insulter" (in XL of course) I created for someone at work for International Talk Like A Pirate Day. It creates random insults by making random selections from columns of text. You can, of course use other lists of words for other randome phrases...

    Steve
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formating TextBox on User Form (2003 SP2)

    Thanks Steve

    I have already wasted an 1/2 on the Sir Roger saga and will have to finish it tonight!! I have downloaded the workbook and will be ready with many an insult come next TLAPD

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •