Re: Protecting data lists but allowing data entry

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Gord,

"Thanks for the rap upside the head."

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

Doug

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:u6o7f4pujsfkij7q04jkn9quhiqkr9t698@xxxxxxxxxx
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

On Mon, 13 Oct 2008 15:22:14 -0700, "Doug Glancy"
<nobodyhere@xxxxxxxxxxxxxxxx> wrote:

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" <gorddibbATshawDOTca> wrote in message
news:i4h7f49od7v1gt1mc4m17eikcrh3gm8oup@xxxxxxxxxx
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

On Mon, 13 Oct 2008 12:56:59 -0700, "Doug Glancy"
<nobodyhere@xxxxxxxxxxxxxxxx> wrote:

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

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:tl77f4thvk8l15kcjrgb8c8fjrj3i6di36@xxxxxxxxxx
This event code will allow you to select anywhere within the List and
the
*** 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 *** 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 *** event code. Right-click on the *** tab and "View
Code"

Copy/paste into that module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Sun, 12 Oct 2008 17:05:50 -0700, "Doug Glancy"
<nobodyhere@xxxxxxxxxxxxxxxx> wrote:

Steve,

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

Thanks,

Doug

"AltaEgo" <Somewhere@NotHere> wrote in message
news:%23tRn7bLLJHA.1556@xxxxxxxxxxxxxxxxxxxxxxx
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()
...
Active***.Protect Password:="thepassword"
... do the task ...
Active***.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 *** to work
around
the problem?

--
Steve

"Doug Glancy" <nobodyhere@xxxxxxxxxxxxxxxx> wrote in message
news:uIESaKILJHA.3412@xxxxxxxxxxxxxxxxxxxxxxx
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" <Somewhere@NotHere> wrote in message
news:eisW40BLJHA.3496@xxxxxxxxxxxxxxxxxxxxxxx
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 ***
4) Check "Insert Columns"
5) Check "Insert Rows"
6) Enter your password
7) Click OK
8) Re-enter your password

--
Steve

"Doug Glancy" <nobodyhere@xxxxxxxxxxxxxxxx> wrote in message
news:uLh8Mc8KJHA.5232@xxxxxxxxxxxxxxxxxxxxxxx
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 ***, and have the
choice
available in the List menu's Insert>Row item.

Doug

"AltaEgo" <Somewhere@NotHere> wrote in message
news:%23j1iBq0KJHA.1160@xxxxxxxxxxxxxxxxxxxxxxx
Not sure if other versions of XL are different but, in XL 2003
when
you protect the work*** it pops up a list of items on the
Protect
*** popup window. All you need do is check items in the "Allow
all
users of this work*** to:" section. "Insert columns" and
"Insert
rows" are options 6 & 7 in the list.

--
Steve

"Doug Glancy" <nobodyhere@xxxxxxxxxxxxxxxx> wrote in message
news:OlevFGwKJHA.5328@xxxxxxxxxxxxxxxxxxxxxxx
I've recently been tinkering with Excel data lists in 2003. I
can't
figure out a way to protect the work*** 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













.

Quantcast