[userform troubles] can someone please help me out?

[Dinictus]

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.

[Dave Hawley]

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

[Tom Schreiner]

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 ]

[Dinictus]

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 ]

[Tom Schreiner]

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 ]

[Dinictus]

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