Prevent data entry in a Userform field after 20 entries

[Luisao82]

This is my second post and the first in this particular Forum, so Hello everyone.
You'll have to forgive me coz my english is not that great.


Right, let's get down to business.

I have a file that automatically shows at startup, a Dialog Box (UserForm) with a "Date" field, which filling is required to continue using the file. If OK button is clicked and none is written in that field, it shows an error Dialog Box and won't allow the user to continue until he writes something in the "Date" field:

1. So, parallel to this I want to create a Macro which will generate and update (in a Sheet called: DateField_History), a list of all the values entered in the "Date" field (starting from cell B3 and down).

2. In the same Sheet, in cell F3 I'll use a simple COUNT formula which will count all the Dates listed in column B3.

Now, here's the main question:

- if the value (counting) in cell F3 reaches "20", I want a code that will automatically prevent any data entry in the Date field (startup Dialog Box), from that moment on... until I click on a button created by me, specifically to delete all the 'stored' dates in Sheet "DateField_History", and password protected of course .

It's some sort of "conditional cell locking macro", as you can see.



Any help will be greatly appreciated.
Cheers*

 

[ChrisM]

Easy enough. At the correct moment just run this line of code:

myTextBox.Locked = True

The question is when do you want to run that code. You could check it when your userform opens:

Sub Workbook_Open
   UserForm.Show
   If Sheets("DateField_History").Range("F3") >= 20 Then
          myTextbox.Locked = True
   End If
End Sub

Or, you could run the same line of code but execute it in the Worksheet_Change event, to monitor the COUNT function. Once it hits 20 then lock the textbox.

 

[Luisao82]

Or, you could run the same line of code but execute it in the Worksheet_Change event, to monitor the COUNT function. Once it hits 20 then lock the textbox.

Exactly, Chris.

I'll test that as soon as possible. Thanks!



Any ideia for question 1.?