Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
control advancing to cells
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
control advancing to cells
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 On 6/23/04 00:12, Junior did expound most eloquently: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
control advancing to cells
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" wrote in message ... 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 On 6/23/04 00:12, Junior did expound most eloquently: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
control advancing to cells
Junior
Hitting the TAB key will move you from unlocked cell to next unlocked cell in a left to right, top to bottom order. DO NOT use the <ENTER key. You can also control the movement with a Named Range and no sheet protection. Assuming your range of cells to be A1, B2, C3, F4, A2, F1 for example. Select the Second cell(B2) you want in the range then CRTL + click your way through the range in the order you wish, ending with the First cell(A1). Name this range under InsertNameDefineOK. Now click on NameBox(top left corner above row 1 and col A), select the range name to highlight the range. With these cells selected, you can input data and Tab or Enter your way through the range in the order you selected. Note: there is a limit of about 25 - 30 cells to a range using this method due to a 255 character limit in a named range. Longer sheet names will reduce the number of cells considerably. If more needed, you can enter them manually in the "refers to" box. From Debra Dalgleish..... The limit is 255 characters in the Name definition. For example, I can define a range of 46 non-contiguous cells, with the following string: =$B$2,$D$2,$F$2,$H$2,$J$2,$B$4,$D$4,$F$4,$H$4,$J$ 4,$B$6,$D$6,$F$6,$H$6, $J$6,$B$8,$D$8,$F$8,$H$8,$J$8,$B$10,$D$10,$F$10,$ H$10,$J$10,$B$12,$D$12, $F$12,$H$12,$J$12,$B$14,$D$14,$F$14,$H$14,$J$14,$ B$16,$D$16,$F$16,$H$16, $J$16,$B$18,$D$18,$F$18,$H$18,$J$18,$L$3 Gord Dibben Excel MVP On Wed, 23 Jun 2004 10:11:25 -0500, "Junior" wrote: 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" wrote in message ... 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 On 6/23/04 00:12, Junior did expound most eloquently: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
control advancing to cells
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 On 6/23/04 11:11, Junior did expound most eloquently: 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" wrote in message ... 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 On 6/23/04 00:12, Junior did expound most eloquently: 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. -- 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advancing the year | Excel Discussion (Misc queries) | |||
Advancing One row without enter | Excel Discussion (Misc queries) | |||
Advancing formula | Excel Worksheet Functions | |||
Advancing Timing | Excel Discussion (Misc queries) | |||
Advancing Down A List | Excel Worksheet Functions |