Results 1 to 7 of 7

Thread: What else can I protect?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2012
    Location
    Warrenton, VA
    Posts
    463
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default What else can I protect?

    I am trying to lock down this workbook and all worksheets from users making accidental or intentional changes to ANYTHING except specific cells for date or text entry and also data entry through the userforms activated from form buttons and command buttons.

    I have locked off cut/copy/paste, right-clicking, I have designed the worksheets and workbook to automatically lock every time the workbook is closed.

    But there are still changes people can make. For example FORMATTING. They can still Clear Formats using the menu bar button, they can Fill Down, Right, Up, Left. Also, the Formulas tab is FULL of active buttons that can make undesirable changes. I don't want users to be able to make ANY CHANGES except to cells I have left unlocked and changes made through userforms from form/command buttons. Below is some of the code I use to lock off changes. Any help appreciated!

    Code:
    Private Sub Workbook_Activate()Application.CutCopyMode = False
    Application.OnKey "^c", ""
    Application.OnKey "^v", ""
    Application.CellDragAndDrop = False
    End Sub
    
    
    Private Sub Workbook_Deactivate()
    Application.CellDragAndDrop = True
    Application.OnKey "^c"
    Application.OnKey "^v"
    Application.CutCopyMode = False
    End Sub
    
    
    Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    Application.CutCopyMode = False
    Application.OnKey "^c", ""
    Application.OnKey "^v", ""
    Application.CellDragAndDrop = False
    End Sub
    
    
    Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    Application.CellDragAndDrop = True
    Application.OnKey "^c"
    Application.OnKey "^v"
    Application.CutCopyMode = False
    End Sub
    
    
    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    MsgBox "Right click menu deactivated." & vbCrLf & _
    "Cannot copy or ''drag & drop''.", 16, "For this workbook:"
    End Sub
    
    
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Application.CutCopyMode = False
    End Sub
    
    
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.OnKey "^c", ""
    Application.OnKey "^v", ""
    Application.CellDragAndDrop = False
    Application.CutCopyMode = False
    End Sub
    
    
    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Application.CutCopyMode = False
    End Sub

  2. #2
    Board Regular
    Join Date
    Dec 2012
    Location
    South Floriduhhh
    Posts
    230
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What else can I protect?

    how about start by using Excel's built-in tool for this.
    If after this it still doesn't close all the holes, you can write some code. however, I've found that the Protection Tool can do it.

    From the main menu Tools > Protection...
    choose from the submenu which allows protecting a worksheet, a workbook, a range of cells, etc.
    These are the rantings of a madman. Only a fool would listen.
    I roll Ol' Skool with Excel 2003
    Click here to see how to insert your code sample into a post without upsetting anyone, (especially Moderators)

  3. #3
    Board Regular
    Join Date
    Feb 2012
    Location
    Warrenton, VA
    Posts
    463
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What else can I protect?

    I of course protect the sheets and the workbook. I ensure this happens at every workbook close event. However, protection does not remove the capability of formatting via the buttons I referred to. The Clear and Fill buttons are active. Also almost all buttons on the Formulas tab. People can still insert a header or footer. Not to mention that the Format button is also active and can be used even when the worksheet is protected.

  4. #4
    Board Regular
    Join Date
    Dec 2012
    Location
    South Floriduhhh
    Posts
    230
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What else can I protect?

    If I understand you have buttons on your forms they shouldn't click on.
    Can you keep users from clicking on buttons with .Hide and .Enabled.
    myControl.Hide = True
    myControl.Hide = (ThisUser <> TheUserHatingAdmin)
    or
    myControl.Enabled = True
    myControl.Enabled = NOT myControl.Enabled

    For menus how about build your own menu bar (looking like the 'Real' default one) but without the items that are causing you grief Then enable yours and hide Excel's.
    Then you could hide Excel menu and enable your own.
    or whatever other menu is a problem.

    Application.CommandBars("Standard").Visible = True
    Application.CommandBars("Formatting").Visible = True
    blah blah

    Just some ideas to kick around.
    These are the rantings of a madman. Only a fool would listen.
    I roll Ol' Skool with Excel 2003
    Click here to see how to insert your code sample into a post without upsetting anyone, (especially Moderators)

  5. #5
    Board Regular
    Join Date
    Feb 2012
    Location
    Warrenton, VA
    Posts
    463
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What else can I protect?

    I guess I am explaining poorly. Users enter data through unlocked cells and also through userforms activated by form/command buttons. I am referring to the unlocked cells. Even though the worksheet is "protected" format changes can still be made to those unlocked cells! On the Home tab at the far right, the Fill and Clear options STILL FUNCTION. Also almost all options on the Formulas tab STILL FUNCTION. Insert Header/Footer STILL FUNCTIONS. Can I restrict these and other functions from user action?

  6. #6
    Board Regular
    Join Date
    Dec 2012
    Location
    South Floriduhhh
    Posts
    230
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What else can I protect?

    OK you have a newer version than I. I live a blissful Excel life in 2003. This seem to be a case where new and improved everyone was sold, really isn't. Say,Thanks Bill.
    So I'm out at this point with any detail solutions.

    What i have found while looking for your solution is collective. ".......you can't do that." to almost anything related to Tabs natively in VBA.

    I did find something called a Custom UI Editor that seems to allow you to make a custom tabs. But it doesn't keep you user away from the native ribbons.
    Here are some links to some information about it that I found.
    CustomUI Editor | The Office Corner
    CustomUI Editor Part II | The Office Corner
    CustomUI Editor | The Office Corner

    Load different RibbonX when opening file in Excel 2007 or 2010/2013

    Sorry no more help here, I'm out of gas........

    *Bruce now has one more reason to love 2003*
    These are the rantings of a madman. Only a fool would listen.
    I roll Ol' Skool with Excel 2003
    Click here to see how to insert your code sample into a post without upsetting anyone, (especially Moderators)

  7. #7
    Board Regular
    Join Date
    Feb 2012
    Location
    Warrenton, VA
    Posts
    463
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What else can I protect?

    Thank you, Bruce! Yes, I am thinking this is not possible....

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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