Visual Basic Macro to print hidden worksheet

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 


0
10/12/2005 10:41:20 AM
excel 39879 articles. 2 followers. Follow

4 Replies
377 Views

Similar Articles

[PageSpeed] 4

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.

 ---
Regards,
Norman



"Noel S Pamfree" <Noel.spamfree@hotmail.com> wrote in message 
news:eiS3HmxzFHA.3756@tk2msftngp13.phx.gbl...
>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
> 


0
normanjones (1047)
10/12/2005 11:47:55 AM
Absolutely wonderful!

Thank you so much.

Noel


"Norman Jones" <normanjones@whereforartthou.com> wrote in message 
news:uextILyzFHA.2212@TK2MSFTNGP15.phx.gbl...
> 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.
>
> ---
> Regards,
> Norman
>
>
>
> "Noel S Pamfree" <Noel.spamfree@hotmail.com> wrote in message 
> news:eiS3HmxzFHA.3756@tk2msftngp13.phx.gbl...
>>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
>>
>
> 


0
10/12/2005 3:10:04 PM
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

"Noel S Pamfree" <Noel.spamfree@hotmail.com> wrote in message
news:OnVGL8zzFHA.3896@TK2MSFTNGP10.phx.gbl...
> Absolutely wonderful!
>
> Thank you so much.
>
> Noel
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:uextILyzFHA.2212@TK2MSFTNGP15.phx.gbl...
> > 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.
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Noel S Pamfree" <Noel.spamfree@hotmail.com> wrote in message
> > news:eiS3HmxzFHA.3756@tk2msftngp13.phx.gbl...
> >>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
> >>
> >
> >
>
>


0
10/12/2005 10:00:17 PM
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.

---
Regards,
Norman



"Paul B" <to_much_spam_to_list@nospam.com> wrote in message 
news:uSfL5h3zFHA.2312@TK2MSFTNGP14.phx.gbl...
> 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
>
> "Noel S Pamfree" <Noel.spamfree@hotmail.com> wrote in message
> news:OnVGL8zzFHA.3896@TK2MSFTNGP10.phx.gbl...
>> Absolutely wonderful!
>>
>> Thank you so much.
>>
>> Noel
>>
>>
>> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
>> news:uextILyzFHA.2212@TK2MSFTNGP15.phx.gbl...
>> > 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.
>> >
>> > ---
>> > Regards,
>> > Norman
>> >
>> >
>> >
>> > "Noel S Pamfree" <Noel.spamfree@hotmail.com> wrote in message
>> > news:eiS3HmxzFHA.3756@tk2msftngp13.phx.gbl...
>> >>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
>> >>
>> >
>> >
>>
>>
>
> 


0
normanjones (1047)
10/13/2005 12:59:31 AM
Reply:

Similar Artilces:

How do you print selected labels in Word?
When you create a page of labels in Word, I need to select a handful of the labels and print only those. When you select & go to print, the "selected" button is greyed out and will not let you select it. Is there any way around printing selected text in a label or table in Word? I know that you can go into the document and select which individual label you want to print but this would take too long if I had 6 or so labels off of the page to print. We used to be able to do it in Corel Word Perfect but are now working in Word! Thanks! See http://www.gmayor.com/conv...

Print a MS Word Doc from Excel workbook
Hi, I have a workbook in Excel where I want to be able to place a button that will print a letter that has been created in a seperate MS Word documnet. I have tried recording a macro but nothing happened. Is there a way to link the two together to enable this 'button' to produce the letter ?? Thanks Anthony See your previous post. Anthony wrote: > > Hi, > I have a workbook in Excel where I want to be able to > place a button that will print a letter that has been > created in a seperate MS Word documnet. > I have tried recording a macro but nothing happened....

How to write a simple copy-paste macro?
I've used 123 for over 20 years - simple, easy to learn and use, but my XP OS doesn't handle it well, so I'm trying to move to Excel. But in Excel I can't even write a simple macro such as: Copy from a cell, move down one space, Paste. It will do this once, but then not continue. If I run it from another cell - just restarts from the original cell. Without further comment, I learned this macro in 2 minutes on 123. I've been working with Excel for a week now, and I'd appreciate some help. To recap, I simply want to be able to choose a cell having data, run a C...

macro help #17
Hi All, 3*2 3*3 3*2 3*4 3*4 3*4 3*2 3*2 3*5 3*5 3*4 3*4 3*4 3*4 3*5 3*5 3*5 3*5 3*4 3*4 3*4 3*4 3*4 3*4 3*2 3*2 3*3 3*3 I have following kind of data i want to convert the above column into following , how can i do that =3*2 =3*3 =3*2 =3*4 =3*4 =3*4 =3*2 =3*2 =3*5 =3*5 =3*4 =3*4 =3*4 =3*4 =3*5 =3*5 =3*5 =3*5 =3*4 =3*4 =3*4 =3*4 =3*4 =3*4 =3*2 =3*2 =3*3 =3*3 Can i do it using the help of macro ? Regards, Raan Sub multiply() For Each cell In Selection cell.Value = "=" & cell.Value Next cell End Sub Gord Dibben MS Excel MVP On Sat, 17 Nov 2007 08:25:09 -0800 (PST), ...

Macro problem for timesheet
Currently, I am doing a timesheet. The timesheet are given every month. For December, a file December.xls with sheet name - December as well is given to them. Then, the staff are required to rename the file same as their staff number. So, for staff no. P101 and in December, the sheet name is December where else the file name is P101.xls. And, so on. Then, I have to manually renamed all the sheet name same as the staff's' numbers. This for the purpose where the formula that need to refer to data entered by the particular staff, I can just easily refer by the sheet name. Th...

set macro security to low programmatically
Dear all, Is it possible to set macro security to low programmatically?Thanks.Chlaris On Tue, 29 Dec 2009 11:32:10 +0700, "Chlaris" <chlarrissa_nospam@yahoo.com> wrote: There are a few registry keys you can set BEFORE you run your app, for example by your setup program. See below for details. IMHO it would be much better to digitally sign your app, so this is not needed. Root: Local Machine Key: Local Machine Software\Microsoft\Office\11.0\Access\Security Name: level Value #00000001 Root: Local Machine Key: Local Machine SOFTWARE\Microsoft\Jet\4.0...

Printing Items
I have a program that can hold up to 1000 items, basically 1000 boxes with information inside them. The items are contained in a struct, where all the characteristics are. I have created a Dialog Box for choosing which items to print out, whether it be one item or a thousand. When it comes to printing, however, I am lost. When printing multiple items, I wish to have 5 items per row, and 5 rows per page. How do I basically automate this process? I haven't ever worked with complex printing, so I have been having a lot of trouble trying to figure this out. Thank you, Donovan J. Hirsc...

Outlook 2002: Print Range?
I just switched from Outlook Express 6 to Outlook 2002. In Outlook Express 6, I could highlight text in an email, then on the print dialogue box I could select "print range" "selection" to print only the selected text. I can't seem to find this option in Outlook. Am I missing this or is it just not an option? Thanks, George You would need to copy it to another program such as notepad, wordpad, or word to do this. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual accou...

Printing Only Rows With Values In Them Options
In Workbook2 I=92ve got formulas in all cells from A1 to J100. The formulas =91pull in=92 values from the corresponding cells in Workbook1. Not all rows in Workbook1 always have values in them, ie the values may stop at row 20, 30, 45, whatever (but there won=92t be any gaps). I need to print Workbook2 sometimes and I only want to print the rows where there are values, not the whole 100 rows with formulas in them, but if I click =91Print=92 I always get the whole 100 rows printed, whether there are values in them or not. In the 100 rows I=92ve got conditional formatting thus: If cel...

How do I print non-consecutive pages on the same sheet in a Publi.
Developed a 4 page flyer, want to print it front & back on 1 sheet that I can fold like a card. Cannot print pages 1 & 4 together, only consecutive pages. Sound slike what you need to do is set it up as a booklet. Under page setup choose booklet, landscale. the default is what you probably want. Then when you print, you can choose just page 1, but it will print the 1/4 page. Using booklet, it will work. Jo "Pampered Paws Gourmet" wrote: > Developed a 4 page flyer, want to print it front & back on 1 sheet that I can > fold like a card. Cannot print pages 1 ...

Deleting Hidden Rows
I have a worksheet that was emailed to me. Unfortunately, the originator of the worksheet hid rows instead of deleting them. I need to get rid of the hidden rows. How do i go about doing that. "Raquel" <anonymous@discussions.microsoft.com> wrote in message news:83746118-C926-4313-83EE-29EF31A7B9EA@microsoft.com... > I have a worksheet that was emailed to me. Unfortunately, the originator of the worksheet hid rows instead of deleting them. I need to get rid of the hidden rows. How do i go about doing that. No need to repost so quickly. See recent answer. ...

A macro question
Hello, I have a macro that when prompted opens up many different files that are located in the same folder. Each of these files that I am opening are files that have been previously summarized through the use of a macro. Example: I wll have 40 to 60 files that I save in the same folder on a daily basis. At the end of the day I use a template and a macro that will open all of the daily files and summarize them into one neat sheet for me. When this macro is executed all of the 40 to 60 daily files are opened to give me the total of my daily summary sheet. I save each of these daily summari...

underline in cell prints on word below
Spreadsheet looks fine on screen, but underlined cells print on top of cell below when printed. ...

Printing of Borders 11-30-09
I have a phenomenon I have not seen before, despite using Excel for over 15 years. Can anyone please point to a cause? Excel 2000 on a Windows XP SP3 platform. On a particular worksheet the Borders are printing on copy one but not copy two. The Borders are delineating sub-totals and totals of columns of figures. Not all worksheets are affected. TIA -- Gerry ~~~~ FCA Stourport, England Enquire, plan and execute ~~~~~~~~~~~~~~~~~~~ ...

How do I print a Publisher document without the margin edge?
I'm working on a Publisher document that has a border around the edges. I would like to print the document so that the border goes all the way to the edge of the paper but I get a 1/4" white margin on the page even though I have set the margins to 0" and the print preview does not show the margin. This is a limitation of your printer, not Publisher. If your printer cannot print full bleed, there's nothing you can do. -- JoAnn Paules MVP Microsoft [Publisher] "Greer" <Greer@discussions.microsoft.com> wrote in message news:1378F597-0DBA-4956-A5F1...

Enable/Disable Macros question
Hi I have a workbook that has a few macros in it which need to be enabled. How can i close the workbook if someone clicks 'disable macros'.? I would like to place the 'check' in the workbook open module if possible. Thanks Paul On Fri, 13 Feb 2004 18:19:05 -0000, "Paul Watkins" <paul.watkins4@ntlworld.com> wrote: >Hi > >I have a workbook that has a few macros in it which need to be enabled. >How can i close the workbook if someone clicks 'disable macros'.? > >I would like to place the 'check' in the workbook open modu...

Why do I have to save before printing in Publisher 2003?
When printing in Publisher 2003 I have to save all of my documents before printing. How can I fix this problem? summer <summer@discussions.microsoft.com> was very recently heard to utter: > When printing in Publisher 2003 I have to save all of my documents > before printing. I can print documents without saving. However, it is more advisable to save before printing, in case a system crash occurs (Publisher can take quite a while to print). Are you converting to Adobe PDF? Their software requires you to save first for reasons never explained to me (something about the way t...

Save sheet copy with cell value name using a macro.
Good afternoon All, I need a VB statement to save a copy of the sheet i'm using (same were the macro is executed) with a cell value (EG cell "B2") of the workbook I'm using into a predefined path (EG "C:\"). If anybody can help me with this I'll be very glad. Leo. You want to save a sheet as a workbook with a name of the value in Range("B2") of the sheet? Sub Make_New_Book() Dim w As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False ActiveSheet.Copy ActiveWorkbook.SaveAs Filename:="C:\...

Printing BW/Color problem (RLi)
I have an access 2003 application and a HP laser color printer. When i print a report from this application there might be color on the page (photo) But I want the printout allways to print in black&white. So i went tot the windowsXP config panel and gave the default BW to the properties off my color laserprinter. But it does not work: when i print the access-report access overrules the default back to color and my page is printed with color. In order to get a black&white printout i have rechange the properties back to black&white for every print i make. The page setup for a re...

Would like better understanding of how this macro works.
I found most of the macro online and made some changes, but I'm not sure of all of the functions being done. The macro lets you select a group of graphic files and then inserts them into a Word table with one graphic per row. I would appreciate comments on what the different parts of the macro do. Sub AddPix() Dim fd As FileDialog ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1, NumColumns:= _ 2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _ wdAutoFitContent With Selection.Tables(1) .Columns.Pre...

printing on wide paper
I still run 123 spreadsheets that require wide, pinfeed 11x17" paper. I would like to convert these to Excel but cannot find a selection for paper size that corresponds to 11x17. Any suggestions would be appreciated. Thanks in advance. You don't say what version of Excel you use, but on the Excel that comes with Office XP, I can click on File Page Setup Page Paper Size and 11 x 17 is one of the options. Hope this helps >-----Original Message----- >I still run 123 spreadsheets that require wide, pinfeed >11x17" paper. I would like to convert these to Excel but ...

Excel 2007 Countifs macro with multiple criteria (OR)
Been working on this reporting macro for excel 2007. Cant figure out how to make countifs work for many possible values in one cell. I have report with multiple colums. With no problem I can check if call has been on hold for less than 26 seconds etc. But in the same countifs sentence I should check also if colum H cell has one of correct names from 50 possible names. There is something like 50 names and about 30 names should be counted and the other 20 not. So I think I need somekind of a OR sentence inside criteria (also saw one possible solution that there would be possib...

Printing Calenders
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I am trying to print a calendar to fit into my own MULBERRY Filofax style book, the sizes etc are all OK but none of the options seem to be working, e.g. if i select print 6 Holes, NO HOLES are printed. <br> Anyone solve this or have the same issue? <br> James On 1/11/10 10:25 AM, dseven@officeformac.com wrote: > I am trying to print a calendar to fit into my own MULBERRY Filofax > style book, the sizes etc are all OK but none of the options seem to > be work...

problem to print
l had received one cd from my department. when l tried to print l got a problem.there was two page in one sheet. that mean 50% one page. l want one page for one sheet but l do not know how to do it. could anyone understand me and please help me hi, Sounds like your print area may not be set correctly. on the menu bar...file>print area>clear print area Then high light the area you wish to print as one page. once high lighted...on the menu bar...file>print area>set print area post back if this does not work. Regards FSt1 "zilah" wrote: > l had received ...

outlook locks up when trying to print an email
Occasionally MS Outlook 2002 will lock up when I try to print an email. The only way to correct for this is to hard boot my computer. Has anyone ever encountered this? ...