Gord
Mon Oct 13 17:05:08 CDT 2008
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@replytogroup.com> 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@4ax.com...
>> 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
>>
>> On Sun, 12 Oct 2008 17:05:50 -0700, "Doug Glancy"
>> <nobodyhere@replytogroup.com> 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@TK2MSFTNGP03.phx.gbl...
>>>> 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?
>>>>
>>>> --
>>>> Steve
>>>>
>>>> "Doug Glancy" <nobodyhere@replytogroup.com> wrote in message
>>>> news:uIESaKILJHA.3412@TK2MSFTNGP05.phx.gbl...
>>>>> 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@TK2MSFTNGP04.phx.gbl...
>>>>>> 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
>>>>>>
>>>>>> --
>>>>>> Steve
>>>>>>
>>>>>> "Doug Glancy" <nobodyhere@replytogroup.com> wrote in message
>>>>>> news:uLh8Mc8KJHA.5232@TK2MSFTNGP05.phx.gbl...
>>>>>>> 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" <Somewhere@NotHere> wrote in message
>>>>>>> news:%23j1iBq0KJHA.1160@TK2MSFTNGP04.phx.gbl...
>>>>>>>> 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.
>>>>>>>>
>>>>>>>> --
>>>>>>>> Steve
>>>>>>>>
>>>>>>>> "Doug Glancy" <nobodyhere@replytogroup.com> wrote in message
>>>>>>>> news:OlevFGwKJHA.5328@TK2MSFTNGP04.phx.gbl...
>>>>>>>>> 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
>>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>
>>
>