I have a script that loops through the files in a choosen folder and is supposed to protect or unprotect the workbook depending on what the user types into a Application.InputBox.
The script will cycle through the files but does not either protect or unprotect them as expected. And from all my research, the code seems to be correct.
I am looking for a little guidance with the code below.
I have sectioned off the part I need help with.
Please Login or Register to view this content.
A bit different than you thought:' 'I NEED HELP WITH THIS BIT.
' ' Want to test if the user entered a correct value before proceedingPlease Login or Register to view this content.You found the wrong protection. You want to password protect the file on opening, but you used workbook structure protection. Keep looking.' I NEED HELP WITH THIS PART
' THE PROTECT AND UNPROTECT DO NOT SEEM TO BE DOING WHAT THEY ARE SUPPOSED TO
' I DO NOT GET AN ERROR
Artik
First off thank you for taking the time to review and answer my questions, this is very helpful.
I am however, still unable to protect workbooks??
Please Login or Register to view this content.
I have tried each of these and then both together. I'm sure I am close, but almost all websites are telling me the same thing, and that is what I am trying.
Any guidance?
I ended up getting the code to work.
See the code below:
Please Login or Register to view this content.
Your code after the fix is pretty good, although you trusted me too much.
For example. This condition will never be met:I wrote it wrong.Please Login or Register to view this content.
This snippet won't necessarily work either:You mistakenly assumed that the user would type words in capital letters. And before that, you didn't convert user responses to uppercase.Please Login or Register to view this content.
This code construction is very dangerous:The explanation is going to be a little tricky.Please Login or Register to view this content.
Before explaining the problem with the cited code, you need to know what happens when you use SaveAs. This command saves the workbook under a new name, the "source" workbook is abandoned (closed without saving changes), and the active workbook is already a new instance of the "source" workbook.
With ActiveWorkbook refers to the active workbook. And everything inside that construction refers to what comes after With, which is the "source" workbook.
.SaveAs still refers to the correct workbook, but .Close does not. You are trying to close a workbook that is no longer there because it was abandoned when you saved it. But do not worry. You are not alone.Even though I know about this trap, I made this mistake a moment ago.
Sometimes a folder may contain temporary files that are created when you open a particular workbook. After the file is properly closed, the temporary file is deleted by Excel. However, when a file is closed abruptly (due to a fatal error), the temporary file may remain on disk. They start with a tilde (~), but the file extension is the same as the actual file. Temporary files have a very different structure and you may get an error when you open them in Excel. When looping through files, exclude them.
It's mine now.
I don't trust the Dir() function, so I use File System Object. This makes the code longer. Besides, I like "rich" code. In addition, I pay more attention to the safe execution of the code. You can apply it, or you can stay with yours, provided that you correct the mistakes I mentioned.
ArtikPlease Login or Register to view this content.
Last edited by Artik; 07-08-2021 at 09:59 PM.
@Artik This is amazing!
Its funny you should say that about your bit of code,I did have to work on it after I posted my "Solution". See below:Please Login or Register to view this content.
Thank you very much for taking the time and effort to work this all out and for the concise explanation! Very much appreciated.
I will take this all in. I find after I figure a bit of code out (or get the answer in this case) I can take the time to do a deeper learning dive.
Thanks again,
Joe
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks