Remove toolbars....flaw I can't work round

[geff]

Hi guys,

I have a slight problem with some code I am using. I have a user form where I force the users to enable Macros using the hidden sheets trick. Once the user enables the macros all of the toolbars dissapear from excel. The code I use to do this is below:

Please Login or Register  to view this content.

I also have a "restore toolbars" code below which is basically the opposite of this so that when the user exits or saves the form using my macro button the toolbars are restored.

Please Login or Register  to view this content.

I have the following in my workbook code so you can see when the above functions are called:

Please Login or Register  to view this content.

Hopefully the above will be enough information for you to help me. The flaw I cannot get around is this:

If I have 2 copies of the form and open them both with macros enabled, when I exit one of them all the toolbars are restored for the form that is still open. I really need to find a work around for this, it is essential that the users are not able to navigate around the worksheets or print and save without using the macro buttons embedded within the form as there is additional validation functionality written into those buttons.

Any help would be greatly appreciated.

Warm Regards

Geff

[Charlize]

Create two public boolean's (form1open and form2open). You set them to false. When you open form1, you set form1open to true. When closing any of your two forms, you check upon those two boolean's. When one of them is true, don't perform the action to restore the bars. For the form that is closed, the boolean is set to false.

Charlize

[geff]

Wow! Thanks for such a quick response.

I have to be honest though...I have no idea how to actually do that. Allthough I have some very basic knowledge of VBA the majority of my code in the this form has either been worked out through trial and error by adjusting recorded macros or via code found on the internet or code kindly donated by people on this forum.

Any chance you could give me some more detail...i.e the where, the how and the what. I really appreciate your time. Thank you.

[Charlize]

I hope this is somewhat better explained.

Please Login or Register  to view this content.

[geff]

Once again thanks for such a quick and detailed response. I have understood your instructions (really helpful for me to get a better understanding as well). However, I have added the code as instructed but when I open the workbook and enable the Macros I get an error saying "Compile Error in hidden Module: ThisWorkbook" It doesn't give me the option to debug just OK or Help.

My ThisWorkbook code has changed to this:

Please Login or Register  to view this content.

Any ideas? Once again thanks so much for your help.

[Charlize]

Maybe you just have to remove the

Please Login or Register  to view this content.

declaration.

I use it to be sure that I declare everything that I want to use. Advantage of this is that I have to think about what I need to declare (long, boolean, string ...).

Maybe you have to change the logic a bit. Because you restore the toolbars on the workbook_deactivate event to show them in another workbook, you cannot check on those booleans inside the restore_toolbar coding. Instead check on the booleans before the call to the restore_toolbar macro.

So when you close your form1, you set boolean for form1 to false, check with that if clause (in the before_close event of your forms) if both booleans are false, if so then perform the restore_toolbar macro.

Please Login or Register  to view this content.

And when you activate your workbook again, you'll have to check on true and call the remove_toolbar macro.

Charlize

[geff]

Unfortunately that still throws up the Compile Error. It seems to be this module causing problems:

Please Login or Register  to view this content.

I

[geff]

Unfortunately that still throws up the Compile Error. It seems to be this module causing problems:

Please Login or Register  to view this content.

I am getting confused I must admit. Thankyou for your time and effort so far I hope we can get to the bottom of this.

[Charlize]

Unfortunately that still throws up the Compile Error. It seems to be this module causing problems:

Please Login or Register  to view this content.

I am getting confused I must admit. Thankyou for your time and effort so far I hope we can get to the bottom of this.

Sorry, instead of

Please Login or Register  to view this content.

try

Please Login or Register  to view this content.

[geff]

Sorry, I'm still getting compile error as soon as I enable the macros. My ThisWorkbook code is as follows:

Please Login or Register  to view this content.

My Restore Toolbars macro is now:

Please Login or Register  to view this content.

Its still not showing me an option to debug so I can't see where the code is failing.

If it helps the error message pops up when enabling macros, I click ok and it pops up again after click ok the 2nd time it dissapears but I am left on the "macrotest warning sheet". When I click the close application (X) button the same message pops up again twice.

I really appreciate your help so far thank you.

[Charlize]

It's because you've got two workbook_open events. You can only have one.

Charlize

[geff]

Ive changed my workbook_open code to this now:

Please Login or Register  to view this content.

I still get the compile error. I've also tried this but the same thing happens:

Please Login or Register  to view this content.

Sorry to be a pain. I really appreciate your help.

[Charlize]

Post your workbook or send me a pm (only this time) with your workbook attached.

Charlize

[geff]

Thanks Charlize. I will send you the original workbook without your additional code, because currently the Compile error is causing excel toolbars to be removed permanantly and I keep having to run a seperate macro to restore them all, I certainly wouldn't want to be responsible for you not being able to get your toolbars back.

Regards Geff

[geff]

I have thought of a suitable workaround to my problem although I'm not sure how to actually do it.

If when the workbook opens VBA exports a .txt file for example "C:/TEMP/check.txt". In the same workbook open code there should be a check for this file in existance, and if it already exists then there should be a message box saying "only one instance of this workbook can be opened at a time", if the .txt file does not exist then it should create it.

Then on the exit workbook macro it should simply delete that file.

Is the above possible...if so any suggestions on how to do it would be great.

[Charlize]

And you must create that directory if it's not present. So better check if C:\Temp exists before writing to that directory.

Please Login or Register  to view this content.

[geff]

OK we are definately getting somewhere!! There is still a drawback though....I really appreciate your time on this, thankyou so much.

Anyway the drawback is this:

As soon as the duplicate workbook is opened the message box comes up saying that two cannot be open at a time and it closes the workbook. But during that process the "Workbook_Deactivate" code on the other workbook is run and so the problem still exists all the toolbars are restored on the remaining workbook!....this is beginning to drive me potty. I can't understand why the "Workbook_Activate" code is not kicking in and removing the toolbars again.

Here is my code slightly adapted as you can see, this is called in the Workbook_Open code.

Please Login or Register  to view this content.

I thought a workaround...long winded though it is would be to get the above code to create another text file after

Please Login or Register  to view this content.

Then I could add an IF function into the "Workbook_Deactivate" to only run the "Restore Toolbars" code if that file did NOT exist.

What do you think?

Once again thanks so much for your help.

[Charlize]

Variation. Check on the name of the workbook.

Please Login or Register  to view this content.

[geff]

Is that dependant on the filename because the user can save and rename the file within explorer as they choose so theres no way of knowing what the file could be called.....unless of course there is a way of defining a name for the workbook that is seperate to the filename? Or perhaps defining a name for the workbook in the "workbook_open".

Trouble is, is that thinking about it, regardless of how I ensure only one version is opened as soon as I try opening the duplicate version its going to deactivate the open workbook...thus restoring the toolbars.

[geff]

Just encountered another drawback to the create text file idea. Obviously the code to kill the text file is in the Workbook_BeforeClose code in case of the user exiting the application via the red cross rather than my exit macro button. This means that when you get the message that it cannot open a duplicate workbook it kills that .txt file which then flags an error that the file doesn't exist when the original workbook is closed. If you get what I mean?

I think I'm about to give up with this unless you have any sudden flashes of inspiration.