Unlocking and Dragging Excel VB Textboxes Freely in Protected Worksheets

Unlocking Dragging Excel VB Textboxes Freely in Protected Worksheets

In this article, we will discuss how to enable dragging of VB (Visual Basic) textboxes in Excel protected worksheets. This can be particularly useful when creating a scheduling app in Excel's testing center, where textboxes of different heights can represent tests at different times.

Benefits of Enabling Dragging of VB Textboxes in Protected Worksheets

Protecting worksheets is a common practice in Excel to prevent accidental changes to the data or formatting. However, this can also prevent users from moving or resizing VB textboxes. By enabling dragging of VB textboxes in protected worksheets, users can still interact with the textboxes while the worksheet remains protected.

Code to Enable Dragging of VB Textboxes in Protected Worksheets

To enable dragging of VB textboxes in protected worksheets, you can use the following code:

Private Sub Workbook_Open()
    ActiveSheet.Protect Password:="your_password", DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.Shapes.Range(Array("TextBox1")).Select
    With Selection.ShapeRange
        .LockAspectRatio = msoFalse
        .Width = 100
        .Height = 50
    End With
    Selection.ShapeRange.Placement = xlMoveAndSize
End Sub

In this code, replace "your_password" with the password you want to use to protect the worksheet. The code sets the password for the active sheet, allows drawing objects to be moved and sized, and sets the width and height of the textbox. The Placement property is set to xlMoveAndSize, which allows the textbox to be moved and resized.

How to Use the Code

To use the code, follow these steps:

1. Press Alt + F11 to open the Visual Basic Editor.
2. In the Project Explorer, right-click on ThisWorkbook and select View Code.
3. Copy and paste the code into the code editor.
4. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm).
5. Close the Visual Basic Editor and return to the worksheet.
6. Protect the worksheet by going to the Review tab and clicking Protect Sheet.
7. Enter the password and check the "Select locked cells" option.
8. Save the workbook.

Enabling dragging of VB textboxes in protected worksheets can be useful when creating a scheduling app in Excel's testing center. By using the code provided in this article, you can allow users to move and resize VB textboxes while the worksheet remains protected. This can help prevent accidental changes to the data or formatting while still allowing users to interact with the textboxes.

References

• Worksheet.Protect Method (Excel)
• ShapeRange.Placement Property (Excel)