Hide Formula & auto forwad to certain cell

Posted by Phil M on January 07, 2001 2:04 PM

I am trying to make a test that would be on a CD... What I would like is two things if they are at all possible.
1. hide the formulas in the cells (or any cell), BUT allow the user to type an answer in other cells (they are scored with IF formulas)

2. After a user hits enter in a certain cell, it would automatically go to the next answer cell..
Can either or both of these be done??

Posted by Dave Hawley on January 07, 2001 9:21 PM

Hi Phil

Both can be done.

For hiding formulas, select all you formula cells and go to Format>Cells/Protection and check the "Hidden" box. You will need to apply sheet protection for this, so make sure you input cells are NOT Locked.

This may also help you with moving from cell to cell, as when cells are locked with sheet protection, using the Tab key will move you into Unlocked cells only.

Or you could set the sheet so that ONLY unlocked cells can be selected via any navigation with this code.

Private Sub Workbook_Open()
'Get the sheet Code Name from the Left under _
"Microsoft Excel Objects". If you cannot see this _
the push Ctrl+R
Sheet1.EnableSelection = xlUnlockedCells
End Sub

To place it in: Right click on the Sheet picture (top left next to "File") and select "View Code" paste the code over the top. Push Alt+Q to return to Excel.

This will fire each time your workbook opens.

If you need a more elaborate cell select code I could write you one with a simple Select Case statement placed in the Sheet_Change event.

Hope this helps
Dave

OzGrid Business Applications