In a textbox in my userform only one @ is allowed. How can VBA see the
second @ and delete it?

msgbox len(tb.text) - len(replace(tb.text,"@","")

Replace was added in xl2k.

use application.substitute() in xl97.

Gert-Jan wrote:

In a textbox in my userform only one @ is allowed. How can VBA see the
second @ and delete it?

--

Dave Peterson

Sorry, but that doesn't work: it replaced al the mentioned characters. I
using this code for NOT WANTED characters:

Private Sub aantalpersonen2_Change()
Dim i As Long
For i = 1 To Len(Me.aantalpersonen2.Text)
Select Case Asc(Mid(Me.aantalpersonen2.Text, i, 1))
Case 46, 48 To 57
Case Else
Dim Msg, Style, Title, Response
Msg = Range("error2").Value
Style = vbInformation
Title = Range("naam").Value
Response = MsgBox(Msg, Style, Title)
Me.aantalpersonen2.Value = Left(Me.aantalpersonen2.Text,
Len(Me.aantalpersonen2.Text) - 1)
Exit For
End Select
Next i
End Sub

I suppose it should be possible to count the ASC 46, and if there is already
one the next one will be deleted.


"Dave Peterson" schreef in bericht
...
msgbox len(tb.text) - len(replace(tb.text,"@","")

Replace was added in xl2k.

use application.substitute() in xl97.

Gert-Jan wrote:

In a textbox in my userform only one @ is allowed. How can VBA see the
second @ and delete it?

--

Dave Peterson

The msgbox didn't replace any characters.

It showed how many @'s were in the textbox.

if (len(tb.text) - len(replace(tb.text,"@","")) = 1 then
'ok
else
'too many or not enough
end if


Gert-Jan wrote:

Sorry, but that doesn't work: it replaced al the mentioned characters. I
using this code for NOT WANTED characters:

Private Sub aantalpersonen2_Change()
Dim i As Long
For i = 1 To Len(Me.aantalpersonen2.Text)
Select Case Asc(Mid(Me.aantalpersonen2.Text, i, 1))
Case 46, 48 To 57
Case Else
Dim Msg, Style, Title, Response
Msg = Range("error2").Value
Style = vbInformation
Title = Range("naam").Value
Response = MsgBox(Msg, Style, Title)
Me.aantalpersonen2.Value = Left(Me.aantalpersonen2.Text,
Len(Me.aantalpersonen2.Text) - 1)
Exit For
End Select
Next i
End Sub

I suppose it should be possible to count the ASC 46, and if there is already
one the next one will be deleted.

"Dave Peterson" schreef in bericht
...
msgbox len(tb.text) - len(replace(tb.text,"@","")

Replace was added in xl2k.

use application.substitute() in xl97.

Gert-Jan wrote:

In a textbox in my userform only one @ is allowed. How can VBA see the
second @ and delete it?

--

Dave Peterson

--

Dave Peterson