How to prevent data entry in WS TextBox


0. The WS TextBox.Locked property

  1. The TextBox.Locked property on a WorkSheet, functions differently to the TextBox.Locked property on a UserForm
  2. TextBox.Locked
    1. UserForm - when the UserForm is displayed, TextBox.Locked = True prevents text entry in the TextBox
    2. 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.


text box locked property
Fig 1: InData TextBox, and RetValue Label - Scroll image to view. ActiveX TextBox for the return value is replaced by an ActiveX Label

WorkSheet controls in figure 1:

  1. Input value: ActiveX TextBox named txtInValue
  2. 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