Protecting user from changing previous data

[apache007]

Hi All,

I have a block of data A110 with Protection using ALLOW USER TO EDIT RANGE.

The user enter data with the following order:
Input 1: A1, B1, C1, D1
Input 2: A2, B2, C2, D2
Input 3: A3, B3, C3, D3


I would like to Automatically prevent the user from changing ROW A1-D1 when
Inserting NEW ROWS (Row A2-D2, Row A3-D3).

Thank you.

 

[JLatham]

This can be a very complex issue if you don't think things out really well
ahead of time. What happens if, after they've typed data in A11 and while
entering more data in rows below row 1, they realize they made a mistake in
one of the entries above it? How do they correct the error?

How do you know they won't start in row 2 instead of row 1? Is that not
allowed at all?

Are the rows in your example already on the sheet for them to type into, or
do they actually have to use Insert --> Row to get a new row?

 

[JLatham]

Disregard the question about having to insert new rows. I realize now that
you said you have A110 set up already to permit the data entries.

But we still have the questions about "what if they need" to correct an
error in earlier rows, and one more I didn't mention before:

What if they don't completely fill a row initially? Suppose they only make
entries in A1 and B1 and then the next entry they make is in A2? What do we
do with C1 and D1?

 

[apache007]

Hi JLatham,

Thanks for your insight.

Perhaps a code such as this may suffice:

- Given data range is A110
- If ANY of the CELL in that particular row is FILLED (ex. A3/B3/C3/D3
is filled)
THEN....the aboved rows are not allowed to be EDITed (ex. ROW 1 and ROW 2)
is PROTECTED
- If Need to change the above CELLs, then I will have to do one of these
options
1. Delete your Macro first, Change and then Put it back on
2. Cut / Delete ALL Rows below it and put them back on.

==================

I know there are loopholes on that code, but I just want to prevent the
users from changing the previous data. At least, there are a couple more
barriers.

More alternatives are appreciated.

 

[JLatham]

I think I can work something up that adheres to those rules. Give me a day
or so - other irons in the fire.

Also, see my response to your request for control of the user input.

 

[JLatham]

Try this combination of worksheet-event process handling codes. The
_Change() event processor will lock the cells, one by one from A1 to E3000 as
they are used, while the _SelectionChange() event deals with forcing the
focus to the next empty cell in that sequence.

Choose the worksheet, right-click the sheet's name tab and choose [View
Code] and then copy and paste all the code below into the code module and
edit it to set things like the password as needed.

Private Sub Worksheet_Change(ByVal target As Range)
'this depends on the Worksheet_SelectionChange() event
'handler to force the focus to the next cell that
'should have an entry placed into it, working
'through columns A:E, rows 1:3000 in sequence
'
'start by selecting all cells on the worksheet and
'using Format --> Cells --> [Protection] to 'Unlock'
'all cells so that entries can be made in them
'The cells will be locked, one at a time as
'entries are made into them
'
Const sheetPassword = "myPassword" ' change as required

If target.Column > 5 Or _
target.Row > 3000 Or _
IsEmpty(target) Then
Exit Sub
End If
Me.Unprotect Password:=sheetPassword
target.Locked = True
Me.Protect Password:=sheetPassword

End Sub

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Dim protectedRange As Range
Dim anyCell As Range

If target.Column > 5 Or _
target.Row > 3000 Then
'not in A:E, or below row 3000
'ignore
Exit Sub
End If
Set protectedRange = Me.Range("A1:E3000")
For Each anyCell In protectedRange
If IsEmpty(anyCell) Then
If anyCell.Address = target.Address Then
Exit For
Else
Application.Goto anyCell
'to force scroll to the cell
'use this instead
'Application.Goto anyCell, True
Exit For
End If
End If
Next
Set protectedRange = Nothing
End Sub