Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Textboxes in Userforms

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Melbourne, Australia
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm using a userform for data entry, which includes a textbox for a numeric code. My problem is that the textbox automatically kills all leading zeros... ie... 0104 will become 104. However i need these leading zeros as the are part of the code. Can anyone help me with this?

    Cheers...

    Daniel

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Daniel,

    I hope you are not too dazed.

    Actually, you must set up the textbox for string input, because numerically there is no difference between 0104 and 104--they are both the value one hundred four. What I would recomend is that right after textbox entry you check the string using the VBA IsNumeric function to ensure that the string represents a legal numeric value. But from that point on, since you want 0104 to be distinct from 104 you must deal with it as a string. The second you convert the code to any numeric type (Integer, Long, Single, Double, etc.) it will lose its "distinctness."
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could also try with this code (Which converts the text to a string)


    Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1 = Format(TextBox1, "0000")
    End Sub

    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    Melbourne, Australia
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your suggestions guys...

    Just wondering, how would i set a textbox for string entry only, as opposed to numeric? It there an option somewhere that i can select?

    Cheers...

    Dan


  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Melbourne, Australia
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Don't worry.... i've sorted it... i just set the cells i was putting the data into to be text format and its solved all my worries...



    Cheers

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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