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

Thread: [userform troubles] can someone please help me out?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, I`ve been working on a userform for a few days now and it is almost finished.

    However the finishing touch always takes 50% of the time.....

    Now I can`t post the whole file here but perhaps someone will spare me some time and is kind enough to take a look at it?

    There are 2 problems with the userform:

    1: I want to date to fill to 0000 so 23 becomes 0023 automatically. (I uses all types of functions: Right$("000"& etc) formatting the cell with custom format 0000. Nothing seems to work.

    2: If data entered in one of the textboxen is notnumeric an msg appears. Thsi work fine but if you backspace in a textbox to delete any alphanumeric characters you also get the message. I cannot seem to avoid this.

    So, I am aware that this will be a major thing to ask from someone but I would be so glad if someone tried to help me out.

    If someone is interested, I`ll send you the file.

    thnks!
    Dinictus.




  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    I would say you are using the wrong Event for your needs, try this:


    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsNumeric(TextBox1.Value) Then
    MsgBox "No Text Please"
    Cancel = True
    End If
    TextBox1 = Format(TextBox1, "0000")
    Sheet1.Range("A65536").End(xlUp).Cells(2, 1) = TextBox1.Value
    Sheet1.Range("A65536").End(xlUp).Cells(2, 1).NumberFormat = "0000"
    End Sub

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey Din. WHat's Up!

    I don't know the best way perhaps but this will give you the 00's you need



    1: I want to date to fill to 0000 so 23 becomes 0023 automatically. (I uses all types of functions: Right$("000"& etc) formatting the cell with custom format 0000. Nothing seems to work.



    Assign the Day Number to a variable for this:
    txtDinsText.text = Format(DayNumber, "0000")

    Or just fiddle with the date this way
    txtDinsText.text = Format(day(yourdate), "0000")




    2: If data entered in one of the textboxen is notnumeric an msg appears. Thsi work fine but if you backspace in a textbox to delete any alphanumeric characters you also get the message. I cannot seem to avoid this.

    Use the exit event instead of the change event for the texbox(es)

    Tom


    [ This Message was edited by: TsTom on 2002-03-25 03:15 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks guys.

    I`m going to try to implement some of your syntax.


    [ This Message was edited by: Dinictus on 2002-03-25 03:18 ]

    [ This Message was edited by: Dinictus on 2002-03-25 07:11 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Go ahead and post your code so we can see what's up???

    Mail it to me if ya want.

    [ This Message was edited by: TsTom on 2002-03-25 03:18 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Allright: here it is:

    KABOOOM>


    Private Sub TBpanel_Change()
    Worksheets("sheet1").Range("A2").Value = TBpanel
    If Not IsNumeric(TBpanel.Value) Then
    MsgBox "Alleen numerieke data is toegestaan", vbExclamation, "Invalid Entry"
    End If
    End Sub

    Private Sub TBL1_Change()
    Worksheets("sheet1").Range("B2").Value = TBL1
    If Not IsNumeric(TBL1.Value) Then
    MsgBox "Alleen numerieke data is toegestaan", vbExclamation, "Invalid Entry"
    End If
    End Sub

    Private Sub TBL1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    L = Len(TBL1.Value)
    If L < 4 Then
    TBL1.Value = Application.Rept("0", 4 - L) & TBL1.Value
    End If
    End Sub

    **********
    Above:
    My attempt to format to "0000" it works but if you tab out of this taxt box, somehow it skips 2 tabs not 1
    **********


    Private Sub TBL2_Change()
    Worksheets("sheet1").Range("C2").Value = TBL2
    If Not IsNumeric(TBL2.Value) Then
    MsgBox "Alleen numerieke data is toegestaan", vbExclamation, "Invalid Entry"
    End If
    End Sub


    Private Sub TBL3_Change()
    Worksheets("sheet1").Range("D2").Value = TBL3
    If Not IsNumeric(TBL3.Value) Then
    MsgBox "Alleen numerieke data is toegestaan", vbExclamation, "Invalid Entry"
    End If
    End Sub

    Private Sub TBL4_Change()
    Worksheets("sheet1").Range("E2").Value = TBL4
    If Not IsNumeric(TBL4.Value) Then
    MsgBox "Alleen numerieke data is toegestaan", vbExclamation, "Invalid Entry"
    End If
    End Sub

    Private Sub TBL5_Change()
    Worksheets("sheet1").Range("F2").Value = TBL5
    If Not IsNumeric(TBL5.Value) Then
    MsgBox "Alleen numerieke data is toegestaan", vbExclamation, "Invalid Entry"
    End If
    End Sub

    Private Sub TBDL1_Change()
    Worksheets("sheet1").Range("G2").Value = TBDL1
    If Not IsNumeric(TBDL1.Value) Then
    MsgBox "Alleen numerieke data is toegestaan", vbExclamation, "Invalid Entry"
    End If
    End Sub

    Private Sub TBDL2_Change()
    Worksheets("sheet1").Range("H2").Value = TBDL2
    If Not IsNumeric(TBDL2.Value) Then
    MsgBox "Alleen numerieke data is toegestaan", vbExclamation, "Invalid Entry"
    End If
    End Sub

    Private Sub TBDL3_Change()
    Worksheets("sheet1").Range("I2").Value = TBDL3
    If Not IsNumeric(TBDL3.Value) Then
    MsgBox "Alleen numerieke data is toegestaan", vbExclamation, "Invalid Entry"
    End If
    End Sub

    Private Sub TBDL4_Change()
    Worksheets("sheet1").Range("J2").Value = TBDL4
    If Not IsNumeric(TBDL4.Value) Then
    MsgBox "Alleen numerieke data is toegestaan", vbExclamation, "Invalid Entry"
    End If
    End Sub

    Private Sub TBDL5_Change()
    Worksheets("sheet1").Range("K2").Value = TBDL5
    If Not IsNumeric(TBDL5.Value) Then
    MsgBox "Alleen numerieke data is toegestaan", vbExclamation, "Invalid Entry"
    End If
    End Sub


    Private Sub CommandButton1_Click()
    Unload Me
    Rows("2:2").EntireRow.AutoFit
    Rows("2:2").Insert Shift:=xlDownward
    Range("A2").Select
    UserForm1.Show
    End Sub

    Private Sub CommandButton2_Click()
    MsgBox "Om de gegevens handmatig aan te passen kunt u de knop 'SHEET HANDMATIG AANPASSEN' gebruiken." & Chr(13) & "Om hierna weer terug te gaan naar het invoerscherm kunt u gebruik maken van de knop 'INVOERFORMULIER'." & Chr(13) & Chr(13) & "Nadat alle gegevens ingevoerd zijn, kunt u de informatie kopiŽren en opslaan in een andere sheet.", vbInformation
    End Sub

    Private Sub CommandButton3_Click()
    Unload Me
    End Sub


    Private Sub UserForm_Click()

    End Sub

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
  •