Removing spaces from a users entry

[sametch]

I have a field for entering URL's and I would like to detect if a user accidentally types in a "space" character and either prompt them with a message box or replace the space with _

Does anyone know how I can detect the prescence of a space character in a string using VBA

 

[Jack Cowley]

This function should do what you want. I did not add a message box but you can easily do that.

Public Function fReplaceSpaces(strEntry As Variant) As String

Dim strTemp As String
Dim x As Integer

If IsNull(strEntry) Then Exit Function

For x = 1 To Len(strEntry)
If Mid(strEntry, x, 1) = " " Then
strTemp = strTemp & "_"
Else
strTemp = strTemp & Mid(strEntry, x, 1)
End If
Next x

fReplaceSpaces = strTemp

End Function

hth,
Jack

 

[raskew]

Here's another approach that relies on the InStr() function:

Function Removeomatic(ByVal pstr As String, ByVal pchar As String) As String
'*******************************************
'Name:      Removeomatic (Function)
'Purpose:   Removed specified characters from a string.
'Inputs:    from debug window:  ? Removeomatic("123-45-6789", "-")
'Output:    123456789
'*******************************************

Dim strHold As String
strHold = RTrim(pstr)
Do While InStr(strHold, pchar) > 0
  strHold = Left(strHold, InStr(strHold, pchar) - 1) & Mid(strHold, InStr(strHold, pchar) + 1)
Loop
Removeomatic = strHold
End Function

 

[ghudson]

I would prefer to alert the user that they keyed a space and that the URL field can
not contain a space. This will give the user the opportunity to fix the error themselves
instead of replacing the space with another character that they might not want.

Assuming the text box field name is [tbURL], I am testing for the space key being
pressed in the OnKeyPress event of the text box tbURL.

Private Sub tbURL_KeyPress(KeyAscii As Integer)
On Error GoTo Err_tbURL_KeyPress
    
    If KeyAscii = Asc(" ") Then
        KeyAscii = 0
        Beep
        MsgBox "The 'URL' field can not contain a space.", vbInformation, "Invalid Space"
    Else
        'Do nothing
    End If
    
Exit_tbURL_KeyPress:
    Exit Sub
    
Err_tbURL_KeyPress:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_tbURL_KeyPress
    
End Sub

HTH

 

[sametch]

What a great forum, thanks for so many responses. I will try them later tonight with interest.

sametch

 

[sametch]

Thanks for all your help I ended up combining bits from different suggestions and coming up with:

Function ReplaceSpaces(strEntry As Variant) As String

On Error GoTo Err_ReplaceSpaces

Dim strTemp As String
Dim i As Integer
Dim booSpacesFound As Boolean

booSpacesFound = False

If IsNull(strEntry) Then Exit Function

For i = 1 To Len(strEntry)
If Mid(strEntry, i, 1) = " " Then
strTemp = strTemp & "-"
booSpacesFound = True
Else
strTemp = strTemp & Mid(strEntry, i, 1)
End If
Next i

If booSpacesFound Then
MsgBox "You have entered filenames containing spaces. Spaces are not allowed in filenames and have been replced with a - symbol", vbInformation, "Warning"
End If

ReplaceSpaces = strTemp

Exit_ReplaceSpaces:

Exit Function

Err_ReplaceSpaces:

MsgBox Err.Number & " " & Err.Description
Resume Exit_ReplaceSpaces

End Function

It works fine.

Sametch

 

[Jack Cowley]

I'm glad you have your code working the way you want. Continued success with your project.

Jack