Excel Sheet Lockout

November 23, 2007 11:07 AM

I have a spreadsheet that I want to lock after a certain amount of data is entered. It needs to be an auto lockout as I cannot count on the user to stop entering data after a certain point. MI

OK, so let's just say that I have 3 columns with 10 rows. All 30 rows will be filled and when that happens, all editing is disabled.

I was goofing with some VBA with works surprisingly well but how would I get something like this to trigger when cell C10 is filled in?

Here is the little code snippet

Dim dtval

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> dtval Then Target.Formula = dtval
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
dtval = Target.Formula
End Sub

posted by lampshade to Technology (2 answers total)

"When cell C10 is filled in" is not really the same as "after a certain amount of data is entered," unless all the data has to be entered sequentially. I think conceptually, after each edit you have to loop through every cell in your range of data (the 3 columns/10 rows in your example) and check whether every cell has a value. I don't know VBA so I can't help you with the code for it.
posted by greenmagnet at 12:47 PM on November 23, 2007

After you do the loop test and fill in all the blanks all you have to do is have a11 put the data somewhere else and generate a message "on enter" that data collection is complete.
posted by ptm at 2:48 AM on November 24, 2007