Protecting Spreadsheet Layout

dapoole

New Member
Joined
Jul 20, 2004
Messages
22
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Right Click on the Worksheet Tab

Select View Code from the Menu List

Paste the Following into the VB Window:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,308
Messages
6,171,335
Members
452,396
Latest member
ajl_ahmed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top