Protecting Spreadsheet Layout

[dapoole]

Hi Excel Demigods

I have a spreadsheet that has a certain layout (i.e. borders, colours, etc) which I would like to be protected against all users changing. Although they need to be able to update the cells I do not want them altering the layout. One example is that when a user cuts and pastes one cell into another the borders and colours seem to be altered as well as the cells data. How can I prevent this? I am sure it must be possible, however I need to consult the gods.

Cheers
David

 

[MichaelRo]

You could disable Cut & Copy

Check out Ivan F Moala's site:

http://www.xcelfiles.com/VBA_Quick13.html

 

[dapoole]

Thanks but how do I apply the code to my spreadsheet?

 

[MichaelRo]

Right Click on the Worksheet Tab

Select View Code from the Menu List

Paste the Following into the VB Window:

Option Explicit

Private Sub Worksheet_Activate()
    Call DisableCopyCutAndPaste
End Sub

Private Sub Worksheet_Deactivate()
    Call EnableCopyCutAndPaste
End Sub


Sub DisableCopyCutAndPaste()
    EnableControl 21, False   ' cut
    EnableControl 19, False   ' copy
    EnableControl 22, False   ' paste
    EnableControl 755, False  ' pastespecial
    Application.OnKey "^c", "Dummy"
    Application.OnKey "^v", "Dummy"
    Application.OnKey "+{DEL}", "Dummy"
    Application.OnKey "+{INSERT}", "Dummy"
    Application.CellDragAndDrop = False
    Application.OnDoubleClick = "Dummy"
    CommandBars("ToolBar List").Enabled = False
End Sub

Sub EnableCopyCutAndPaste()
    EnableControl 21, True   ' cut
    EnableControl 19, True   ' copy
    EnableControl 22, True   ' paste
    EnableControl 755, True  ' pastespecial
    Application.OnKey "^c"
    Application.OnKey "^v"
    Application.OnKey "+{DEL}"
    Application.OnKey "+{INSERT}"
    Application.CellDragAndDrop = True
    Application.OnDoubleClick = ""
    CommandBars("ToolBar List").Enabled = True
End Sub

Sub EnableControl(Id As Integer, Enabled As Boolean)
Dim CB As CommandBar
Dim C As CommandBarControl
  
On Error Resume Next
For Each CB In Application.CommandBars
    Set C = CB.FindControl(Id:=Id, recursive:=True)
    If Not C Is Nothing Then C.Enabled = Enabled
Next

End Sub

Sub Dummy()
    '// NoGo
    MsgBox "Sorry command not Available!"
End Sub

 

[dapoole]

Brilliant! Thanks a lot.