VB code protection/unprotection

[GrantLund]

Hi all:hi: ,

This problem may be very similar to that posted by Wolfgang below but not 100% certain if it is:

I protected my code using VBE->tools-> VBA properties->protection. That is fine BUT the problem is when I open up the workbook and click on commandbuttons and view the userforms that pop up and then close the workbook, after that I get the VBA password pop-up (after Excel closed down). Since I know the password it is a little bit of an irritation however I want to distribute and the only way other people can close this pop-up is to run End Task. What I was thinking would be best is if there is someway when someone opens the workbook to activate the VB code protection and then on closing to deactive the VB code protection to prevent the password pop-up coming up after the file has been closed. That way the workbook is protected immediately and no-one can access the code when the file is open and when file closes, that the irritating pop-up doesn't come on. Can this be done?:dunno

Any help would be appreciated - PS: I don't know anything about send keys but if it means I need to then so be it (having read the links to Wolfgang's request).

Grant

[johnske]

Sounds like you haven't closed something properly. Have a read through this thead http://www.vbaexpress.com/forum/showthread.php?t=5770

[ALe]

can u attach your file?

[GrantLund]

I've closed it down and re-opened it from scratch and it still does this. Funny thing it doesn't do this when you close the file but only when you close excel down.

Here is the code that is on "This workbook":
Option Explicit
Private Sub Workbook_Open()
Application.CommandBars("Control Toolbox").Protection = msoBarNoCustomize
Application.CommandBars("Control Toolbox").Controls("Design Mode").Enabled = False
ThisWorkbook.Protect ("X")
ActiveSheet.Protect ("X")
Range("A1").Select
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Control Toolbox").Controls("Design Mode").Enabled = True
ThisWorkbook.Saved = True
End Sub
(sorry if the code didnt come out properly - I dunno how to paste code as proper code like you guys).

Rgds
Grant

[GrantLund]

Johnske,

You link looks like the problem I have...just need to figure out what the gobblygook is saying - will give it a shot and see if I can solve the problem.

Grant

[ALe]

I don't get any problem.

3 ideas:
1. unload all the add-ins if you have (sometimes they create problems)
2. attach the complete file if possible (it's easier for us)
3. don't look at sendkeys (it generates troubles). it's better to focus on what creates the error

[johnske]

I've closed it down and re-opened it from scratch and it still does this. Funny thing it doesn't do this when you close the file but only when you close excel down.

Here is the code that is on "This workbook":
Option Explicit
Private Sub Workbook_Open()
Application.CommandBars("Control Toolbox").Protection = msoBarNoCustomize
Application.CommandBars("Control Toolbox").Controls("Design Mode").Enabled = False
ThisWorkbook.Protect ("X")
ActiveSheet.Protect ("X")
Range("A1").Select
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Control Toolbox").Controls("Design Mode").Enabled = True
ThisWorkbook.Saved = True
End Sub
(sorry if the code didnt come out properly - I dunno how to paste code as proper code like you guys).

Rgds
GrantThere's nothing there that would cause this, does anything in the rest of the code in your workbook open anything?

[GrantLund]

yes I have links to documents and other intranet items on some of the userforms (170 userforms) but they are all closed (unless something wasn't closed down properly along the way - however, I have nothing else open and still get the problem). Is there someway to make sure that everything is closed before the workbook or application closes to prevent the VBA password pop-up on excel closing?

PS: Have sent the file to Ale (1MB).

Grant

[johnske]

It sounds like something's not being released properly. Do you Hide or Unload all these userforms? Do you create any new instance of an object using the 'New' keyword? (If so the object needs to be Set = Nothing)

[ALe]

No file received

[GrantLund]

Yes, I unload and show userforms. I also protect unprotect sheets as well as hide unhide sheets. How do I know which one to set to nothing? Do I have to set to nothing after each and every instance?

PS: When I take off the VBA protection then I obviously dont get this problem. As soon as I set a password I get this problem.

Grant

[GrantLund]

All,

Still no luck with the problem - is there any other way to protect code other than via tools -> VBA propeties -> protection? ie. is there any code that can protect the code with a password?

I've tried to go back to the very beginning and still get this problem when I set the protection - after going into one or two userforms and then closing excel.:banghead:

Rgds,
Grant

[ALe]

So far I couldn't find a solution. It must be related to references somehow...