Check ImputBox Characters

[Pulsus]

Hi,

First of all I must say that I am new in the forums and previously thanks any help I can get.

To the problem:

I have a workbook that works as a Model for other users, so I don´t want then to use this file, as I inserted the Code below on the Open WorkBook event, this code prevents the user to use the model (Unless he changes the file name), maybe its a "Dirt" code, but works fine for all.

Unfortunatelly, if the user insert a special character (/ \ |... and others) the code give a error and the original workbook get able to edit. The problem is that I am not able to insert a code that check if the user inserted any special character that are not accepted in file names. I tried (the code in red) but doesn´t worked, anyone can help me fix this code?

Thanks,

---

Please Login or Register  to view this content.

[jindon]

Does this help?

Please Login or Register  to view this content.

[Leith Ross]

Hello Pulsus,

You do realize that using StrPtr will never equal zero don't you? StrPtr is a special undocumented function in VBA that returns the address of the variable that points to the string and not the string itself.

Unfortunately when using the VBA InputBox you can not distinguish between no file name being entered or the user clicking Cancel as both return an empty string. Using the Application.InputBox you can. Here is how...

Please Login or Register  to view this content.

[Pulsus]

@jindon

Yes, Helped, Thank You, but then I have a problem to still checking the other criterias, I am working on the code.

@Leith Ross

Actually I didn´t no it (Until Now), thank you, and I liked the clean code (I tried to use The Select Case // End Select before but, as I am using the VBA ImputBox that didn´t worked well). But how I can use your code and still checking the invalid characters?

I know that a new Case Is = Argument is necessary, but wich is this one?

Thanks for the replies.

[jindon]

Use GetOpenFileName for selecting file, so that you don't need to check anything.
e.g

Please Login or Register  to view this content.

[Pulsus]

jindon

I Don´t know if this code is good for me, because I want the user has a model but, after he saved as a name he wants, he is free to use the file as many times he wants, as don´t need to creat a new one every time he opens.

Thanks

[jindon]

Sorry I don't understand what you mean.

The code I posted was to select the file to open, not to Save.

If you want to Save, use Application.GetSaveAsFileName method instead.

Anyway, using InputBox is not recommended to select File/Folder etc.
Too many work to check.

[Pulsus]

jindon,

Sorry, I will try to explain better:

I Have a file called MODEL

Every time the user open this file, he is asked to save it with a diferent name, so the file MODEL will be aways avaliable for him.

But, after he saved it with a diferent name (for example NEWFILE), this NEWFILE must be free to him open and use as many times he wants, so he is not asked to sav it as NEWFILE1 for example.

I hope i was clear now.

Thanks,

[jindon]

Not really sure though
Is it something like

Please Login or Register  to view this content.

[Pulsus]

jindon

Exactly,

Please Login or Register  to view this content.

[Leith Ross]

Hello Pulsus,

This will check the file name for illegal characters and alert the user. If user inputs a bad file name the message is displayed and the macro returns to get another filename.

Please Login or Register  to view this content.

[Pulsus]

Leith Ross,

Hello,

Outstanding Code, works almost perfectly but didin´t save the new file, so i made a little change that fits perfectly.

And I just make some improvements to treat some errors (Like the user try to save the file with a name he used before and press "CANCEL" button). CODE CHANGES IN RED.

I have to admit that i don´t know what the first two lines of the code do, but I promisse I will study this after.

Please Login or Register  to view this content.

Thanks,

[Leith Ross]

Hello Pulsus,

The 2 lines of mystery code check for illegal file characters. The script is Regular Expressions. Regular Expressions is a powerful pattern matching engine. Programmers in other languages like Java, ASP, and Perl use this also for handling web pages and web information. As you can see the syntax is rather obtuse. There is a lot of information out there but you must be sure it relates to VBA and not some other language as each language has its own syntax differences.

[Pulsus]

Leith Ross,

I was searching and find out this is a very powerful tool on programing, yes it´s a little bit complex but makes our life easier.

Here is a link with a nice explanation and tutorial to Regular Expressions (If someone that read this post gets interested): http://www.aivosto.com/regexpr.html

Thanks for the explanation, I will search more about it.

[Leith Ross]

Hello Pulsus,

Thanks for posting the link. If you have any questions, I will be happy to answer them for you.