Friday, November 13, 2009

Excel Macro : Hide Cell Content from Printing

Some times we have some information in worksheet which is we need to input but do not want that information to be printed. With the following macro code you can achieve the desired results

You will need to copy this code to workbook module of your workbook


Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Application.EnableEvents = False
    Cancel = True
    A = Range("A1").NumberFormat
    Range("A1").NumberFormat = ";;;"
    
    Application.Dialogs(xlDialogPrint).Show
     
    Range("A1").NumberFormat = A
    Application.EnableEvents = True

End Sub


As per above code the contents of Range A1 will not be printed, you will need to change this reference to your cell.

Download file with the code to hide cell contents before printing

.
Posted by Yogesh Gupta at 8:08 PM
Labels: , , ,

17 comments:

  1. AnonymousDecember 8, 2009 at 9:18 AM

    Doesn't exactly work for me. I'm trying to hide a range of cells in a sheet (e.g. B7-D9). I tried the code:

    Range("B7:D9").NumberFormat = ";;;"

    But that didn't work.

    ReplyDelete
  2. Yogesh GuptaDecember 8, 2009 at 12:45 PM

    Hello Chris

    I hope that you have enabled macros. Macros needs to be enabled to have this working.

    If you have macros enabled, make sure that it has been added to workbook module of your file.

    Also you will need to change the reference range at 3 places but this should not impact hiding contents. I suggest that you download file available at download link of the post and then change the refernce range to see how this works.
    Again make sure that you have enabled macros.

    Thanks
    Yogesh Gupta

    ReplyDelete
  3. shankarJuly 23, 2010 at 6:17 PM

    myself Ravishankar in our office we get a report from oracle based software like on sorted and its taking hell out my time pls help me out to do the same if you can suggest me

    ReplyDelete
  4. Yogesh GuptaJuly 24, 2010 at 9:08 AM

    Hi Ravi

    I am not clear on what do you want. Can you be more specific about your question.

    Regards//Yogesh Gupta

    ReplyDelete
  5. shankarJuly 24, 2010 at 12:59 PM

    Hi Yogesh

    actually we using oracle software and we getting daily reports which in un sorted condition with extra lines which is taking too much time for me to sort and analyzing the report

    so can you help in this...

    ReplyDelete
  6. Yogesh GuptaJuly 26, 2010 at 8:53 AM

    Hi Ravi

    I can suggest some thing if I know the exact details of problem along with the resolution you want for it. What ever you have said is very generic and can not be commented upon for soultion

    Regards//Yogesh Gupta

    ReplyDelete
  7. shankarJuly 29, 2010 at 6:20 PM

    Yogesh one of my office mate locked excel file and he left job we keep trying to contact him and not responsing for anything, Pls not the spread sheet total file locked, means when we try to open the file Excel prompting the password

    Pls help since this contain my full year finance data

    ReplyDelete
  8. Yogesh GuptaJuly 29, 2010 at 9:24 PM

    Hi Ravi

    I will not be able to help you with problem relating to password protection of the file.

    Regards//Yogesh Gupta

    ReplyDelete
  9. kkku11January 31, 2012 at 8:53 AM

    How to hide multiple cells from printing? eg F$, E19, G54 etc

    ReplyDelete
  10. MarianneSeptember 14, 2012 at 2:22 AM

    exactly what I needed. Just wish I could make it work in the macro-enabled worksheet. when i print preview, it still shows the content of the cell i am trying to hide. i wonder since this is module 2, if module 1 needs to run first? Module 1 is making lower case letters to caps automatically when clicking an assigned radio button. I am not a coder so I am only guessing that "private" means that this macro won't show up in the list to assign to a button. rather it will run automatically. No?

    ReplyDelete
  11. Yogesh GuptaSeptember 15, 2012 at 5:10 PM

    Hi Marianne

    You need to copy the module above to workbook module. Kindly see link in this post above how to copy this code to workbook module.

    Thanks

    ReplyDelete
    Replies
    1. MarianneSeptember 15, 2012 at 6:49 PM

      i almost got it I feel. Thanks for prompting me to read that again. I don't know why a Module2 kept opening, but now I was able to add the code to ThisWorkbook. I've changed A1 to D22 in all 3 places. When I print preview, the contents of D22 still show. Maybe it won't show when I actually print it, but I don't want to waste paper and ink. Plus, I would think print preview would show exactly what the worksheet would look like when printed. Again, the content of that cell is still showing. I know I'm close. I wish I could show you screenshots to show that I did copy your code right and how the print preview still shows the cell contents. If I change the cell format to ";;;" then I can't see the contents while working on the worksheet. I believe your code DOES allow one to see the cell value but when printing it, the cell is blank. What I have is a time sheet on a windows tablet. I want to be able to have a cell that shows how much I make in a month, but i don't want to have to change the cell format every time I want to see the contents of cell D22. I have a formula in cell D22. But when I print it to turn into my supervisor, I don't want the amount to show (even though the supervisor will figure the amount anyway). It is just for my information. I searched and found your code. Exactly what I needed. You posted this code way back in 2010, so I appreciate your reply to my post.

      Delete
  12. UnknownMay 13, 2014 at 8:04 AM

    Does this code work in tables in Microsoft Word?

    ReplyDelete
  13. BUMBADecember 14, 2015 at 4:49 PM

    This comment has been removed by the author.

    ReplyDelete
  14. BUMBADecember 14, 2015 at 4:50 PM

    On the "Number" tab, select Category = "Custom" and for "Type" enter:

    "";"";"";""

    ReplyDelete
  15. BUMBADecember 14, 2015 at 4:52 PM

    On the "Number" tab, select Category = "Custom" and for "Type" enter:

    "";"";"";""

    ReplyDelete
  16. BUMBADecember 14, 2015 at 4:52 PM

    n the "Number" tab, select Category = "Custom" and for "Type" enter:

    "";"";"";""

    ReplyDelete

Subscribe to: Post Comments (Atom)