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

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)
--
Regards,
Tom Olvy

"Tim" wrote in message
...
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

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

"Tim" wrote in message
...
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

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 a" & 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
"Tim" wrote in message
...
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

Within the design view, check the comboboxe Data
Validation settings.
-----Original Message-----
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


.

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

"Kathy" wrote in message
...
Within the design view, check the comboboxe Data
Validation settings.
-----Original Message-----
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


.

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

--
Regards,
Tom Ogilvy


"Tim" wrote in message
...

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

"Kathy" wrote in message
...
Within the design view, check the comboboxe Data
Validation settings.
-----Original Message-----
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


.