mlagrange
asked on
Settings to protect a data entry sheet?
Hello - I have worked up a data entry worksheet where the users would make entries into cells of a data table (entering new rows after existing rows at the bottom of the table).
Most of the entries are in the form of data validation lists, and there are hidden columns with formulas that I don't want them to unhide.
At first glance, I thought the default options of "Protect Sheet" would be what I needed, but it won't let me select a value from the first visible column (that uses a data validation list) on the first blank row ("The cell or chart you are trying to change is protected...")
What am I missing?
Thanks
Most of the entries are in the form of data validation lists, and there are hidden columns with formulas that I don't want them to unhide.
At first glance, I thought the default options of "Protect Sheet" would be what I needed, but it won't let me select a value from the first visible column (that uses a data validation list) on the first blank row ("The cell or chart you are trying to change is protected...")
What am I missing?
Thanks
Before you "Protect Sheet", select entire sheet -> go to right click -> Format Cells-> Protection -> Uncheck "Locked"
ASKER
Ok, tried your suggestions, but now the table will not expand to include the new row
I need this to work like it did before, ie: after the first cell has been entered, the table takes in the new/bottom row, and all the data validation and formulas are applied to it.
I need this to work like it did before, ie: after the first cell has been entered, the table takes in the new/bottom row, and all the data validation and formulas are applied to it.
Excel's base features won't allow the auto expansion/contraction of a table once protected. You will need an Event macro that triggers a temporary unprotect while the table is used.
ASKER
Ok... what would such a macro look like, pray tell? ;-)
It could be perhaps:
where table is between column 1 and 10 of Sheet1.
Password is optional.
You will need to unlock those whole columns first through Format|Cell|Protection
Then right click the tab name and select View Code. Then paste and edit code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column >= 1 And Target.Column <= 4 Then
Sheet1.Unprotect ("Password")
Else
Sheet1.Protect ("Password")
End If
End Sub
where table is between column 1 and 10 of Sheet1.
Password is optional.
You will need to unlock those whole columns first through Format|Cell|Protection
Then right click the tab name and select View Code. Then paste and edit code.
ASKER
Sorry, I wasn't thinking that one all the way through; the user would be able to unhide columns while the sheet was unlocked, right?
The way this sheet is structured, the user can't really screw anything up as far as the entries. I just don't want them unhiding hidden columns.
Is there a way to prevent just that?
The way this sheet is structured, the user can't really screw anything up as far as the entries. I just don't want them unhiding hidden columns.
Is there a way to prevent just that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great! that got me thinking of an alternative - in the Worksheet Selection Change event, I test Target.Address, and if it contains a ":" and alpha characters (1 or more entire columns were selected), I bop them down to the first input cell on the last row.
Private Sub Worksheet_SelectionChange( ByVal Target As Range)
Dim intLastRow As Integer
If InStr(Target.Address, ":") > 0 Then
'MsgBox "range"
If ContainsAlpha_TSB(Target.A ddress) Then
'-- don't let them select a column
'MsgBox "col"
intLastRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(intLastRow, 1).Select
Else
'MsgBox "row"
End If
Else
'MsgBox "cell"
End If
End Sub
That still lets them select individual cells, or an entire row (if they want to delete a row entered in error).
Thanks again
Private Sub Worksheet_SelectionChange(
Dim intLastRow As Integer
If InStr(Target.Address, ":") > 0 Then
'MsgBox "range"
If ContainsAlpha_TSB(Target.A
'-- don't let them select a column
'MsgBox "col"
intLastRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(intLastRow, 1).Select
Else
'MsgBox "row"
End If
Else
'MsgBox "cell"
End If
End Sub
That still lets them select individual cells, or an entire row (if they want to delete a row entered in error).
Thanks again
Now protect the sheet and try again.