How can I protect only format of the cell ?

B

bookton

Any knows how can lock the format of the cell.
I want users can edit the cell but can not change the font, size, color
etc...
 
L

Leith Ross

Hello Bookton,

Copy this macro code, insert a VBA Module into your project, and paste
the macro. You can run the macro from Excel by pressing ALT+F8 to
display the macro dialog. Select "DisableFormatting" and press the
Enter key. This will prevent the user from changing the cell's format,
and still allow the cell data to be edited.


Code:
--------------------
Sub DisableFormatting()

Dim CmdBar
Dim CmdBarCtrl

'Disable the Formatting toolBar
Excel.CommandBars("Formatting").Enabled = False

Set CmdBar = Excel.CommandBars("Worksheet Menu Bar")

'Disable the controls on the Format Menu
Set CmdBarCtrl = CmdBar.Controls("F&ormat")
With CmdBarCtrl
.Controls("C&ells...").Enabled = False
.Controls("&AutoFormat...").Enabled = False
.Controls("Con&ditional Formatting...").Enabled = False
End With

'Disable the controls on the Edit Menu
Set CmdBarCtrl = CmdBar.Controls("&Edit")
With CmdBarCtrl
.Controls("Cle&ar").Enabled = False
.Controls("F&ill").Enabled = False
.Controls("&Delete...").Enabled = False
End With

'Disable controls on the Right-Click Context Menu
With Excel.CommandBars("Cell")
.Controls("F&ormat cells...").Enabled = False
.Controls("&Insert...").Enabled = False
.Controls("&Delete...").Enabled = False
End With

End Sub
 
Top