Excel: how do I set page3 to be locked until page 1, 2 are solved
 

I'm making a test for a project and I've got three pages.
I want the last page to be locked until the first two are solved.
So: 1 and 2 are free to use (pages of questions) and I want 3 (page of
answers) to be locked until 1 and 2 are filled out. (I figure I need te
coordinates of the answer fields and such, but I can't figure out how.)

Excel: how do I set page3 to be locked until page 1, 2 are solved
 

On Fri, 12 Aug 2005 10:07:02 -0700, "Fillius Furral" <Fillius
wrote:

I'm making a test for a project and I've got three pages.
I want the last page to be locked until the first two are solved.
So: 1 and 2 are free to use (pages of questions) and I want 3 (page of
answers) to be locked until 1 and 2 are filled out. (I figure I need te
coordinates of the answer fields and such, but I can't figure out how.)


I think the approach I'd adopt is as follows. It assumes you can test
the condition of a cell such that you know an answer has been given.
For instance the answer cells may all start out as being blank.

Name the third sheet "Answers", and in VBA set the Visible property to
xlSheetVeryHidden

Assuming you have say 4 answer cells dotted around each of sheets 1 &
2, say A1, C1, B3, C4, in say E1:E4 enter the formulae
=IF(A1<"",0,1)
=IF(C1<"",0,1)
=IF(B3<"",0,1)
=IF(c4<"",0,1)

Then in say F1 sum the values E1:E4 on sheet 1 and say E1:E4 on sheet
2. This means that if all the answers have been completed, the value
of F1 will be zero. Anything else will mean a total of at least 1.

Then in a macro enter the following procedure

Sub UnhideSheet3()
If Range("F1") = 0 Then Worksheets("answers").Visible = True
End Sub

Now put a button on sheet 1 and/or 2 with a caption of "Display
answers" and link it to the UnhideSheet3 macro.

When the user presses this button, the Answers sheet will only display
when all answers have been completed.

It's not foolproof of course, since a VBA savvy user would be able to
open the VBA window and display the sheet manually.

HTH





__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Excel: how do I set page3 to be locked until page 1, 2 are solved
 

There are probably many ways for this to be done, and this will not
necessarily be the best way.

If you want the answer sheet to be displayed only when all answers are
correct, then you would need to have something that would determine the
correct answers.

This is assuming the correct answers are on Sheet3 in A1 through A10.
This would also be assuming that there are only 10 questions on sheet 1.
You can adjust as necessary.

On sheet1 In column AA1 to AA10 as an example for validation of answers to
questions 1 though 10, you could use a formula such as:
=IF(A1=Sheet3!A1,1,"")
Copy formula down as necessary.

In AA11, use a SUM formula
=SUM(AA1:AA10)


Do the same for sheet2. You'll need to adjust the formula for these
answers. Well say the answers are on sheet3 in A11 through A20
The formula would be:
=IF(A1=Sheet3!A11,1,"")
Copy formula down as necessary.

In AA11, use a SUM formula
=SUM(AA1:AA10)


Once this is setup:
Press Alt + F11 to open the VB editor
Double-click on ThisWorkbook.
First dropdown, select Workbook.
Second dropdown, select SheetChange.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sheets("Sheet1").Range("AA11").Value +
Sheets("Sheet2").Range("AA11").Value = 13 Then Sheets("Sheet3").Visible =
True Else Sheets("sheet3").Visible = False
End Sub


HTH, Good Luck,
Paul





"Fillius Furral" <Fillius wrote in message
...
I'm making a test for a project and I've got three pages.
I want the last page to be locked until the first two are solved.
So: 1 and 2 are free to use (pages of questions) and I want 3 (page of
answers) to be locked until 1 and 2 are filled out. (I figure I need te
coordinates of the answer fields and such, but I can't figure out how.)

Excel: page3 to be locked until page 1, 2 are solved
 

My excuses if I was unclear, but I don't get what you explained.

I have a small table (4x4) on page 1. and a few open spaces on page 2 (A bit
hard to explain, because it is a bit random, anyway...) I want the third page
to be accessible, if that table on page 1 is filled in. It doesn't need to
make any sense, it just has to be filled. Then, on page two, if those spaces
are filled in as well, I want page three to be opened, to check the given
answers.

So.. Table filled in on page 1, spaces filled in on page 2. Page 3 opened.

Thanks anyway, and thanks in advance. :) *takin' the easy way out*

Excel: page3 to be locked until page 1, 2 are solved
 

This code will count the number of cells on Sheet1 Range("A1:D4") that
have entries in them.

MsgBox WorksheetFunction.CountA(Sheets("Sheet1").Range("A 1:D4"))

x =WorksheetFunction.CountA(Sheets("Sheet1").Range(" A1:D4"))
If x = 16 then
msgbox "Sheet1 complete"
End if

You can do the same for the cells on any sheet.

this is pseudo code. (use the recorder to get the syntax you are looking
for.
Than set up an If....Then such that
If Sheet1 & Sheet2 complete then
Sheet3 unprotect
end if

Put the code in the ThisWorkbook module and use a sheet_change event.
Than every time a cell is changed - the code will run.

--
steveB

Remove "AYN" from email to respond
"Fillius Furral" wrote in message
...
My excuses if I was unclear, but I don't get what you explained.

I have a small table (4x4) on page 1. and a few open spaces on page 2 (A
bit
hard to explain, because it is a bit random, anyway...) I want the third
page
to be accessible, if that table on page 1 is filled in. It doesn't need to
make any sense, it just has to be filled. Then, on page two, if those
spaces
are filled in as well, I want page three to be opened, to check the given
answers.

So.. Table filled in on page 1, spaces filled in on page 2. Page 3 opened.

Thanks anyway, and thanks in advance. :) *takin' the easy way out*

Excel: page3 to be locked until page 1, 2 are solved
 

On Fri, 12 Aug 2005 12:29:01 -0700, "Fillius Furral"
wrote:

My excuses if I was unclear, but I don't get what you explained.

I have a small table (4x4) on page 1. and a few open spaces on page 2 (A bit
hard to explain, because it is a bit random, anyway...) I want the third page
to be accessible, if that table on page 1 is filled in. It doesn't need to
make any sense, it just has to be filled. Then, on page two, if those spaces
are filled in as well, I want page three to be opened, to check the given
answers.

So.. Table filled in on page 1, spaces filled in on page 2. Page 3 opened.

Thanks anyway, and thanks in advance. :) *takin' the easy way out*

See the previous answers from PClive and myself.

The 16 cells in the table, and the spaces on page 2 merely need to be
evaluated in some test cells as explained previously. Then the macro
checks the value in the test cell(s) and unhides sheet 3 or not as the
case may be.

The macro could be run by hitting a button embedded in the Ws, or
alternatively you could have it run automatically in the sheet Change
event, every time a value is entered in a cell. Only when the last
cell has been completed would sheet 3 be unhidden.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________