Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Settings to protect a data entry sheet?

Posted on 2014-04-01
9
274 Views
Last Modified: 2014-04-01
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
0
Comment
Question by:mlagrange
  • 4
  • 4
9 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39970085
Before you protect the worksheet, you need to "unlock" the cells you want to have people edit.  Right-click on the columns or cells you want to have open after protecting.  Choose Format Cells, then in the Protection tab, uncheck the Locked checkbox.

Now protect the sheet and try again.
0
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 39970110
Before you "Protect Sheet", select entire sheet -> go to right click -> Format Cells-> Protection -> Uncheck "Locked"
0
 

Author Comment

by:mlagrange
ID: 39970157
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.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 23

Expert Comment

by:NBVC
ID: 39970206
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.
0
 

Author Comment

by:mlagrange
ID: 39970236
Ok... what would such a macro look like, pray tell?  ;-)
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39970275
It could be perhaps:

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

Open in new window


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.
0
 

Author Comment

by:mlagrange
ID: 39970305
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?
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39970327
Something like this maybe:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Columns("A:B").EntireColumn.Hidden = True
End Sub

Open in new window


where columns A:B are ones you want hidden.

the user can unhide the columns.  But as soon as they click anywhere it re-hides, not giving a chance to edit.
0
 

Author Comment

by:mlagrange
ID: 39970836
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.Address) 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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question