Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Message - Protected Sheet (2000)

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Message - Protected Sheet (2000)

    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

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Message - Protected Sheet (2000)

    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. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Alan

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Message - Protected Sheet (2000)

    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

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Message - Protected Sheet (2000)

    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. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Alan

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Message - Protected Sheet (2000)

    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

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Message - Protected Sheet (2000)

    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.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Message - Protected Sheet (2000)

    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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Message - Protected Sheet (2000)

    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"

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Message - Protected Sheet (2000)

    I thought about some kind of "less than subtle" <img src=/S/grin.gif border=0 alt=grin width=15 height=15> 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

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Message - Protected Sheet (2000)

    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. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    cheers

    Alan

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Message - Protected Sheet (2000)

    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!

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Message - Protected Sheet (2000)

    Thanks Mike. I'll look and hopefully learn.

    Alan

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Message - Protected Sheet (2000)

    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

    <pre>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</pre>


  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Message - Protected Sheet (2000)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •