Trying to 'Grey Out' menu items on Custom Menu if Workbook is ReadOnly

Hi,

I've been playing with some code to try and prevent a user from carrying out certain tasks from a custom menu when the work book is opened read only - basically to prevent them wasting time working on things then realising they can't save it as it's read only. The menu is created only when a specific sheet is active, and deleted when not.

However I can't quite get it to work, any ideas what I'm doing wrong? Sure it's something silly...

Please Login or Register  to view this content.

Any help appreciated.

Thanks, TC

Should probably add that I don't get an error message or anything but that the menu item is not greyed out and still triggers the "NewCase" code...

Anyone got any thoughts on this?

I know it's Friday, but just on the off chance - any ideas what I'm doing wrong here???

Wow, I'm impressed - no shirking on a Friday

JosephP, I believe the Active Workbook is read only. It's the only one open and the Window title is showing as the file name followed by [Read Only]. The code to add the menu (as above) is triggered by the WorkSheet Activate event in the relevant sheet and run from Module 1. This is so that the Menu is only added for the user to access when they have the right workbook / worksheet selected. It's deleted on the deactivate event.

XeRo Solus - I'm not sure what you mean by enable editing? I have selected Enable Macros and the sheet can be edited - but cannot be saved - is this not true read only? I would like certain users to have the modify password and be able to edit and save the workbook, while other users would not have the password and open as read only just to view the information.
Again not sure about modeless - the Show Modal property is set to true but I don't see how the user would access the form any other way that through the menu - which is what I'm trying to prevent them from doing.

I'm trying to avoid a situation where a user would go in as read only - get distracted - forget they went in as read only - carry out a load of work - then go to save it only to find that they cannot. Would make me pretty angry...

Does this make any sense at all?

Okay, this is really weird.

I opened the workbook and stuck a pause break in the code listed above. Then to trigger the code selected another sheet and when back to the 'Tracker' sheet. This triggered the code as expected and hey presto the menu item is greyed out. Why was it not greyed out when the menu was first created??? Would it not check it was read only then?

Anyway so I came out of the work book and opened it as read write, but the menu item is still greyed out. If i flick between sheets it rund through the code and then it's no longer greyed out.

To summarise, it looks likt the first time the AddMenu code it run it's not triggering that bit of code - or doesn;t know if it's read only or not at that point? Really can't work out the logic...

Does this make any sense to you or is it best just to put a work around in so that it opens on another sheet then activates the tracker sheet. Seems a bit cluncky....

Apologies for the delay in responding - been manic at work recently.

I've done as you suggested Josie and called the sub to add the menu from the workbook open event and delete from the workbook close event - in addition to the activate and deactivate code. Everything appears to run fine now.

Still makes no sense to me that on opening the menu was being added but for some reason wasn't checking if if read only / read write and somehow just carried over the status from the last time it was open. One of those VBA peculiarities I guess? I would submit a trimmed down version of the workbook - but I can't upload anything from work so i'll have to write something from scratch at home - when I have some spare time perhaps. For now though this has fixed my problem.

Thanks Josie & Solus.

Just went back and checked on an older version and you are right! Feel like such a noob!
I presumed that activate and deactivate wolud happen as part of opening and closing the workbook.

One last question then, is it better to 're-build' the menu on the activate event and then delete it on deactivate each time - or now that i have it as part of open and close, would it be better to 'hide / disable' it when the worksheet is not active?

Sorry and thanks again.

Okay, that's great. Thanks.