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: , , ,
Reactions: 

44 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
  17. Mueeid SoomroDecember 19, 2016 at 7:12 PM

    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. tim markApril 1, 2017 at 5:06 PM

    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. UnknownMay 17, 2017 at 8:56 AM

    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. Alex JohnJune 7, 2017 at 3:25 AM

    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. UnknownSeptember 14, 2017 at 7:19 PM

    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
  22. MiaNovember 2, 2017 at 10:18 PM

    Office printing procedure - Small amounts each time, Eight information sheets, one organization profile sheet and one envelope, 1000 envelopes are printed just once,VSL

    ReplyDelete
  23. tim markNovember 10, 2017 at 7:46 PM

    Acquiring your own machine to influence shading duplicates to can be expensive at first. While you can get singular duplicate machines, which arrive in a wide assortment of characteristics, you can likewise get multifunctional printer copiers which will likewise enable you to do similar things and the sky is the limit from there. cheapest color copies printing

    ReplyDelete
  24. RobertJanuary 14, 2018 at 3:26 PM


    I think this is a really good article. You make this information interesting and engaging. You give readers a lot to think about and I appreciate that kind of writing.
    Luxury printing

    ReplyDelete
  25. Elizabeth J. NealFebruary 16, 2018 at 3:50 PM

    I m no expert, but I believe you just made an excellent point. You certainly fully understand what youre speaking about, and I can truly get behind that.
    Vehicle Signs Shropshire

    ReplyDelete
  26. MatiasApril 15, 2018 at 7:59 PM

    To be positive that you have properly reversed the image, print a copy and put it in front of a mirror. This is the way it will look on your personalized t-shirt. gifts for companies

    ReplyDelete
  27. Jims lindaApril 15, 2018 at 11:58 PM

    First You got a great blog .I will be interested in more similar topics. i see you got really very useful topics, i will be always checking your blog thanks. prototyp

    ReplyDelete
  28. Digital Marketing AgencyJune 27, 2018 at 6:13 PM

    Everyone has a business card, but not every business card is memorable. Printing technology has evolved to the point that you can now order business cards with pop-ups, cutouts, translucent overlays, and many other features. A business card that doesn't stand out won't be remembered, so plan the design of your business card carefully before you order business card printing. Leeds printers

    ReplyDelete
  29. Digital Marketing AgencyJune 27, 2018 at 6:13 PM

    Business card printing is a classic marketing strategy that one would think would always be successful. But if you don't know the ins and outs of this promotional component, you may be wasting your time creating business cards. In this day and age, business card printing is much more than a way to swap contact information; it's a way to make a statement for your company. Printing Services Leeds

    ReplyDelete
  30. MatiasAugust 20, 2018 at 1:51 AM

    You have a genuine capacity for composing one of a kind substance. I like how you think and the way you speak to your perspectives in this article. I concur with your mindset. Much obliged to you for sharing. cheap every door direct mail® print (eddm®)

    ReplyDelete
  31. Seo LoverAugust 26, 2018 at 1:39 AM

    This blog through it words has given a key to proficiency.
    free reverse cell phone lookup

    ReplyDelete
  32. Mueeid KhanSeptember 1, 2018 at 2:31 PM

    Such matter may be a bit complicated, but know just exactly what kind of printing services you are getting for your money when you've acquired a printer.best cheap 3d printer

    ReplyDelete
  33. msmeerJuly 18, 2019 at 2:47 PM

    I really loved reading your blog. It was very well authored and easy to undertand. Unlike additional blogs I have read which are really not tht good. I also found your posts very interesting. In fact after reading, I had to go show it to my friend and he ejoyed it as well! Custom Stickers

    ReplyDelete
  34. JennetJuly 18, 2019 at 4:18 PM

    All things considered, since your present clients are spending less, the best activity is increment your customer base. This is a lot actually quite difficult. הדפסה על מוצרים

    ReplyDelete
  35. Jenny RoseAugust 17, 2019 at 10:12 PM

    i wanted to leave a little comment to support you and wish you a good continuation. wishing you the best of luck for all your blogging efforts.
    Custom Packaging Boxes

    ReplyDelete
  36. jiya77207@gmail.comAugust 28, 2019 at 1:39 AM

    Hi! Great work. I feel nice to be here reading your authentic work.



    Custom Sticker

    ReplyDelete
  37. SmithSeptember 4, 2019 at 11:05 PM

    This comment has been removed by the author.

    ReplyDelete
  38. ShawnSeptember 11, 2019 at 7:45 PM

    This comment has been removed by the author.

    ReplyDelete
  39. BrooklynSeptember 18, 2019 at 2:56 PM

    That is an especially wonderful powerful resource that you’re giving and you merely give it here, thanks. hp officejet pro 6970 setup

    ReplyDelete
  40. Yehana MccoyNovember 14, 2019 at 6:07 PM

    webroot antivirus activation code

    Renew Norton with Product Key

    activate my mcafee product key

    ReplyDelete
  41. asking your questionsNovember 25, 2019 at 5:30 PM

    healey1234@gmail.com

    Custom Boxes

    Great Information sharing ..
    I am very happy to read this
    article .. thanks for giving
    us go through info. Fantastic
    nice. I appreciate this post.
    and thank you for sharing
    an amazing article…and Thank
    you For Sharing
    .

    https://www.healeypackaging.co.uk

    ReplyDelete
  42. Stickers Zone June 12, 2020 at 10:21 PM

    What’s Happening i’m new to this, I stumbled upon this I’ve discovered It absolutely useful and it has helped me out loads. I’m hoping to contribute & aid other customers like its helped me. Good job.
    Printed Labels
    Sticker Printing Press

    Get Instant Printing Company

    ReplyDelete
  43. MudassirJuly 13, 2020 at 6:43 PM

    Good but i have problem for responsive Google+ comment on my website Soap boxes

    ReplyDelete

Subscribe to: Post Comments (Atom)