Prevent Excel cells from being deleted

    Prevent Excel cells from being deleted

    This is more an Excel related question: When users enter data in a excel/palo based planning sheet, it happens that cells ar being deleted (instead of entering 0). In this case the formula is being deleted and further changes will not be stored in the cube. Is the a way to protect this cells/formulas?

    RE: Prevent Excel cells from being deleted

    Hi,
    one solution is to let users enter values in non-formula cells and perform writeback in (hidden) PALO.SETDATA-formulas. One nice thing about this is that you got the possibility to create a kind of "Save" button that recalcs the sheet and triggers the SETDATA-formulas.

    Greetings from cologne
    Holger

    RE: Prevent Excel cells from being deleted

    Thanks for the fast reply. The disadvantage of this solution is (for me), that data ist not automatically actualized - so i would need a makro to do this and makros are unfortunately against our internal security orders.

    Writing directly on formulas ist the perfect solution - except the problem with deleted cells...

    RE: Prevent Excel cells from being deleted

    Unfortunately, I don't see how you can avoid this problem.

    You have to leave the cell unprotected otherwise users cannot input data in the cell. If you're saying that the auto recalc is off then linking a palo formula to the input area won't work either. Why is the auto recalc off?

    And without Macros, you can't enforce any input structure.

    Maybe you can have another workbook (with recalc on) that links to the user's input workbook. This gets messy though if the user can add or delete rows! Or maybe just add BOLD instructions telling the user that they must type zero in a cell and they cannot delete the formula? Not very good but it's all you have...

    Regards,
    Jason

    Disable delete key

    You can put the following code in the "ThisWorkBook" module in Excel.

    ***
    Private Sub Workbook_Activate()
    Application.OnKey "{Del}", ""
    End Sub

    Private Sub Workbook_Deactivate()
    Application.OnKey "{Del}"
    End Sub
    ***

    This will disable the delete key entirely for the workbook when it is activated but will reactivate the delete key if a different workbook is activated.

    Thanks to Jim Rech - Excel MVP for this code.

    RE: Disable delete key

    Hi Paolo,
    Im wondering why I didnt have that idea before because I already use that in a project... ###!*!

    Of course you can also do that with BACKSPACE and Instead of "" you can name a procedure what to else. Place that procedure in a module of your choice. Here is a example to send 0 instead of backspace.


    Sub SendZero()

    Application.SendKeys "0"

    End Sub

    In your workbook_open its

    Private Sub Workbook_Open()

    Application.OnKey "{BACKSPACE}", "sendzero"

    End Sub

    in workbook_beforeclose it should be


    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.OnKey "{BACKSPACE}"

    End Sub


    Greetings from cologne
    Holger
    Thanks a lot to all posters. Seems that we a creating some powerful planning sheets here :))

    A last Problem i was thinking of: If the User has macros deactivated, all these things won´t work. I´m using a planning sheet with all cells locked except the ones used to enter planning data. Is it possible to lock the sheet completely and open the specific fields with the activate() trigger, so data entry is only enabled when activating macros? This should make deleting cells 100% proof.
    Ok, I can answare to this question: give to the file read only proprierties.

    This means: user can use all palo feature, but if he tries to make a change in the file....simply he is not allowed.

    So: or he follows instruction or he (and he only) will have problems

    Paolo

    RE: Disable delete key

    Holger,

    Good idea to send a zero rather than disable completely. You could also add a line like
    Msgbox "Please don't use the delete or backspace key"
    to the SendZero procedure. A good way to annoy your users but it may start training them to do things the right way.

    I'd recommend using the workbook_activate and workbook_deactivate events rather than the workbook_open and workbook_close events. This way, the delete and/or backspace keys will still work normally in any other excel files that the user has open.

    Aaron
    Messagebox or Zero both seem to be a good solution.

    Does anybody know how to lock a worksheet completely and unlock it in the workbook_activate trigger to prevent by-passing the makros?

    @paolo: Thanks, but I dont think that setting the file read-only will really solve the problem.
    Hi,

    I wonder, if this principle will also help with copying figures directly into the cube using copy and past. Normally it would delete the formulas and write the copied figures direct into the excel cells. But with the right macro it might be possible to avoid this and write the figures into the cube.

    Any ideas how that could work?

    Thanks
    Dirk