2

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.

Tunaki
  • 132,869
  • 46
  • 340
  • 423
Jan Chmelíček
  • 41
  • 1
  • 3
  • Record a macro :) then put that in a small function if you like, and call it upon.. when necessary.. – bonCodigo Feb 04 '13 at 11:35
  • You could use an approach like [this one](http://stackoverflow.com/questions/12064439/vba-worksheet-change-detecting-actual-cell-change/12068586#12068586). 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 Feb 04 '13 at 11:42
  • 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 Feb 04 '13 at 11:53
  • @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 Feb 04 '13 at 11:53
  • I have just checked and *Worksheet_Change* event does not trigger on font style changes or any other format changes. – MPękalski Feb 04 '13 at 12:05
  • @bonCodigo: Yes, you have to unprotect the sheet to make changes to it; then you can re-protect it. All that can be done in the code. – Jean-François Corbett Feb 04 '13 at 12:52

1 Answers1

0

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.

Luke M
  • 1
  • 3