Protected Worksheet - Excel bug?

[Kained]

Hi there,
I have developed 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.

[vba]ActiveSheet.Protect UserInterfaceOnly:=True, AllowFiltering:=True[/vba]

This does indeed protect the sheet but it has an undesirable effect in that the keyboard cursor movemens are restricted 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..." (which is a pain in the butt for the user)

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.

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

Thank your for your time.

[GTO]

Greetings Kained,

If you are saying that the cells above/below are actually unlocked, but the up/down arrow are not working, then nope. I see you are working in a post 2000 version, and I'm at home (2000 here, 2003 at work). Anyways, for kicks, try having it protect first, then set enable:

[vba]With ActiveSheet
.Protect Password:="MyPassword", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
.EnableSelection = xlUnlockedCells
End With[/vba]

No promises, but would be a harmless check.

Hope this helps,

Mark

[Kained]

Greetings Kained,

If you are saying that the cells above/below are actually unlocked, but the up/down arrow are not working, then nope. I see you are working in a post 2000 version, and I'm at home (2000 here, 2003 at work). Anyways, for kicks, try having it protect first, then set enable:

[vba]With ActiveSheet
.Protect Password:="MyPassword", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
.EnableSelection = xlUnlockedCells
End With[/vba]
No promises, but would be a harmless check.

Hope this helps,

Mark

Mark, thanks for your reply.

Yes, cells are unlocked. I use a Union range to unlock them.
I've tried both ways .enableselection before and after. It makes no difference.

Within the worksheet I use the _change event to unprotect the sheet, allow myself to insert time stamps in an area that is protected, then protect the sheet again. Could this possible be an issue?

[GTO]

Golly, somehow you caught me at home (xl2000) again :-(

I read twice, but am tired... what ver are you running?

As to the change event, AFTER you reprotect the sheet, change .EnableSelection.

This well may not be the problem, as allow filtering has to be post 2000.

Sorry not more help,

Mark

[Simon Lloyd]

Are you importing the cells from an access data table?

[Kained]

Simon, Appologies for the cross post... I confess I've been somewhat ignorant of BB culture.

GTO - am working with Office 2007 on winXP. Also .Enableselection is always after re-enabling events(have also worked it into a toggle subroutine). Thank you for your time - this is so obscure I was hoping someone had experienced this wierdness before.

[vba]
Sub ToggleProtect()
If ActiveSheet.ProtectionMode Then
ActiveSheet.Protect UserInterfaceOnly:=True, AllowFiltering:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Else
ActiveSheet.Unprotect
End If
End Sub
[/vba]


Another symptom is that the cursor jumps to the top left of the sheet and is restricted in the first two visible columns if I press Up&Left together... mad!

[Aussiebear]

Please post the cross post link so we can see if any progress has been made there.

[Kained]

I had posted in Exel Help forum, but this was last August... there has been zero response.

[Simon Lloyd]

Kained, can you post the workbook?, remember to remove or replace sensitive data but the layout/structure should remain the same.

[Kained]

Thanks Simon, Ive attached a copy.

I've removed sensitive data and replaced with dummy data.

If you open the workbook you will very quickly see my navigation problem. To get around this issue in the 'ToggleProtect' subroutine and Openworkbook event am having to set the sheet property for Enable selection to 'xlNoRestrictions' instead of 'xlUnlockedCells'

I've code spread through the workbook, using events on opening the workbook, change events on Sheet1 and there is a code module where most of the code resides for sending and recieving the db data.

The forums help with this is very much appreciated.

Kind regards,
Kained

[GTO]

Hi Kained,

I could not figure this out, but did find these possibly related:

http://support.microsoft.com/kb/213741

http://support.microsoft.com/kb/178683


Mark

[Kained]

GTO, thank you.

I think this hits the nail on the head.

I will revert to having the annoying Msgbox pop ups.