Protecting data lists but allowing data entry

[Doug Glancy]

I've recently been tinkering with Excel data lists in 2003. I can't figure
out a way to protect the worksheet and still have people be able to insert
new rows. The Insert button on the Row menu still works, but it generates a
"Protected Cells" warning several times. The table's Insert>Row option is
grayed out.

I like the functionality of inserting new rows, complete with formulas, but
it's rather pointless if I can't protect the formulas.

Any tips on this?

Thanks is advance,

Doug

 

[AltaEgo]

Not sure if other versions of XL are different but, in XL 2003 when you
protect the worksheet it pops up a list of items on the Protect Sheet popup
window. All you need do is check items in the "Allow all users of this
worksheet to:" section. "Insert columns" and "Insert rows" are options 6 &
7 in the list.

 

[Doug Glancy]

I'm using 2003 and I did that. The Insert>Rows choice in the List menu is
still grayed out. The Insert item in the Row menu (and the Row iem in the
Insert menu) is available, and it ultimately works, but it generates
several, "You are atempting to change a locked cell" messages.

What I'd like is to be able to protect the sheet, and have the choice
available in the List menu's Insert>Row item.

Doug

 

[AltaEgo]

It tests fine for me. I can only suggest you try again following carefully
the steps below.

Using XL2003 with protection off:

1) Click Tools
2) Click Protection
3) Click Protect Sheet
4) Check "Insert Columns"
5) Check "Insert Rows"
6) Enter your password
7) Click OK
8) Re-enter your password

 

[Doug Glancy]

Steve,

Thanks for sticking with the thread.

I've been doing the protection exactly the same as you describe all along
(except without the password).

I tried it again on a different computer, also using XL 03. After doing it,
the Insert>Row and Delete>Row buttons are both grayed out in the List
toolbar and the list's right-click menu (along with the equivalent Column
choices).

Also, the New Row (row with asterisk at bottom of list isn't available). Is
it available for you?

Have you used this functionality in an actual project?

Doug

 

[AltaEgo]

Doug,

I presume by your question, you are not striking the same problem in a new
workbook. If this is the case, possibly there is a glitch somewhere in your
project that is preventing Excel working as its makers intended.

Try this:

1) Copy (not move) all of you worksheets without moving code to a new
workbook and try again. If this works
2) Clean up you code (in the existing workbook) with VBA Code Cleaner (Link
below) and copy modules to the new workbook.

http://www.appspro.com/Utilities/CodeCleaner.htm

Alternatively, I like to tightly control what my projects and normally
allow none of the listed option other than the two defaults (sometimes also
removing access to select locked cells). Normally, I control what can be
done switching password protection through VBA:

Sub Foobar()
....
ActiveSheet.Protect Password:="thepassword"
.... do the task ...
ActiveSheet.Unprotect Password:="thepassword"
....
End Sub

You could create a "insert row at selected cell" button using the above as
the basis for a workaround.

You may have valid reasons for retaining protection but have you considered
placing raw data in a separate unprotected sheet to work around the problem?

 

[Doug Glancy]

Steve,

I guess I wasn't clear. I was experimenting with lists, so on eahc computer
I'm working only with a small list. I've seen the exact same results with
both workbooks, which have no VBA, one three-row list, and nothing else.

I currently have addins where the normal Row>Insert buttons have been hooked
in a class to substitute my own insert row functionality, like you describe.
The reason I'm fooling with lists is to see whether I can replace that code
(assuming the projects are for 2003 or later) with list functionality.

Did you have an answer to my question about whether you see the last row
with the asterisk?

Thanks,

Doug

 

[Doug Glancy]

Steve,

You do understand that I'm talking about Lists, as in Data>List?

Thanks,

Doug

 

[AltaEgo]

I thought you were talking about lists rather than defining data lists. I
never use these, preferring dynamic named ranges. That said, When I do
define a data list, on applying protection as below, I can insert rows. To
retain the list filter, I also need to allow auto filter. The asterisk
disappears with protection and reappears after protection is removed.

 

[Gord Dibben]

This event code will allow you to select anywhere within the List and the
sheet will become unprotected for inserting/deleting rows and columns within
the List only.

The ListObject Range will expand with inserted rows and columns

Select anywhere outside the List and sheet will re-protect.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim objlist As ListObject
Set objlist = Me.ListObjects(1)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, objlist.Range) Is Nothing Then
Me.Unprotect Password:="justme"
Else
With Me
.Protect Password:="justme"
.EnableSelection = xlUnlockedCells
.EnableSelection = xllockedCells
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

 

[Doug Glancy]

Thanks Gord,

I tried something like this. But this allows the user to mess up the
formulas in the List. And the main situation where I'd use a list (as a
substitute for VBA to insert new rows) is when the rows I'm inserting have
formulas.

TheVBE chokes on the line:
..EnableSelection = xllockedCells
so I commented it out. Did you intend something else there?

Thanks,

Doug

 

[Doug Glancy]

Gord,

Just a couple more thoughts:

If the list allowed entry without VBA at all on a protected sheet, that
would be a great tool. And when I first started looking at it, that's what
I expected. It seems silly to have protection turn off the ability to enter
rows, because, after all, that's one of the main benefits of the List. If
somebody wanted it not to be modified when the sheet is protected, why would
they use a List in the first place?

And once I have to add even one line of VBA, it loses a fair amount of
utility. I had hoped that it would be a way to distribute fairly simple
worksheets to multiple users, without the headache of addins being updated
from a centralized source.

Does that make sense?

Doug

 

[AltaEgo]

Doug

Curiosity leads me to ask, why are your users adding to a list containing
formula?

 

[Doug Glancy]

Steve,

For example, a waitlist that calculates a persons place in line based on
other criteria in the row. Or a calculation of median income category,
based on household size and income.

Doug

 

[Gord Dibben]

I don't know why it choked, but I added that so's users could select any
cell outside or inside the List.

These two lines

.EnableSelection = xlUnlockedCells
.EnableSelection = xllockedCells

could be one

..EnableSelection = xlNoRestrictions


Gord

 

[Doug Glancy]

Gord,

I should have been clearer. It choked because it's not a choice in my
version of 2003. As you say "xlNoRestrictions" is a choice, along with
"xlNoSelection". (I notice that "xllockedCells" is not fully capitalized in
your code either, as it would be if Intellisense recognized it. I'm
guessing you cut and pasted from the previous line?).

Thanks again,

Doug

 

[Gord Dibben]

If you select the row header you can insert a row on a protected sheet
providing insert rows is allowed.

But that will insert a row across the entire sheet, not just in the List.

The code I provided just inserts the row within the List.

As far as distribution............I guess you could instruct all users to
set security at medium and enable macros.

Or sign the workbook.


Gord

 

[Doug Glancy]

Gord,

You're right on all counts. (The insert from the Row header also generates
"the cell you are attempting to change is protected..." messages, at least
in some cases.)

Again, I was curious to see if Lists could be a VBA-free tool for fairly
simple lists that I distribute. It seems the answer is "no".

Thanks again.

Doug

 

[Gord Dibben]

Doug

Good chance I cut and pasted as you surmise.

xlunlockedcells is not an option in 2003..........maybe not in 2007 either?

Thanks for the rap upside the head.


Gord

 

[Doug Glancy]

Gord,

"Thanks for the rap upside the head."

I'd never do that! You're too valuable.

Doug