Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Detecting line copy / insert (EXCEL 97/2000)

    I want to allow certain users to make changes to my sheet while others can only look. That works fine using a combination of sheetprotection and the "Worksheet _Change" event (the latter with an Application.Undo to reverse a non-allowed change).

    Now, I also want authorized users to enter data only (either by overtyping/editing existing cells or by inserting rows, but than via one of my VBA routines), no copying, moving and/or formatting should be allowed.

    Of course for this I also need to be in the Worksheet_Change event module <font color=blue>[call syntax: Private Sub Worksheet_Change(ByVal Target As Range)]</font color=blue>.

    How do I deal with this? Can I trap the type of event and allow/dis-allow accordingly?

    Appreciate any help/suggestions...

    Erik Jan

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting line copy / insert (EXCEL 97/2000)

    Unluckily, there is no event that fires when one inserts a row/deletes a row.

    But, if you say you don't want to allow formatting, just editing, why not simply use protection?

    It does disallow insertion and deleting of rows and columns, but you could deal with that by adding a macro that does that by:

    -unprotecting the sheet
    - inserting the rows/columns
    - protecting the sheet again
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting line copy / insert (EXCEL 97/2000)

    Yeah, I wanted that originally... however I have (many) cells which use the validation feature (displaying drop down lists for cell entries). It appears that (for reasons I do not understand) changes to these cells in a protected sheet are allowed !!!

    As that basically disabled my protection against un-authorized users, I had to revert -partly- to the change event.

    Still... maybe I could combine your suggestion with what I have... I think that will work...

    Thanks!

    Erik Jan

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting line copy / insert (EXCEL 97/2000)

    In my XL2K, if I use data, validation on an unlocked cell with the list option *and* protect the sheet, I can normally use the validation features (select from the drowpdown, enter a value, etc.) The validation seems to work as well. A bug in XL97?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting line copy / insert (EXCEL 97/2000)

    Actually it's the other way round: I need the sheet to be TOTALLY protected if I protect it. Even if I lock all cells, entry in cells with validation is still possible however...

    EJ

    BTW: for development I AM using EX2000 (most users use EX97 however)

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting line copy / insert (EXCEL 97/2000)

    Something strange happens here. If I set a locked cell to validation and protect the sheet, the validation cell is also locked. I use XL2K. Are you sure the sheet is properly protected?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting line copy / insert (EXCEL 97/2000)

    Created an empty sheet

    I use XL2000, I have all cells locked, I use validation for one cell to display a little drop-down list (two other cells on the same sheet).
    Now I protect the sheet (Tools, Protection, Protect Sheet)
    I leave all options on (Contents, Objects, Scenarios)
    I type a password (twice).

    All is now locked but STILL when I select the cell I can use the dropdown to select another value from the list. I can even use the Undo and Redo buttons which then become un-grayed.
    (I cannot enter one of the allowed entries in the cell through the keyboard however).

    Where did I go wrong..???

    Erik Jan

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting line copy / insert (EXCEL 97/2000)

    I confirm your experience.

    But!! if you manually type a list into the validation, it does work as I said: protection!

    So much for consistancy.... <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    I've learnt something new again today!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting line copy / insert (EXCEL 97/2000)

    Can't you just make your unauthorised users open read-only version?

    One other thought was using a "mirror" sheet for the unauthorised users and then Hiding/Revealing the relevant sheets depending on user status. I think that this might be a bit difficult to implement though <img src=/S/smile.gif border=0 alt=smile width=15 height=15> if you have users adding rows and columns <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>

Posting Permissions

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