Macro password protection via vba code 1

[JOB2828]

~Excel 2003~

Is it possible to write a subroutine that will "lock project for viewing" and unlock?

Here's why I ask:

Due to the error defined in the knowledge base article Q199268, I can not "lock project for viewing" and write a macro that performs a saveas in a tab seperated format. I was going to work around this by adding code to simply unlock the project prior to saving the file and relock it after it finished.

Any ideas are appreciated!

It really ticks me off that Q199268 is from November 5th 2003! How can they not have this issue fixed by now?

 

[KenPuls]

Hi there,

Natively? No. The Protection property of the VBProject is Read Only.

The only way I can think of doing this would be to use SendKeys to pull it off. SendKeys has long been viewed as an unreliable method for anything though...

Ken Puls, CMA

http://www.excelguru.ca

 

[firefytr]

Well if you could unlock it via code, then what would even be the purpose of having a password at all?!!?!! Sounds kind of ridiculous if you think about it. ;-)

(Heya Ken!)

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[KenPuls]

Heya Zack!

I didn't read it as a "hack" so much as an attempt to provide the password via code to unlock, much the same as we'd do with a worksheet.

Regardless, natively this is not supported. I'm sure I've seen it done via SendKeys before somewhere, but again, it's not a route that I would choose unless there were no other options.

Ken Puls, CMA

http://www.excelguru.ca

 

[TonyJollans]

The way round this is to dump the VBA project that doesn't get saved anyway. Copy the sheet you want saved as text into a new workbook and save that instead, something along these lines ...

[blue]ActiveSheet.Copy
ActiveWorkbook.SaveAs "C:\Path\And\Name", xlText
ActiveWorkbook.Close xlDoNotSaveChanges[/blue]

Hi Ken .. good to see you here

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[firefytr]

I know the words "hack" were not mentioned, but that is exactly what it is. Getting around ways to get information. Not all hacking is bad or malicious, I know this. Have you ever had to strip a VBA password before (legally)? ;-)

I too remember some in-depth threads at VBAX and MrExcel about this very issue.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[TonyJollans]

I have to side with Ken on this. The questioner is just asking if he can supply, in code, a password that he knows (or can legally obtain), in order to workaround an Excel bug - not asking for a way to bypass any security. As already posted there is no need to do it to achieve his goal.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[firefytr]

I know that's not what the OP's literally asking for, but it is in essence. Now I don't have a problem with it, I can see many, many occasions where that would be useful. But, it is still contradicting the entire purpose of a VBA project password.

That's pretty much my opinion, and I don't want to beat a dead horse. Have a good one fellas.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[JOB2828]

Thank you all for your posts...

I tried the recommendation of TonyJollans that does the copy prior to doing the saveas with the same result. You are correct Tony, I am simply trying to find a work around for the Q199268 bug. I need to be able to lock the code and be able to save to a text file. I'm just flustered that something so simple even needs a work around in the first place.

Once again any ideas are greatly appreciated!

 

[JOB2828]

Sorry Tony,

I think I misinterpreted what you recommended earlier. I must have accidentally diregarded the first line saying to "dump" the VBA code. I guess I'm not clear on how you mean for me to perform the saveas when the poject is locked...

 

[xlbo]

you savea different workbook effectively

Just copy the data out of the original workbook and save it seperately as a tab delimited file...pretty much exactly as the example he has given you....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Thanks Geoff - I thought it was fairly straightforward.

Sorry JOB2828, 'dump' is not a very good technical term - and slightly inaccurate anyway.

Saving As Text saves a single sheet without any VBA Project. Copying the sheet to a new workbook as an intermediate step copies only the sheet and not the locked VBA project so that you don't then have a problem saving that new workbook.

One point: you may have to specify False (or SaveChanges:=False) rather than xlDoNotSaveChanges.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[firefytr]

Here is some more info on setting a password:

http://www.standards.com/Office/SetVBAProjectPassword.html

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[JOB2828]

Sorry for the confusion.. Your suggestion worked just fine. I just didn't understand that you meant the vba code is what the saveas was having problems with. I figured that the method would have automatically removed the vba code when saving as text. Once again, it makes me frustrated that this is not fixed. Seems easy enough to make the method do this for you (remove the code that is before saving to text). Anyway here's a code snippet of what it looked like.

Private Sub GenerateText()

    Dim fs
    Set fs = CreateObject("scripting.filesystemobject")
    
    Application.DisplayAlerts = False
    Me.ActiveSheet.Copy
    Me.ActiveSheet.SaveAs DestPath & fs.getbasename(Me.Name), xlTextWindows
    ActiveWorkbook.Close
    Me.Saved = True
    Application.DisplayAlerts = True

End Sub