control advancing to cells

[Junior]

My spreadsheet has protected and unprotected cells
i thought that when the sheet is protected -
it would advance to the next unprotected cell when return is pressed
but not so on this one-
how can i program which cell it will advance to next?

 

[George Clark]

It's certainly possible to control where the cursor goes each time, but it
can get a bit complicated, depending on the setup in the worksheet. Perhaps
simply preventing the cursor from going to protected cells would suffice.

To do that, in the Workbook_Open event you could put something like:

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

Replace "Sheet1", of course, with whatever the name of the worksheet is. You
can do the same for multiple sheets if necessary.

As long as the sheet is protected, the cursor can only go to cells that are
marked as "unlocked."

As with any programming solution, the user must enable macros when they open
the workbook for this to work. Unfortunately, even though you can set the
Worksheet.EnableSelection property in the VBE, it doesn't "stick", so must
be set each time the workbook is opened.

George

My spreadsheet has protected and unprotected cells
i thought that when the sheet is protected -
it would advance to the next unprotected cell when return is pressed
but not so on this one-
how can i program which cell it will advance to next?

--
Mac Word FAQ: <http://www.mvps.org/word/FAQs/WordMac/index.html>
Entourage Help Page: <http://www.entourage.mvps.org/>
Or try Google: <http://www.google.com>

Please do not reply to this message. Although the email address is perfectly
valid, any replies to this account never get to the Inbox on my computer.

 

[Junior]

George- please excuse my dumbness- i don't usually do more than basics in
Excel - mostly use Access and understand the sub you provided
but don't know how to put the sub in the Open event
actually don't even know where to find the open event to place the code?
help please

 

[George Clark]

On the Tools menu, go to Macro, then select Visual Basic Editor (VBE) on the
submenu. Once in the VBE, on the left you should see a list of the sheets in
the workbook, and at the bottom of that list will be "ThisWorkbook".
Double-click on that to open the Workbook code window. At the top of the
code window there is a drop-down that probably has "General" selected;
select "Workbook" on that drop-down.

You should now have the Workbook_Open event ready for your to enter your
code. Do that, then return to "regular" Excel and Save the workbook.

George

George- please excuse my dumbness- i don't usually do more than basics in
Excel - mostly use Access and understand the sub you provided
but don't know how to put the sub in the Open event
actually don't even know where to find the open event to place the code?
help please

--
Mac Word FAQ: <http://www.mvps.org/word/FAQs/WordMac/index.html>
Entourage Help Page: <http://www.entourage.mvps.org/>
Or try Google: <http://www.google.com>

Please do not reply to this message. Although the email address is perfectly
valid, any replies to this account never get to the Inbox on my computer.