TextBox validation code adjustment needed

[kelly mort]

This code was written for me by one of the gurus here way back. It's working great just that there is some behavior of it that I wish I could improve upon it.

When I want to edit already keyed data in the textbox , the cursor jumps to the last character in the TextBox.

For example if I have "NICE BOOK" inside the TextBox, except I place the cursor in front of the "K", the cursor will always jump to be in front of the "K" when I try editing behind the K.

I want it to stay as I edit just like making corrections in a text editor.

How do I get it to do what I want?

Private Sub freg2_Change()
    Static LastText$
    Static SecondTime As Boolean
    If Not SecondTime Then
        With freg2
            If .Text Like "*[!A-Za-z. -1-5]*" Or .Text Like "*..*" Or _
                .Text Like "*--*" Or .Text Like "*  *" Or .Text Like "*-.*" Or _
                .Text Like "* .*" Or .Text Like "[. -]" Or .Text Like "[1-5]" Then
                Call Beep
                SecondTime = True
                .Text = LastText
            Else
                LastText = .Text
            End If
            For i = 1 To Len(.Text) - 1
                If Mid(.Text, i, 2) Like ".[A-Za-z1-5]" Then
                    .Text = Application.Replace(.Text, i + 1, 0, " ")
                End If
            Next i
            .Text = Replace(Replace(Replace(.Text, " - ", "-"), "- ", "-"), " -", "-")
        End With
    End If
    SecondTime = False
End Sub

Thanks in advance.

 

[kelly mort]

@GWteB
I think I missed one thing during the testing process.
I did not load the textbox with data from my dB - when I look up data from the dB and load the data to my textboxes, the freg2 stays blank.

I am having the feelings the code that is preventing pasting into it is a factor.
How do I get around that?

 

[Rick Rothstein]

Hello @Rick Rothstein
I am still having the same issue though I am using the exact same code you posted recently.

So I don't have to try and decipher your code, what exactly are you attempting to block from being put into the textbox?

 

[kelly mort]

I am blocking :
1. Hyphen, space, period or number as first characters into the textbox is not allowed .
2. No pasting into the textbox.
3. No double spaces
4. A hyphen must not be immediately followed by space or period
5. A space must not be followed by a period or hyphen.

Which is to say that I am allowing texts, numbers from 1 to 5, space, hyphen and period only.

 

[Rick Rothstein]

My code, as originally created, will not block pasting of text that meets the specified criteria but will block pasting of text that does not meet the criteria. Before I create code for you, is that okay?

By the way, the criteria for the first character is that it must be only be a letter, is that correct?

 

[kelly mort]

Yes those are okay and correct.

 

[Rick Rothstein]

Okay, this global variable couple with this set of procedures should do what you want. Place all of it in the UserForm's code module.

Dim LastPosition As Long

Private Sub freg2_Change()
  Static LastText As String
  Static SecondTime As Boolean
  If Not SecondTime Then
    With freg2
      If .Text Like "[!A-Za-z]*" Or .Text Like "*[!A-Za-z0-9. -]*" Or _
        .Text Like "*-[. ]*" Or .Text Like "* [.-]*" Or .Text Like "*  *" Then
        Beep
        SecondTime = True
        .Text = LastText
        .SelStart = LastPosition
      Else
        LastText = .Text
      End If
    End With
  End If
  SecondTime = False
End Sub

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

Private Sub freg2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  With freg2
    LastPosition = .SelStart
    'Place any other KeyPress checking code here
  End With
End Sub

 

[kelly mort]

Yes!
This one is cooler.

I am very grateful.
Have a great moment.