How to prevent data entry in WS TextBox
0. The WS TextBox.Locked property
- The TextBox.Locked property on a WorkSheet, functions differently to the TextBox.Locked property on a UserForm
- TextBox.Locked
- UserForm - when the UserForm is displayed, TextBox.Locked = True prevents text entry in the TextBox
- WorkSheet - when TextBox.Locked = True, and Worksheet.Protect (DrawingObjects:=True) is set, then the TextBox cannot be edited in Design mode. Text entry is still permitted in the TextBox
1. Prevent text entry
This section demonstrates a work-around to the WS TextBox.Locked text entry issue. The solution replaces the result TextBox control with a Label control. See figure 1.

WorkSheet controls in figure 1:
- Input value: ActiveX TextBox named txtInValue
- Return value: ActiveX Label named lblResult. lblResult.SpecialEffect = fmSpecialEffectSunken
2. VBA code
2.1 WS TextBox and Label
Here is the code for the txtInValue Change and KeyPress events (code 1).
Code 1: VBA txtInValue control (WS Module)
Private Sub txtInValue_Change() lblResult.Caption = Val(txtInValue) ^ 2 'ActiveSheet.Shapes("txtResult2").TextFrame.Characters.Text = Val(txtInValue) ^ 2 End Sub ' =========================== Private Sub txtInValue_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' Reference: https://excelatfinance.com/xlf18/xlf-multiplier-textbox-keypress.php Select Case KeyAscii Case Asc("0") To Asc("9") Case Asc(".") If InStr(1, txtInValue.Text, ".") > 0 Then KeyAscii = 0 End If Case Else KeyAscii = 0 End Select End Sub
2.2 WB open event
The code 2 procedure initializes the Input value box by selecting it and setting value to zero, if required.
Code 2: WorkBook_Open event in the ThisWorkbook module
Private Sub Workbook_Open() With Sheet1 .txtInValue.Activate .txtInValue.Text = 0# End With End Sub
- Download the file: xlf-textbox-lock.xlsm [70 KB]
- Development platform: Office 365 ProPlus Excel 64 bit.
- Published: 2 June 2020
- Revised: Friday 24th of February 2023 - 03:13 PM, Pacific Time (PT)