Excel (VBA): Disabling cell formatting except bold font

Question

I'd like to disable changing the format of the cell except making font bold. Is there any way to achieve that?

Obviously, .Protect AllowFormattingCells:=True enables all possible format changes.

I've thought that maybe making custom button on Ribbon could serve for this (i.e. unprotecting sheet, making the content bold and protecting again), but I wonder whether there is some more convenient approach to this problem.

I've come across similar issue at http://www.excelforum.com/excel-programming-vba-macros/676299-use-vba-to-lock-all-cell-formatting-except-background-color.html - but it also remains unsolved.

Record a macro :) then put that in a small function if you like, and call it upon.. when necessary.. — bonCodigo
You could use an approach like this one. Except that instead of comparing the old and new .Value, you'd compare the old and new formatting, and revert to the old formatting if the change was something else than bold font. — Jean-François Corbett
But remember that if you decide to use events like Worksheet_Change you have to take into account that the argument you are going to use, TARGET, is a Range, so it can span across many cells. — MPękalski
@Jean so you don't have to unprotect the sheet for that? :) as for Events, OP can turn it off by the Application.EnableEvents = False — bonCodigo
I have just checked and Worksheet_Change event does not trigger on font style changes or any other format changes. — MPękalski

Luke M Luke M · Accepted Answer · 2013-02-12T15:19:16

Not a perfect solution, but a possible workaround. In the ThisWorkbook module, paste these events:

Private Sub Workbook_Activate()
Application.OnKey "^b", "MakeBold"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "^b"
End Sub

Then, in a regular module:

Sub MakeBold()
ActiveSheet.Unprotect
On Error Resume Next
Selection.Font.Bold = Not (Selection.Font.Bold)
On Error GoTo 0
ActiveSheet.Protect
End Sub

Limitation is that it only works for the keyboard shortcut, and not the ribbon button. I suppose you could create a custom button and "disguise" it as the Bold button, but it's still an imperfect workaround.

Excel (VBA): Disabling cell formatting except bold font

1 Answers