Data Validation for symbols

[weefisheads]

I have a spreadsheet that creates a named file using data entered into a field. For a hypothetical example, people can put their favorite car in A2 and, when the macro is run, it will SAVE AS the spreadsheet and name it with the value of A2. Problem is, some folks don't follow instructions and do things like HONDA / INSIGHT, or else something using symbols that are not valid for file names. Bloody slashes!!

I know there are lots of ways to use Data Validation to prevent certain entries, but is there any way to prevent certain symbols? If not, can anyone help me with an Event Handler that would evaluate the data for certain symbols once the data is entered?

Many thanks for any help I can get...

dB

 

[jbeaucaire]

You can CHEAT! Hehe. Let them put anything they want in A2, and WAAAY off in the distance, in cell AA2, put this formula:

=TRIM(SUBSTITUTE(CLEANALL(A2),"\","")

Now, this uses a new function called CLEANALL you'll need to add. This nifty User Defined Function lets you pick any character(s) you want to strip out completely. It doesn't work with for backslashes, which is why we added the Substitute function above to do that, too. Here's the code:

Function CleanAll(txt As String) As String
With CreateObject("VBScript.RegExp")
'enter all characters to be stripped out into the pattern
    .Pattern = "[/?><][}{;:@#$%^*!]"
    .Global = True
    CleanAll = .Replace(txt, "")
End With
End Function

In the section called .Pattern, you can edit the characters listed between the brackets [ ] to only the characters you want to remove.

To add this code:
Press Alt-F11 to open the VBEditor
Insert > Module
Paste in the code above
Alt-Q to close the editor
Save the sheet

Try the new function. Enjoy!

The point of all this...change your "save" macro to save the filename based on your "cleaned up" version in cell AA2.
========
TIP: I've editted this .Pattern to list all small and capital letters which turns this function into a nifty RemoveLetters tool.

 

[weefisheads]

Woa - would never have thought to do it that way. However, couldn't the formula - hidden way off somewhere, work just fine without the TRIM part? For my purpose I'd prefer a file named I really, really, like HONDA'S more than any other car vs IreallyreallylikeHonda'smorethananyothercar

Or is there some vital reason why it MUST be TRIMed?

Besides that, methinks this is just what I needed!

dB

 

[jbeaucaire]

Trim only removes LEADING and TRAILING spaces, if they exist at all. It won't touch the spaces within the string. If you wanted to remove ALL spaces within the string, too, I'd tell you to include a space in the .Pattern line of the function. Then you wouldn't need a TRIM function at all, they'd all be gone.

It only must be trimmed if someone oddly added one of your "stripping" characters to the front or end of the string. Let's say you were stripping $ and @ symbols and the user entered

$ My Favorite Car $ @ home @

The function would strip the symbols and leave two leading and two trailing spaces, which is odd, so the TRIM function cleans it up to start and end with the words only.

It's good to go. Try it out fully before you doubt it.

 

[thisisrahul]

hey jbeaucairei tried using your user defined formula but its not working, it is giving me the same result even after applying the formula.
i have written "wel%come" and then apply the formula and it is giving me the same result "wel%come".

any idea why its not giving the correct result.


____________
Rahul

 

[jbeaucaire]

No, but I'm a "fiddler". I put in the code and your wel%come test and it failed for me, too, so I removed the flags and readded them until all the following characters worked.

?%/><}{:;@#$^!*

Change that code to:

Function CleanAll(txt As String) As String
With CreateObject("VBScript.RegExp")
'enter all characters to be stripped out into the pattern
    .Pattern = "[?%/><}{:;@#$^!*]"
    .Global = True
    CleanAll = .Replace(txt, "")
End With
End Function

This stuff is harmless, so feel free to twiddle those .Pattern entries, switch to your sheet and press F9 to see how it changed the result. This thing is wonderfully adaptable.

 

[jbeaucaire]

Well, fiddled some more and got some more info, it appears the ^ can turn the function into an INCLUSIVE list, excluding everything else, and Upper/Lower case can be ignored.

This version of the function will ONLY show letters of the alphabet and spaces. Also, the TRIM thing is now built into the function, so you won't need to do that anymore either.

Function CleanAll(ByVal txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "[^A-Z\s]+"
    .Global = True
    .IgnoreCase = True
    CleanAll = Application.Trim(.replace(txt, ""))
End With
End Function

 

[Tom Urtis]

Trim only removes LEADING and TRAILING spaces, if they exist at all. It won't touch the spaces within the string.

In cell A1 enter

" This is       a test ".

(without the quotes, notice all the spaces between "is" and "a")

In cell B1 enter the formula
=TRIM(A1)

You will end up in cell B1 with
This is a test

 

[jindon]

jbeaucaire

If you don't understand how to use Regular expression, Don't post the code as a SOLUTION.

.Pattern = "[?%/><}{:;@#$^!*]"

Will never work.

 

[jbeaucaire]

In cell A1 enter

" This is       a test ".

(without the quotes, notice all the spaces between "is" and "a")

In cell B1 enter the formula
=TRIM(A1)

You will end up in cell B1 with
This is a test

Thank you Tom. I've used the TRIM function for years and never discovered this interior facet of the function. I appreciate the clarification.

If you don't understand how to use Regular expression, Don't post the code as a SOLUTION.

.Pattern = "[?%/><}{:;@#$^!*]"

Will never work.

Two in a row. *sigh* I created that faulty pattern testing a few characters at time and found the ones that appeared to work in my mockup, it was a bad result based on quick tests.

The corrected version in post #7 with more knowledgeable syntax and function may have been, as your scolding emphasizes, too late. The OP did not respond to my corrected version.