Stop Excel Control being Clicked

Textbox hack

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.

How to make the most of Excel Macros


Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.