Excel Userform TextBox - Change partial string in textbox with command button

**Nu2Java** · 05-05-2012, 07:45 PM

Hello, I am trying to find a way to replace part of a string in a textbox. I have the user enter a job number and I have it verify they have entered the side they are working on which is just 2 choices "-TOP" or "-BOTTOM". So when they work on the same job number, I would like to replace one for the other. If they have this: "98-1002-600-TOP" and are finished, I want to press the button and replace just the -TOP with -BOTTOM. Also work in the opposite way if they start BOTTOM first, then it will change to -TOP. Any ideas on this? Your help is greatly appreciated.

Here is the code I have for checking the end of the job number.... Now I just need to know how to replace one for the other when I change jobs.

'check for -top / -btm entry in field
Private Sub txtCCA_Exit(ByVal Cancel As MSForms.ReturnBoolean)

 With Me.txtCCA
        If Len(.Value) Then
            If CheckIt(.Value) Then
                MsgBox "*Enter -TOP or -BTM *", vbInformation, .Value
                Cancel = True
            End If
        End If
    End With
End Sub

'check for -top / -btm entry
Private Function CheckIt(ByVal txt As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d*(-TOP|-BTM)\d*"
        .ignorecase = True
        CheckIt = Not .test(txt)
    End With
End Function

**Nu2Java** · 05-07-2012, 08:23 PM

Anyone ?? Someone ?? Any ideas ??

**jindon** · 05-07-2012, 09:08 PM

Do you want to toggle "TOP" and "BTM" ?

Private Sub CommandButton1_Click()
    Dim x
    x = Split(txtCCA.Value, "-")
    If x(UBound(x)) = "TOP" Then
        x(UBound(x)) = "BTM"
    Else
        x(UBound(x)) = "TOP"
    End If
    txtCCA.Value = Join$(x, "-")
End Sub

**Nu2Java** · 05-07-2012, 09:46 PM

Hello jindon .... thank you VERY MUCH!! This works exactly how I need it to. You nailed it, yes I wanted to toggle if it is BTM then change to TOP and if TOP change to BTM. Thanks again for your time and help, much appreciated.