[userform troubles] can someone please help me out?

Dinictus

Board Regular
Joined
Mar 19, 2002
Messages
162
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top