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
How to restrict textbox in userform for the following entrySEP 08I mean first three cheractors Alphabatic and space and two cheractors
number.
Shahzad- Hide quoted text -
- Show quoted text -
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
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
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
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
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
now I got the solution exactly what I need.
Once again thank you very much.
May Allah pleased with you.
with best regards.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.