What else can I protect?

[Sven62]

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!

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

 

[brucef2112]

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.

 

[Sven62]

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.

 

[brucef2112]

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.

 

[Sven62]

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?

 

[brucef2112]

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*

 

[Sven62]

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