Visual Basic Macro to print hidden worksheet

N

Noel S Pamfree

I have created a multi-choice test in Excel 2003 for students to use at the
end of a unit. To prevent cheating the worksheet with the results on is
hidden and password protected.

I would like to provide a button at the bottom of the test worksheet which
students can click when they finish. This will send a copy of the hidden
worksheet to the printer with their results but I am unable to get it to
work.

Any ideas would be appreciated.

Noel
 
N

Norman Jones

Hi Noel,

Try:
'===========>>
Sub aTester()
Dim SH As Worksheet

Set SH = ActiveWorkbook.Sheets("Sheet1") '<<== CHANGE

Application.ScreenUpdating = False
With SH
.Visible = True
.PrintOut
.Visible = xlSheetHidden
End With
Application.ScreenUpdating = False

End Sub

'<<===========

BTW, you may wish to change :

.Visible = xlSheetHidden
to

.Visible = xlSheetVeryHidden

This will prevent the sheet being accessesed from the menus.
 
N

Noel S Pamfree

Absolutely wonderful!

Thank you so much.

Noel


Norman Jones said:
Hi Noel,

Try:
'===========>>
Sub aTester()
Dim SH As Worksheet

Set SH = ActiveWorkbook.Sheets("Sheet1") '<<== CHANGE

Application.ScreenUpdating = False
With SH
.Visible = True
.PrintOut
.Visible = xlSheetHidden
End With
Application.ScreenUpdating = False

End Sub

'<<===========

BTW, you may wish to change :

.Visible = xlSheetHidden
to

.Visible = xlSheetVeryHidden

This will prevent the sheet being accessesed from the menus.
Click to expand...
 
P

Paul B

Noel, I think Norman had a typo, the last Application.ScreenUpdating = False
should be set to true, like so, Application.ScreenUpdating = True and be
aware that sheet protection is very weak in Excel, so some of your students
may know how to get the answers :)

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
N

Norman Jones

Hi Paul,
Noel, I think Norman had a typo, the last Application.ScreenUpdating =
False
Click to expand...

Quite correct, Thank you for spotting that.
be aware that sheet protection is very weak in Excel,
so some of your students may know how to get the answers :)
Click to expand...

Again true, which is why I gently suggested the use of xlVeryHidden.

Additionally, it might well advisable to consider a design change. For
example, the student response sheets might be stored and the unit answers,
together with marking and printing routines, maintained in a separate
workbook or addin.
 
Top