Custom Message – Protected Sheet (2000)

Topic

New Reply

I have a workbook, which I’ve protected at the “user interface only” level from within VBA. When the user attempts to make a change, they get the usual message box. What I’d like to do is replace this with a custom message, perhaps even a userform with a help button, to direct them to the custom commandbar, from which they can make their intended changes. It would be nice if I could also capture what they intended doing (insert, delete, modify) so that they could even be directed to the appropriate button on the custom commandbar.

Is this kind of event hooking at all possible? Ideas on how to achieve this behavior appreciated.

Alan

Reply | Quote

Replies

I do NOT think (though maybe others are more confident on its feasibility) that you can “trap” this event (ie the excel popup that that worksheet is protecte). It occurs BEFORE a worksheet change event can occur (actually PREVENTING a change) so that is no good.

Some thoughts:
I considered eliminating the “protection” and then you could have your “own code” to monitor the changes and act accordingly, BUT:
Your code will be MUCH slower than excel’s
Much of the things users try to do (insert delete, formatting changes, do NOT trigger EVENTS) so they are more difficult to trap

So if you want to have protection, but STILL allow the users to make (some) changes, the best way might be to add a new toolbar that has your OWN custom buttons and codes to do whatever you will allow them to do (add, delete, edit, insert, delete, etc).

If you want to get “fancy” (though I think with user-toolbar customizations, this could be a pain) you could decide what changes you will allow and then CHANGE the excel buttons when this sheet is active, to replace those with YOUR custom buttons, and when the sheet is deactivated, but the originals back in.

[the best way to do this is probably to create you OWN toolbars and menubar that JUST have the items you want the users to be able to do (setting them up similarly to the “default” command bars) and then REMOVE ANY that the user has displayed. This is easier than searching and replacing buttons and items one by one and searching all the toolbars! Delete the custom menubar and toolbars when the sheet is inactive and restore the originals]

This scheme would allow certain changes whihc you would control via your own code, so you can validate before you make the changes. It actually should ELIMINATE the need to “trap” the “protected worksheet dialog” since they will not be able to do anything that could change it.

Steve

Reply | Quote

Hi Steve

As always, your advice makes good sense. I figured it might be hard to “trap events” in this scenario – it seems like well defined events don’t even exist. There is also the problem of keypresses like Del, which I’d like to exclude.

I should have been clearer on the intention here. I already have a custom commandbar, through which all changes to the data should be made. This enables validation, comboboxes etc. for each data attribute (cell) of each item (row) of data via a single userform. There are buttons & userforms to insert, modify and delete items. However, the “natural” instinct of the users will probably be to go directly to the rows/ cells. They are then met with the standard message box, which I was hoping to replace with the custom one, directing them to the custom toolbar, or even “pressing” the appropriate commandbar button for them.

Maybe having to keep dismissing the standard messagebox will become the best method of directing them to the custom commandbar as a first instinct.

Alan

Reply | Quote

If they go to the cells and use the “right-click” menu, you could create your own right-click menu to be done and then restore it afterwards just like adding your own other command bars.

Steve

Reply | Quote

Yes, that’s another thought thanks Steve. Actually, the default right-click menu for a protected sheet seems to have the appropriate (or inappropriate) items greyed out, so the user is “alerted” to the fact that they’re trying to do things the “wrong” way. This obviates the need to deal with the appearance of the default message box in the first place. But maybe a custom context menu might be a good idea anyway, for users who are used to working that way. Too many $%^&*!@ variables in peoples’ brains me thinks.

Alan

Reply | Quote

It is one of the strengths and the “problems” with excel. As a user you can do things multiple ways and customize nealry everything, but if you are creating an application it is nearly imposssible to prevent everything.

That is why (I think) the easiest is to HIDE ALL the command bars and replace them witha custom menubar and any additional toolbars you need with copies of the built-in tools you want them to have or your created “duplicates” which have “validation or other checking”.

Steve

Reply | Quote

This approach does seem to be the best to me, but may be overkill for a (important) reason I forgot to mention. The sheets that the user sees contain only references to the (genuinely) hidden data sheets. They are derived/ generated each time the workbook is opened, so any changes the user makes at the visible sheet level will NOT affect the underlying data, even though it may appear to have done so during their “session”. The only way the real data can be altered is through the interface I provide. Users may well get very annoyed that their changes weren’t “saved” if they don’t make them through the custom interface, but the real data is protected from corruption this way.

The purpose of the visible sheets is largely for navigation and summary information. When a user selects a cell or row, they can add, delete or modify via the custom interface, and this is the only way that changes can be made to the data in the hidden master. So it’s really a matter of trying to steer users to using the custom controls, and away from the more familiar ways of doing things. Hiding or modifying many of the “usual” controls, as you suggest, could only help the cause I’d guess.

cheers

Alan

BTW, re: the floating/ sinking egg problem from the PUZZLES board:
You may not have seen that I dug out the old book on this and it is, indeed, as you suggested – a combination of CO2 generation from the dissolving shell, and those bubbles “floating” the egg like the raisins you mentioned.

Reply | Quote

Yes, I saw that you had found the book and how the “trick” worked on the puzzles page. But thank you for thinking about me.

Steve

Reply | Quote

You might just add a “text box” note on this sheet explaining that they must edit through the command bars to get anything to “stick”

Reply | Quote

I thought about some kind of “less than subtle” reminder, but the sheets are jam-packed with data which really shouldn’t be obscured. I considered using a popup with a “Don’t remind me again” checkbox in some capacity too. I may test the water for feedback on a few of these persuasions.

cheers

Alan

Reply | Quote

Hi Steve

Now that I’m getting deeper into this (first time I’ve tried this kind of thing), the significance of hiding the “standard” commandbars, modifying standard menu items, and particularly the value of a custom right-click context menu are starting to dawn. One thing that I’d particularly like to hide is the Formula Bar, since this is often a first port of call for a user to try to edit a cell. Is this possible, just for the active workbook? I guess I’d lose the Name Box too if I did that, although that’s no big deal. I’m hunting down all these issues at the moment, but if you know off hand of any links or threads in the Lounge, they’d certainly be appreciated too.

cheers

Alan

Reply | Quote

Hi Alan,

Saw your post about right click context menus and thought an example might help. Attached is a workbook I made for employee schedules at work a while back…I have modified it a bunch since then, but the best revised file is at work. Anyhow, the attached does not change the standard command bars, but does illustrate the creation and use of a right click context bar. I think the size of the right click bar is self adjusting (the one at work is, I do not remember if this one is or not!).

Right click in the attached workbook to schedule a shift.

Had to zip it to fit…didn’t mean to step on your thread Steve!

Reply | Quote

Thanks Mike. I’ll look and hopefully learn.

Alan

Reply | Quote

No need to worry. It is NOT my thread. I think the more input/answers one gets the better. Helps the poster get more perspective and helps others (like me) to see other techniques

Steve

Reply | Quote

No need to worry. It is NOT my thread. I think the more input/answers one gets the better. Helps the poster get more perspective and helps others (like me) to see other techniques

Steve

Reply | Quote

Hi Michael

Finally got round to looking through the nitty gritty of your workbook. I could understand how you built the context menu and will use the general method for my own project. The only thing that bothered me was that the cell context menu became disabled for all open workbooks, other than this custom one. I’m guessing that this can be rectified by using the activate/ deactivate events of the ThisWorkbook module, rather than the open/ close events. I’ve also never used Application.EnableCancelKey = False – I guess it’s there to stop users stopping macros half way through.

Alan

Reply | Quote

Use the window activate and deactivate events if you are moving between workbooks to enable the standard right click and disable the custom right click in one, and vice versa.

Reply | Quote

Use the window activate and deactivate events if you are moving between workbooks to enable the standard right click and disable the custom right click in one, and vice versa.

Reply | Quote

Hi Michael

Finally got round to looking through the nitty gritty of your workbook. I could understand how you built the context menu and will use the general method for my own project. The only thing that bothered me was that the cell context menu became disabled for all open workbooks, other than this custom one. I’m guessing that this can be rectified by using the activate/ deactivate events of the ThisWorkbook module, rather than the open/ close events. I’ve also never used Application.EnableCancelKey = False – I guess it’s there to stop users stopping macros half way through.

Alan

Reply | Quote

These will hide/unhide the formulabar when the particular workbook is active/inactive. Though if you have an entire “scheme” to hide/unhide, you could just have them call a sub to setup and one to restore to “normal”
Steve

Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
 Application.DisplayFormulaBar = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
 Application.DisplayFormulaBar = True
End Sub

Reply | Quote

Thanks Steve. I discovered these methods in my dabbling, but maybe you could explain the difference between
Workbook_Activate() (Occurs when any workbook is activated) and
Workbook_WindowActivate() (Occurs when any workbook window is activated) events, or more specifically, how/ when to use each of them.

thanks

Alan

Reply | Quote

Thanks Steve. I discovered these methods in my dabbling, but maybe you could explain the difference between
Workbook_Activate() (Occurs when any workbook is activated) and
Workbook_WindowActivate() (Occurs when any workbook window is activated) events, or more specifically, how/ when to use each of them.

thanks

Alan

Reply | Quote

With Window | New Window, you can open more than one window on the same workbook. when you use the Window menu to switch between windows on the same workbook, the Workbook_Activate event obviously doesn’t occur, since the workbook was already active, but the Workbook_WindowActivate event does occur.

Reply | Quote

Ah, that explains it thanks Hans. As usual it sparks another query. If different edits are made to several open copies of the one workbook (in different windows) are they kept in (viewing) sync? i.e. will the active one always show the cumulative effect of all of the changes made across the other windows?

Alan

Reply | Quote

The windows are just views; there is only one underlying workbook, not several copies. Edits in one window are reflected in all other windows on the same workbook immediately, as you can verify by opening a new window, and viewing the two windows side by side.

Reply | Quote

[indent]

---

Edits in one window are reflected in all other windows on the same workbook immediately

---

[/indent]
Thanks Hans. I suspected this was the case, but wasn’t sure.

Alan

Reply | Quote

[indent]

---

Edits in one window are reflected in all other windows on the same workbook immediately

---

[/indent]
Thanks Hans. I suspected this was the case, but wasn’t sure.

Alan

Reply | Quote

The windows are just views; there is only one underlying workbook, not several copies. Edits in one window are reflected in all other windows on the same workbook immediately, as you can verify by opening a new window, and viewing the two windows side by side.

Reply | Quote

Ah, that explains it thanks Hans. As usual it sparks another query. If different edits are made to several open copies of the one workbook (in different windows) are they kept in (viewing) sync? i.e. will the active one always show the cumulative effect of all of the changes made across the other windows?

Alan

Reply | Quote

With Window | New Window, you can open more than one window on the same workbook. when you use the Window menu to switch between windows on the same workbook, the Workbook_Activate event obviously doesn’t occur, since the workbook was already active, but the Workbook_WindowActivate event does occur.

Reply | Quote

These will hide/unhide the formulabar when the particular workbook is active/inactive. Though if you have an entire “scheme” to hide/unhide, you could just have them call a sub to setup and one to restore to “normal”
Steve

Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
 Application.DisplayFormulaBar = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
 Application.DisplayFormulaBar = True
End Sub

Reply | Quote