Disable unhide in Excel workbook 1

[tractorvix]

Hi All,

I hope someone will be able to help me.
I've got an excel workbook with 2 sheets (Assessment), the first sheet with questions, the second with the answers. I have set the workbook to open with the second sheet hidden and a customised toolbar to stop folks from being able to unhide the second sheet.
However,
I have discovered today that if someone already has Excel open, then tries to close the Assessment workbook using the application exit, they are prompted to save changes, if they select no, it reverts back to their original workbook, if at the second save prompt they select cancel the Assessment workbook is restored with a full toolbar, enabling them to unhide the second sheet. (Hope that makes sense!)
I've tried protecting the workbook, but that doesn't seem to make any difference.

Any ideas?

Vicky

 

[mikej336]

Could you progamatically do a save? That way they won't be prompted.

Uncle Mike

 

[misterstick]

if you're distributing the answers, a clever student will be able to find them (via formulae or VBA) if they're on one of the sheets somewhere, no matter what you do.

i suggest you include the answers only in a formula, then when the sheet is protected only the result of the formula will be visible no matter what they do.

sheet2![A1]
not: "This is the answer"
but: =if(sheet1!a1="This is the answer", 1, 0)

hope this helps,


mr s. <

 

[misterstick]

er, why are you distributing the answers?

if it's for dynamic scoring, what's to stop the students from using trial and error?

regards,


mr s. <

 

[tractorvix]

Thanks to you both for your responses
mikej336
I don't want the sheet to be saved as the results are emailed to a specific mailbox - but you may be onto something, maybe I could just disable the save.....
I'm not that clued up on excel VBA, if you could provide any guidance that would be fab

Mr S
The questions are multiple choice and relate to a simple in house questionnaire that to be honest I'd be surprised if they were that desperate to find the answers.
A string is created with 1 and 0, with 1 being a right answer and a 0 being an incorrect one, that is then emailed to a shared mailbox (sent mail not stored in their outbox)

Thanks again

Vicky

 

[SkipVought]

Hi,

Assessment sheet goes.

Answer sheet stays.

YOU bring them together at evaluation time.

Skip,

_[red][/red]

 

[combo]

If you protect workbook-structure, you are not allowed to unhide worksheet (and make some other changes).
You can make the sheet invisible (for unhide command, except of VBA environment): go to VBE, in the 'Project explorer' window find and select the worksheet, in 'Properties' window set 'Visible' property to xlSheetVeryHidden. Password protect the VBproject (Tools > <VBAProject> properties) to lock access to those settings.

One or both of above methods should lock worksheet for average level excel user.

combo

 

[tractorvix]

Hi Skip,

The assessment is automated via an access db that brings in the results email from a shared mailbox and then uploads the responses.
I suppose I could set up something that would just mail A, B, C or D based on their response and then I could upload into the system, but at the moment it's a very simple count function on the email body to see if they've passed or not and with over 1,000 colleagues to test, I'm reluctant to start rewriting it.

Combo, I've tried your solution and the same thing is still happening.

Thanks

Vicky

 

[combo]

My suggestions does not hide "unprotect" option, just blocks sheet's visibility or unprotecting.

combo

 

[firefytr]

Post your code please. And I would keep it all in Excel, personally.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[AnotherHiggins]

This method has been discussed on Tek-Tips before, but I can't find a link at the moment....

Hide all of the worksheets except one. Use xlVeryHidden as combo mentioned. Name the one visible sheet "Warning" and have it notify users that macros must be enabled.

If a user opens the workbook without enabling macros, (s)he will only see the warning sheet.

Create a Workbook_Open procedure that unhides the question sheet and hides the "warning" sheet (again, with xlVeryHidden). The answer sheet should still be VeryHidden.

Create a Workbook_BeforeClose procedure that hides all worksheets except "Warning". This ensures that the workbook is still locked down when the next user opens it.

Protect the VBproject with a password.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[tractorvix]

John that sounds just like what I need.

Thanks for your help

Vicky

 

[firefytr]

Then check out Ken Puls Knowledge Base entry here...

http://vbaexpress.com/kb/getarticle.php?kb_id=379

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci