How to jump over "Locked Cells" like in windows?

[wind54surfer]

Hi all,

I am new at Mac.Excel and I am using a form we used in Windows with the
capability of skip over cells that were locked.

This is a long form and every row is the same entries.
I try to describe it in short form here:

The form has 30 rows and 26 columns and we only fill 7 columns in each
row, say #s: 3,4,8,9,15,20,21
the rest are all formulas.

Is there a way to do it like in windows? Any workaround?

Thanks in advance for any help

Emilio

 

[Ken Johnson]

Hi wind54surfer,
Try this...

Ctrl-Click that worksheet's Sheet Tab.
Select "View Code" from the Popup contextual menu. This takes you to
the VBA Editor.
In the VBA editor's menu bar go View>Properties window.
In the left hand column of the Properties window look for and select
"EnableSelection".
In the right hand column on the same line click on the drop down arrow
and select "1 - xlUnlockedCells".
Press Option + F11 to get back to the worksheet.
If that sheet is protected then the user can only select the unlocked
cells.
Tab, Return and Arrow keys all skip the locked cells.

Hope this helps.

Ken Johnson

 

[wind54surfer]

You are the best.
Thanks a lot!

Emilio

 

[wind54surfer]

I can't make stick, every time I open the worksheet it reverts to "0 -
xINoRestrictions"

What am I missing?

Thanks again,
Emilio

 

[Ken Johnson]

Hi Emilio,
I see what you mean!
My iMac didn't save it but my PC does, so it seems to be a Mac problem.

My only solution is to set the worksheet's EnableSelection property to
xlUnlockedCells in a Workbook_Open event procedure, which frightens a
lot of people and does have the other disadvantage that Excel's
Security level has to be Medium and users have to remember to click on
"Enable Macros" when the workbook is opened.

After copying the code below...

1. Press Option + F11 to get into the VBA Editor
2. On the VBA Editor main menubar go View>Project Explorer
3. In the Project Explorer double click the ThisWorkbook Icon (has the
green X) to open up the ThisWorkbook code module, where you should see
the headings "(General)" on the left and "(Declarations)" on the right.
4. Paste the code into that code module
5. If your worksheet's name is not Sheet1 then edit the macro code so
that it will work on your worksheet.
6. Press Option + F11 to get back to the worksheet
7. Make sure that the sheet is protected then save.
8. Make sure Security is Medium by going (Tools>Macro>Security>Medium)

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

Hope this helps.

Sorry about the delay, I'm changing over to ADSL2 and will be without
web access at home for about a week, so I'm replying from work.

Ken Johnson

 

[wind54surfer]

Thanks again, but still does not work.
Did you get it to work in your iMac?

My sheet1 is called Template and this is what I entered:

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

I also tried with "Sheet1" just in case.

And also what do you mean about macros, in the Mac I do not see how to
adjust macro security
like in windows and what does it have to do with code?

Thanks again,
Emilio

 

[Ken Johnson]

Hi Emilio,
It did work on my old iMac (OS 9.2) and I was mixing up PC and Mac
steps a bit with the Security level.
On my iMac, macro security is not a problem. It's just a matter of
selecting or deselecting "Protect against macro viruses" (or something
like that) under the "General" tab of the "Preferences" dialog. With it
deselected, workbooks with macros open like any other workbook (usually
not recommended), and the Workbook_Open macro will run. With it
selected, if the workbook has one or more macros, the user opening it
has the option of selecting "Enable macros" or "Disable macros" on a
"Security warning" dialog (that's what its called on the PC).
You must make sure "Enable Macros" is chosen, otherwise the
Workbook_Open macro won't run and all cells will be selectable.
When opening your workbook, if you saw that dialog you may have
accidentally chosen "Disable macros". I sometimes do that myself.
If you didn't see that dialog then the macro should have run, and I
can't explain your problem.I can't see what could be causing the macro
to fail if that is the case.
You can determine whether or not the Workbook_Open macro is running by
just temporarily adding a MsgBox to it, eg....

Private Sub Workbook_Open()
Worksheets("Template").EnableSelection = xlUnlockedCells
MsgBox "Yes"
End Sub

If you don't receive the message when opening the workbook you need to
determine why the macro is not running.
If the message is received and locked cells are selectable then there
is something wrong with the main line of the code.

Hope you get this solved.

Ken Johnson