In a protected worksheet users can still click on checkbox and option button controls. A warning message will pop up if the control’s linked cells are locked. There is a technique you can use to stop users clicking on these controls. This involves a macro that you can run just before you protect the sheet.
If you run the macro below when the sheet is unprotected and active it will place an invisible text box above each control.
When the sheet is protected this will stop users clicking on the control.
Sub CoverControl() 'this macro covers each form control in the active sheet with an invisible ' textbox to stop them being clicked when the sheet is protected Dim shp As Shape For Each shp In ActiveSheet.Shapes If shp.Type = msoFormControl Then ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _ shp.Left, shp.Top, shp.Width, shp.Height).Select Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Line.Visible = msoFalse End If Next shp Set shp = Nothing End Sub |
This code is based on a recorded macro, so it uses the Select and Selection keywords.
Note: this code can be saved in the Personal Macro Workbook and run from there. It doesn’t have to be in the file itself.
If you need to remove the invisible textboxes you can use the code below.
Sub RemoveCover() 'this macro removes invisible textboxes in the active sheet Dim shp As Shape For Each shp In ActiveSheet.Shapes With shp If .Type = msoTextBox Then If .TextFrame2.TextRange.Characters.Text = "" Then .Delete End If End With Next shp Set shp = Nothing End Sub |
If you are not familiar with macros, then the link below can help you install and use the above macros.
Please note: I reserve the right to delete comments that are offensive or off-topic.