Block (to not enter data) all textboxes in a userform if a condition is met

YingFa

Board Regular
Joined
Nov 4, 2019
Messages
63
Hello,

Can I please have your help with a code that blocks all textboxes on a userform (the textboxes are not in sequential order) if column D of sheet1 says "Closed"? So, if the entry has been closed already, no one should be able to overwrite the original data.

Thank you.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
You could put this in the Intialize event for the Userform (after the text box values have been put in)

VBA Code:
Dim oneControl as MSForms.Control

For each oneControl in Me.Controls
    if Typename(oneControl) = "TextBox" Then
        oneControl.Locked = (Sheets("Sheet1").Range("D1").Value = "Closed"
    End If
Next oneControl

As I re-read the OP, I notice "if column D of sheet 1 says "Closed" ". What does that mean, any cell in the column or all cells or.....
 
Upvote 0
Hi,

Thank you for your reply. To answer your question, any cell in the column. Column D (starting on D6) can have open or closed status. I have tried your code, but it is not blocking the textboxes on the form. On that same form, I have a combobox1 which is used to select the entry. E.g., if I select N-001 in my combobox1 but if that entry has status closed on column D, then, all other textboxes and comboboxes on the form should be blocked as to stopped entering data. This, so the record cannot be modified after its closure.

You could put this in the Intialize event for the Userform (after the text box values have been put in)

VBA Code:
Dim oneControl as MSForms.Control

For each oneControl in Me.Controls
    if Typename(oneControl) = "TextBox" Then
        oneControl.Locked = (Sheets("Sheet1").Range("D1").Value = "Closed"
    End If
Next oneControl

As I re-read the OP, I notice "if column D of sheet 1 says "Closed" ". What does that mean, any cell in the column or all cells or.....
 
Upvote 0
My code is testing only D1. If you want to test if it is anywhere on or below D6

VBA Code:
Dim LockText as Boolean
Dim oneControl as MsForms.Control

LockText = IsNumeric(Application.Match("Closed", Sheets("Sheet1").Range("D6:D65536"), 0))

For each oneControl in Me.Controls
    if Typename(oneControl) = "TextBox" Then
        oneControl.Locked = ((Sheets("Sheet1").Range("D1").Value = LockText)
    End If
Next oneControl
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top