PDA

View Full Version : Worksheet.Protection userinterfaceonly:=true problem



Kained
09-05-2008, 01:36 AM
Hi there,

Please excuse mistakes with my submission, this is my first post.

I have develeped a simple UI for an Access database with Excel, it is a workbook that contains 1 sheet. It builds an SQL query on a few selectable cell criteria and retrieves information from a central MSAccess Database.

The user of this tool can then update this information and return it to the database. However, I have unlocked only certain range of columns and permitted editing. The remainder is for information only.

With the Workbook Open event I have used the following to protect the sheet.

ActiveSheet.Protect UserInterfaceOnly:=True, AllowFiltering:=True

This does indeed protect the sheet but it has an undesirable effect in that the keyboard cursor movemens are restriced on the unlocked cells. Left & Right is fine. Up and Down keystrokes do not work. Very wierd.

To resolve this I have set the worksheet property EnableSelection = 0 xlNoRestrictions.

This will now allow full keyboard cursor use, but now has an undesired effect in allowing the non editible cells to be selected resulting in the familiar dialog box "The cell or chart you are trying to change is protected and therefore read only... etc.. etc..."

I'd like to protect the sheet, and not allow the cells to be selected. I would have thought my original method would have accomplished this but for the undesired keyboard anomoly.:dunno

Has anyone had similar experiences or can shed any light on the subject?

Thank your for your time.

Bob Phillips
09-05-2008, 01:56 AM
Doesn't sound familiar. Can we see the workbook?

Kained
09-08-2008, 12:44 AM
Hi xld,

Thanks for taking an interest. Ive attached the workbook.
Ive anonomised the data on the sheet, so i wont make any sense. But its the functionalty that counts.

And like I mentioned previously, just setting the worksheet property EnableSelection = 0 xlNoRestrictions will resolve this. Give it a try.

Any insite you or anyone has will be most welcome. :bow:

GTO
10-18-2008, 05:20 AM
Kained,

This is an old thread, but by chance, were you working in Excel 2000? If yes, Application.EnableSelection = xlUnlockedCells (+1) seemed to work for me; and as I recall, needs the sheet protected prior (rather than after) this. On my poor ol' laptop, I believe I went as far as throwing in a DoEvents.


Mark