problem formating text box

[Phillip2]

I wanted to code a text box so that when anytext was entered it would be formated as [anyTextIVR.aef]. However, if the text box was blank it would remain empty. I was able to do this by using the code

If TBscripts1.Value <> "" Then Cells(LastRow, 31).Value = "[" & TBscripts1.Text & "IVR.aef]"

However, If open a record that already has data in this textbox and save it, it will format the text again. So it appears appear like this [[aaaaIVR.aef]IVR.aef].

I've tried several things to attempt to have it look at the first character and skip if it was a "[", but I 'm not having much luck. What would be the best way to write this?

If TBscripts1.Value <> "" Or Left(TBscripts1.Value, 1) <> "[" Then Cells(LastRow, 31).Value = "[" & TBscripts1.Text & "IVR.aef]"

Then I tried using And.

When I used "And", it would delete and text that was in this box.See the pictures for details.


Thank you any help is appreciated.


____________Scripts Frame________________________

'Scripts



If TBscripts1.Value <> "" And Left(TBscripts1.Value, 1) <> "[" Then Cells(LastRow, 31).Value = "[" & TBscripts1.Text & "IVR.aef]"
If TBscripts2.Value <> "" And Left(TBscripts2.Value, 1) <> "[" Then Cells(LastRow, 32).Value = "[" & TBscripts2.Text & "IVR.aef]"
If TBscripts3.Value <> "" And Left(TBscripts3.Value, 1) <> "[" Then Cells(LastRow, 33).Value = "[" & TBscripts3.Text & "IVR.aef]"
If TBscripts4.Value <> "" And Left(TBscripts4.Value, 1) <> "[" Then Cells(LastRow, 34).Value = "[" & TBscripts4.Text & "IVR.aef]"
If TBscripts5.Value <> "" And Left(TBscripts5.Value, 1) <> "[" Then Cells(LastRow, 35).Value = "[" & TBscripts5.Text & "IVR.aef]"
If TBscripts6.Value <> "" And Left(TBscripts6.Value, 1) <> "[" Then Cells(LastRow, 36).Value = "[" & TBscripts6.Text & "IVR.aef]"
If TBscripts7.Value <> "" And Left(TBscripts7.Value, 1) <> "[" Then Cells(LastRow, 37).Value = "[" & TBscripts7.Text & "IVR.aef]"
If TBscripts8.Value <> "" And Left(TBscripts8.Value, 1) <> "[" Then Cells(LastRow, 38).Value = "[" & TBscripts8.Text & "IVR.aef]"

 

[Fluff]

How about

If TBscripts1.Value <> "" And Left(TBscripts1.Value, 1) <> "[" Then
   Cells(LastRow, 31).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
   Cells(LastRow, 31).Value = TBscripts1.Text
End If

 

[Phillip2]

I appreciate your help. However I still have a issue. I'm getting a Compile error, Else with If.

If TBscripts1.Value <> "" And Left(TBscripts1.Value, 1) <> "[" Then Cells(LastRow, 31).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Cells(LastRow, 31).Value = TBscripts1.Text
End If


If TBscripts1.Value <> "" And Left(TBscripts2.Value, 1) <> "[" Then Cells(LastRow, 32).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Cells(LastRow, 31).Value = TBscripts1.Text
End If


If TBscripts1.Value <> "" And Left(TBscripts3.Value, 1) <> "[" Then Cells(LastRow, 33).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Cells(LastRow, 31).Value = TBscripts1.Text
End If

If TBscripts1.Value <> "" And Left(TBscripts4.Value, 1) <> "[" Then Cells(LastRow, 34).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Cells(LastRow, 31).Value = TBscripts1.Text
End If

If TBscripts1.Value <> "" And Left(TBscripts5.Value, 1) <> "[" Then Cells(LastRow, 35).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Cells(LastRow, 31).Value = TBscripts1.Text
End If

If TBscripts1.Value <> "" And Left(TBscripts6.Value, 1) <> "[" Then Cells(LastRow, 36).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Cells(LastRow, 31).Value = TBscripts1.Text
End If

If TBscripts1.Value <> "" And Left(TBscripts7.Value, 1) <> "[" Then Cells(LastRow, 37).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Cells(LastRow, 31).Value = TBscripts1.Text
End If

If TBscripts1.Value <> "" And Left(TBscripts8.Value, 1) <> "[" Then Cells(LastRow, 38).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Cells(LastRow, 31).Value = TBscripts1.Text
End If

 

[Fluff]

You need to do it like I showed in post#2.

Also when posting code, please use code tags the <vba/> icon in the reply window. It makes it a lot easier to read.

 

[Phillip2]

It's amazing what happens when you write it correctly. Thank you so much.

 

[Fluff]

You're welcome & thanks for the feedback