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

.

22 comments:

  1. 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. 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. 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. Hi Ravi

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

    Regards//Yogesh Gupta

    ReplyDelete
  5. 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. 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. 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. Hi Ravi

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

    Regards//Yogesh Gupta

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

    ReplyDelete
  10. 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. 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. 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. Does this code work in tables in Microsoft Word?

    ReplyDelete
  13. This comment has been removed by the author.

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

    "";"";"";""

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

    "";"";"";""

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

    "";"";"";""

    ReplyDelete
  17. Digital printing in bright attractive colors, prominent formats and stylized way of presentation entice the customers and are enthralling for the clients as well. parkplaceprinting

    ReplyDelete
  18. A rumored flyer printing Delhi administrations attempts to keep up first class guidelines with a specific end goal to make your promoting efforts viable.economic brochures

    ReplyDelete
  19. Is there a way to trigger an auto email to go out once a certain amount hits a spreadsheet? For example, I'm tracking several items on inventory. Once they hit a specific number, the word "order" automatically populates. I'd like an email to automatically go to the person who would then place the order. Is that possible? Thank you! - deadmen

    ReplyDelete
  20. Whatever be the message the client wants to put up to the customers, the printing industry takes care to handle it in the exact way they want it.Cheap Online Printing

    ReplyDelete
  21. This is additionally a decent post which I truly delighted in perusing. It is only one out of every odd day that I have the likelihood to see something like this..
    Cheapest Printing Services Near Me

    ReplyDelete