preventing / converting 'illegal' characters in file-save text string

[Tim]

Hi All,

some help would be gratefully appreciated: i have written a small piece of
vba to create a file name from a mixture of comboboxes (userform) and cells
(worksheet), then saves the file (saveas).

it works fine, but i want some automated way of making sure no 'illegal'
characters are used in those 'boxes / cells (eg, ':' or '\'). is there a
single instruction that looks for all such charatcers (that i could then use
in a find-replace or if-then statement?). currently, it causes an error and
stops the macro or if '\' occurs somewhere in the string, it results in an
extra directory being formed (named from the contents to the left of the
'\'), which is at best confusing!

tia,

tim

 

[Tom Ogilvy]

This should take such a small amount of time, why not just "fix" the name

Dim varr as Variant, fName as String

varr = Array("|",":","/","\")
for i = lbound(varr) to ubound(varr)
fname = replace(fname,varr(i),"")
Next

Where fName would hold the just the proposed file name (not including the
drive/path)

 

[Bernie Deitrick]

Tim,

Clean up the file name using the function below, with usage example. You can
exclude further characters by including them in the BadChar string.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myFName As String
myFName = InputBox("Enter an invalid filename")
myFName = ValidFileName(myFName)
MsgBox "This is now valid: " & myFName
End Sub

Function ValidFileName(Proposed As String) As String
Dim BadChar As String
Dim t As Integer

BadChar = "`~!@#$%^&*()+=-{}[]\|;':"",<>/?"

For t = 1 To Len(Proposed)
If InStr(1, BadChar, Mid(Proposed, t, 1)) = 0 Then
ValidFileName = ValidFileName & Mid(Proposed, t, 1)
End If
Next t

End Function

 

[p]

Const MsgString As String = "Enter a name for this category:" & vbCrLf &
vbCrLf _
& "Please make sure that the name does not exceed" & vbCrLf _
& "31 characters including spaces." & vbCrLf & vbCrLf _
& "Permitted word separators are:" & vbCrLf _
& "Spaces [ ], Commas [ , ], Underscores [ _ ]," & vbCrLf _
& "Dashes [ - ] and Ampersands [ & ]." & vbCrLf & vbCrLf _
& "Make sure that the name does not contain any of the following
characters:" & vbCrLf _
& "/ \ ? * [ ] ! =" _

If CheckName(NewTabName) = True Then
MsgBox Prompt:=MsgString, _
Buttons:=vbCritical + vbOKOnly, _
title:="Help", _
HelpFile:=ThisWorkbook.Path & "\"
& "Internet Links Organiser.chm", _
Context:=10004
Else
'.....
end if
Function CheckName(ByVal NewSheetName As String)
Dim NonValidChars(8), j
Dim bFound As Boolean
Dim pos
NonValidChars(1) = "["
NonValidChars(2) = "]"
NonValidChars(3) = "*"
NonValidChars(4) = "/"
NonValidChars(5) = "\"
NonValidChars(6) = "?"
NonValidChars(7) = "!"
NonValidChars(8) = "="
For j = 1 To 8
If InStr(NewSheetName, NonValidChars(j)) > 0 Then
bFound = True
End If
Next j
CheckName = bFound
End Function

 

[Kathy]

Within the design view, check the comboboxe Data
Validation settings.

 

[Tim]

thank you all. it now does what i wanted.

Tom - I want an automated way as only some entries used for making the file
name are already completed - others will be changed in my absence (and i
can't trust people to correct / alter them before saving)

Kathy - I didn't know there were data validation settings for comboboxes on
userforms (and still don't know how to get to them if they are there

Thank you for your help,

Tim

 

[Tom Ogilvy]

There aren't any validation settings for comboboxes from the MSforms2.0
library which is what is provided for Userforms/Excel/Office.