PDA

View Full Version : Solved: Textbox formatting question



lhardee
05-05-2008, 05:51 PM
Hello All,

I've created a textbox where I can control the user's entries whereby the first entry is automatically an "S" upon entering the textbox. The following entries are numerical values only.

See code below:

I know there is a better way to code this. Maybe a Case where the 1st entry is only an "S" and the following entries are numerical.

In short, I would like to get a better understanding of how to control what value can be entered for each entry.



Private Sub Segment_Enter()
Me.Segment.BackStyle = fmBackStyleOpaque
Me.Segment.Text = "S"
Me.Segment.SelStart = 1
Me.Segment.SetFocus

End Sub

Private Sub Segment_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Asc("-")
If InStr(1, Me.Segment.Text, "-") > 0 Or Me.Segment.SelStart > 0 Then
KeyAscii = 0
End If
Case Asc(".")
If InStr(1, Me.Segment.Text, ".") > 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
End Sub



Thanks

lhardee

mikerickson
05-05-2008, 11:14 PM
Private Sub TextBox1_Change()
Static abort As Boolean
Dim sLStart As Long, sLLength As Long

If abort Then abort = False: Exit Sub
With Me.TextBox1
sLStart = .SelStart
sLLength = .selLength
abort = True
If .Text Like "S[1-9]*" Then
.Text = "S" & Int(Val(Mid(.Text, 2)))
Else
.Text = "S"
End If
.SelStart = Application.Max(sLStart, 1)
.selLength = sLLength
End With
End Sub

lhardee
05-06-2008, 06:26 PM
Thanks Mike,

I'll mark this as sovled. If you should read this and have the time, I would love to know what the following is doing:


.SelStart = Application.Max(sLStart, 1)
.selLength = sLLength



Thanks

lhardee :thumb

mikerickson
05-06-2008, 09:53 PM
As written, every Change event causes the routine to write to the textbox.
VBA writting to the textbox causes the cursor to go to the end of the string.

The variables sLStart and sLLength preserve the existing cursor position and restore it after the formatted string is written to the textbox.

However, .SelStart=0 results in the cursor being before the required "S". Which I find both visualy and conseptualy weird.
Hence the statment, requiring the restored cursor's position to be after the mandatory "S".


.SelStart = Application.Max(sLStart, 1)

lhardee
05-07-2008, 06:12 PM
Hi Mike and All,

I ran into a slight problem with the code provided. I am unable to enter multiple zeros after the letter S. I changed the code from:


If .Text Like "S[1-9]*" Then


To:

If .Text Like "S[0-9]*" Then


This only allowed me to enter a zero 1 time before any other zeros where deleted.

Anyone know how I can fix this?

Thanks,

lhardee