Results 1 to 4 of 4

Thread: Prevent delete

  1. #1
    Join Date
    Dec 2004
    Posts
    78

    Question Unanswered: Prevent delete

    Is there a way to prevent users from deleting buttons or text boxes (infact any object) from an unprotected worksheet.

    Thanks.

    ~BS

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    My understanding is that a control, such as a text box, placed on an Excel worksheet can't be deleted unless you first go into design mode by clicking the design mode icon on the Control Toolbox. To disable this option, I placed the following VBA code in Module1 under Modules:
    Private Sub Auto_Open()

    Application.CommandBars("Control Toolbox").Enabled = False

    End Sub

    Sub Auto_Close()

    Application.CommandBars("Control Toolbox").Enabled = True

    End Sub

    It removes the Control Toolbox option from the menu when the workbook is opened, and restores the option when the workbook is closed.

    Someone in the forum may be able to add comments about whether this is a good solution, that it is safe to do this, and if there could be a case where Auto_Close is bypassed and the Control Toolbox might end up disabled for other Excel sessions.

    Jerry

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    If they are ActiveX controls (as Jerry explained above), then 95% of users wouldn't know how to delete them. They wouldn't realize that you had to activate the Control Toolbox toolbar, then press the Design Mode icon, then delete, then exit the Design mode by clicking on the Design icon again. Jerry's approach will work well for the ActiveX controls.

    However, if those boxes are from the Forms toolbar (or Drawing toolbar), then someone can easily delete them. If you have XL2002, then you have some options to prevent this. The easiest is to select all cells, then choose Format > Cells, and select Protection tab, and uncheck the "locked cells" box. Then you can protect the sheet (Tools Protection > Protect Sheet), and be sure to check all boxes (except Edit Objects); this will allow someone to maniuplate the cells, format, etc. but not able to do anything with the Textboxes or objects from the Forms toolbar.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Dec 2004
    Posts
    78

    Thumbs up

    Thanks guys. I think Jerry's code works fine for me. I was using the following and was not able to disable the control tool box (rather any toolbar) even if I was able to disable "Save As..." or "Copy" or "Cut" using the same code.

    Application.CommandBars("Worksheet Menu Bar").Controls("View").Controls("Toolbars").Contro ls("Control Toolbox").Enabled = False

    Thanks.

    ~BS

Posting Permissions

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