move the cursour to where you want in a protected sheet

[Bodi]

Hello there

How to force the movement of the cursor after the "enter" to follow a specified unprotected cells order in a protectd sheet. This movement will be a combination of vertical and horizontal to get you to the next specifies unprotected cell.

Thanks

Bodi

 

[Derek]

Hello there

You could try this:

Select the cells you want the user to make entries - in the order you want them made - (by selecting and holding down control). Now click the lock cell icon (padlock) to unlock those cells. Next use an On Open event macro to restrict selection to unlocked cells only. Next, protect the worksheet. After an entry in the first cell it should lead the user to the cells in the order you selected them.

Alt +F11 will take you to the VB editor. Doubleclick ThisWorkBook in the VB Properties window and paste this code into the white space.

Private Sub Workbook_Open()
Sheet1.EnableSelection = xlUnlockedCells
End Sub

You will need to save, close and reopen the workbook for it to take effect.
The code relates to Sheet1, change this to your sheet name.


regards
Derek

 

[Bodi]

Hello Derek,
I am afraid it did not work. I may have not done it right. What confused me is your statement "Next use an On Open event macro to restrict selection to unlocked cells only."
I did include the macro codes that you suggested under General, and run it, yet at Enter the cursor did what it wanted and not the pattern that I wanted.

Can we try again different approach

 

[Derek]

Hello again.

If you put the code in a normal macro it may not work. An event macro is one that runs automatically when an event occurs. Try again following my instructions carefully.

Alt +F11 will take you to the VB editor. Doubleclick ThisWorkBook in the VB Properties window and paste this code into the white space.

Private Sub Workbook_Open()
Sheet1.EnableSelection = xlUnlockedCells
End Sub

When you get to the VB Editor there should be a window on the left titled Project - VBA Project. If that is not visible go to View and select Project Explorer to open it. In the project window find your project. It should list the sheet names and below that it has ThisWorkbook. Doubleclick on the ThisWorkbook icon to go to it. The dropdown menu will state General but when you paste the above code it should change to Workbook (because it recognises my code as a workbook event (other events are available in the dropdown menu to the right side). Alt+F11 will return you to your worksheet.

Now you must save and close your workbook then reopen it again. Openning the workbook triggers the macro to restrict selection in Sheet1 to unlocked cells only. (This restriction cancels whenever you close the workbook that is why you need this macro to activate it again whenever you open the file).

Try following my original instructions again and see if you have better luck. (I am using Excel2000)

regards
Derek

 

[Nimrod]

Here's a solution that will only go to unprotected cells when you press the enter key.

Step 1. in "Format..protection" Unlock the cells that you wish to have cursor go to
Step 2. Protect the sheet

Step 3. Install the following code in the Specific Sheet

Private Sub Worksheet_Activate()
Application.OnKey "~", "TABit"
End Sub
Private Sub Worksheet_Deactivate()
Application.OnKey "~"
End Sub

TO INSTALL code in specific sheet:
1. Select the sheet you want code to work in
2. right click the the "Name Tab" of sheet
3. Select "View Code" in drop down menu
4. VBE window will open ... paste code in and exit VB

Step 4:
Create a VBA Module and copy the following code to it..

Public Sub TABit()
Application.SendKeys "{TAB}"
End Sub

TO CREATE MODULE:
1. Press Alt+F11 to open VBEditor
2. On tool bar click on "Insert"
3. on drop down menu selectl "Module"

NOW YOUR READY....
Just unselect and then select the "specific" protected sheet and your done

 

[Nimrod]

Hmmm Now that I've looked at Deriks solution I perfer it over my own Nice job Derek....

However I chosen to put it directly in the specific sheet. This will allow the sheet to change name etc and still work.

Private Sub Worksheet_Activate()
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

TO INSTALL:
1. Select the sheet you want code to work in
2. right click the the "Name Tab" of sheet
3. Select "View Code" in drop down menu
4. VBE window will open ... paste code in and exit VBE

 

[Yogi Anand]

Hi Bodi:

In addition to solutions proposed by Derek and Nimrod, I just wanted to add that if you want to restrict the cursor movement to some specified cells in say Sheet1, then you can unprotect the cells of interest, say B3:E10, activate VBE -- goto properties and specify EnableSelection property value to be limited to xlUnlockedCells. Then protect the worksheet -- that should do it without the need for writing any code, as illustrated in the following ...

Book3
	A	B	C	D	E	F	G
1							Torestrictcursormovementtounprotectedcellsonly
2
3							1.Unlockthecells(sayB3:E10)--viaFORMAT|Cells|ProtectionunchekLOCKED
4
5							2.RightClickontheSheet1tab--andclickonView_Code;activateProperties(pressF4)
6
7							3.ClicktotherightofENABLE_Selectionandchoose1-xlUnlockedCells
8
9							4.Protecttheworksheet
10
11
12
Sheet1

I hope this helps. If I have misunderstood your question -- my apologies!

 

[Derek]

Hi Yogi

I tried that but it seemed to be cancelled when the file was closed and reopened????
Also I find my solution does not always strictly follow the chosen sequence of cell selection. Still pondering that.

regards
Derek

 

[Derek]

Hello again

Here is a different approach you might like to try

Enter the cell addresses you wish to visit - in visit order - in Column A, starting at A2. Hide columns A and B if you wish (or make font color same as background)

Right click your sheet tab, left click View Code and paste this into the white area:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Address(rowabsolute, columnabsolute) = Range("A2").Value Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Columns("A:A").Copy Range("B1")
Range("B1").Delete Shift:=xlUp
Application.EnableEvents = True
Exit Sub
Else
Application.ScreenUpdating = False
Application.EnableEvents = False
If Range("B2").Value = "" Then
Columns("A:A").Copy Range("B1")
Range("B1").Delete Shift:=xlUp
Else
Range("B1").Delete Shift:=xlUp
End If
Range(Range("B1").Value).Select
Application.EnableEvents = True
End If
End Sub

Whichever cell you select it will reselect the next cell in your list and loop around indefinately. The cursor position can be reset to the beginning at any time by selecting the first cell in your list.
regards
Derek

 

[Bodi]

I am sorry guys I think we are deviating form the main objectives. I am running Excel 2003 and I can select cells that remains selectable even when I protect the sheet, this is done fairly easy without any codes or macros.

The objective that I am trying to accomplish is TO HAVE THE CURSOR MOVES IN A SEPCFIC PATTERN THAT I CHOOSE (AND NOT THE CONPUTER CHOISE) IN A PROTECTED SHEET.

Derek, if I have to use your extended codes to accomplish this, I prefer not to do so since the this function is not a big deal and the tempalte file that I want to use it on, is already becoming huge due to other subroutines in it.
Thanks
Bodi