Hi,

How to restrict textbox in userform for the following entry

SEP 08

I mean first three cheractors Alphabatic and space and two cheractors
number.

Regards.

Shahzad

What exactly do you mean by "restrict textbox"? If you are talking about
"approving" an entry when the user tries to leave the TextBox, then you can
use something like this...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not TextBox1.Text Like "[A-Za-z][A-Za-z][A-Za-z] ##" Then
Cancel = True
MsgBox "Your entry is not in the proper format!"
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
End If
End Sub

If you have something else in mind when you say "restrict", you are going to
have to tell us what it is that you want to do.

Rick


wrote in message
...


Hi,

How to restrict textbox in userform for the following entry

SEP 08

I mean first three cheractors Alphabatic and space and two cheractors
number.

Regards.

Shahzad

On May 27, 10:07*am, "Rick Rothstein \(MVP - VB\)"
wrote:
What exactly do you mean by "restrict textbox"? If you are talking about
"approving" an entry when the user tries to leave the TextBox, then you can
use something like this...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
* If Not TextBox1.Text Like "[A-Za-z][A-Za-z][A-Za-z] ##" Then
* * Cancel = True
* * MsgBox "Your entry is not in the proper format!"
* * TextBox1.SelStart = 0
* * TextBox1.SelLength = Len(TextBox1.Text)
* End If
End Sub

If you have something else in mind when you say "restrict", you are going to
have to tell us what it is that you want to do.

Rick

wrote in message

...





Hi,

How to restrict textbox in userform for the following entry

SEP 08

I mean first three cheractors Alphabatic and space and two cheractors
number.

Regards.

Shahzad- Hide quoted text -

- Show quoted text -


Hi,

Actually I have a userform for the Data Entry, there is one field for
Part No.
my parts no starts like this

PLU 89
ELE 55
KIT 45
ENG 99

It means the first three cheractors should be text and last two
cheractors are Numaric.

hope it is clear to you.

Regards.

Actually I have a userform for the Data Entry, there is
one field for Part No. my parts no starts like this

PLU 89
ELE 55
KIT 45
ENG 99

It means the first three cheractors should be text and
last two cheractors are Numaric.

I understood that part... what I want to know is **when** do you want to
restrict the entry. Are you trying to stop the user from typing a bad entry
in while he/she is actually typing? Or do you have an OK button and when
pressed, you want to parse the user's entry to see if it conforms to your
pattern and, if not, send the focus back into the TextBox for the user to
correct the entry? Or did you have some other method in mind?

Rick

On May 27, 11:08*am, "Rick Rothstein \(MVP - VB\)"
wrote:
Actually I have a userform for the Data Entry, there is
one field for Part No. my parts no starts like this

PLU 89
ELE 55
KIT 45
ENG 99

It means thefirstthreecheractorsshould betextand
last twocheractorsare Numaric.

I understood that part... what I want to know is **when** do you want to
restrict the entry. Are you trying to stop the user from typing a bad entry
in while he/she is actually typing? Or do you have an OK button and when
pressed, you want to parse the user's entry to see if it conforms to your
pattern and, if not, send the focus back into the TextBox for the user to
correct the entry? Or did you have some other method in mind?

Rick

Hi,

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.

Regards

Shahzad

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

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

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

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

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.

I'm glad that we got this settled for you... thank you for your feedback and
good wishes.

Rick