On May 27, 8:00*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Okay, I think I've got it... the following code should prevent the user from
entering improper data. Copy/Paste the code below into the UserForm's code
window. If you already have coded Change, MouseDown, KeyPress or Exit event
procedures (for functionality other than parsing the TextBox for the "shape"
of its entry), you will need to put that code in the appropriate event
procedures below in the locations indicated by the comments.

'******************** START OF CODE ********************
Dim LastPosition As Long

Private Sub TextBox1_Change()
* Static LastText As String
* Static SecondTime As Boolean
* If Not SecondTime Then
* * With TextBox1
* * * If .Text Like "* [A-Z]*" Or .Text Like "*# *" Or _
* * * * *.Text Like "* * *" Or .Text Like "*#*#*#*" Or _
* * * * *.Text Like "*[A-Z]*[A-Z]*[A-Z]*[A-Z]*" Or _
* * * * *.Text Like "*#*[A-Z]*" Or Len(.Text) 6 Or (Len(.Text) = 6 _
* * * * *And Not .Text Like "[A-Z][A-Z][A-Z] ##") Then
* * * * Beep
* * * * SecondTime = True
* * * * .Text = LastText
* * * * .SelStart = LastPosition
* * * Else
* * * * LastText = .Text
* * * * ' *Place any other Change event code here
* * * End If
* * End With
* End If
* SecondTime = False
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
* If Len(TextBox1.Text) < 6 Then
* * Beep
* * Cancel = True
* End If
* ' *Place any other Exit event code here
End Sub

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As _
* * * * * * * * * * * * *Integer, ByVal X As Single, ByVal Y As Single)
* With TextBox1
* * LastPosition = .SelStart
* * ' *Place any other MouseDown event code here
* End With
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
* With TextBox1
* * LastPosition = .SelStart
* * If KeyAscii = 97 And KeyAscii <= 122 Then KeyAscii = KeyAscii - 32
* * ' *Place any other KeyPress checking code here
* End With
End Sub
'******************** END OF CODE ********************

Now, as to your other question about placing the contents of the TextBox
into Column K after the last piece of data in that column, use this Click
event code for your OK button (make sure the button name is correct in the
event header)...

Private Sub CommandButton1_Click()
* Dim LastRow As Long
* With Worksheets("DailySheet")
* * LastRow = .Cells(Rows.Count, "K").End(xlUp).Row
* * If LastRow 1 Or (LastRow = 1 And Len(.Range("K1").Value) 0) Then
* * * LastRow = LastRow + 1
* * End If
* * .Cells(LastRow, "K").Value = TextBox1.Text
* End With
End Sub

This code should also be placed in the UserForm's code window.

Rick

wrote in message

...
On May 27, 11:59 am, "Rick Rothstein \(MVP - VB\)"

wrote:
Yes I am trying to stop the user from typing a bad entry
in while he/she is actually typing, as well as I want when I
hit Ok button then it will will show in the worksheet like this
Format "ENG 77" .

pls help me it is important for me.

Okay, that request is doable, but not easily so. I am just about to go to
sleep for the night, so I won't be able to work on the code for you until
(my) tomorrow sometime. I'll work on it when I get up and post it later on
in the day. By the way, where in the worksheet will you want it placed?

Rick

Thanks for reply.

I want to place this entry in "DailySheet", Column "K"

Regards

Hi Mr. Rick,

I received your mail. Thank you very much for your support, now I got
the solution exactly what I need.

Once again thank you very much.

May Allah pleased with you.

with best regards.

Shahzad