Visual Basic Macro to print hidden worksheet

[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

 

[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.

 

[Noel S Pamfree]

Absolutely wonderful!

Thank you so much.

Noel

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.

 

[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

 

[Norman Jones]

Hi Paul,

Noel, I think Norman had a typo, the last Application.ScreenUpdating =
False

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

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.