'hidden text' in micosoft excel

This one is Urgent, but not too difficult for you experts I don't think...

I am creating a template on Excel and I am a bit of a novice, so hopefully you guys will find this a really easy one!

I am having a nightmare trying to get some text to be 'hidden' ie. I want the text to show on the template but I do not want it to show on the print copy.  Obviously this is possible , and I know how to on microsoft word.  I have tried highlighting hidden text from Word and copy/pasting it to the required cell in Excel, but this doesn't seem to work either. I have been faffing around with this for some time now and have been told that you may be able to help!  I really hope so!

I tried the method of making a 'text box' which works fine for most things, however there is one box which has a formula applied to it and so I don't think that I can put it in a text box.

Other suggestions have been to change the offending text to white or to just delete it every time we use the template.  These would both work for text, but again would be no good for the cells with formulas applied to them. Anyway I can't expect the user to delete unwanted text everytime they print a new quote.

It sounds like a simple one, but I have tried every other avenue and you guys are my last hope.  I have never used this expert exchange thing and don't even know how to get my answer, if there is one!  I have no problems with attaching the template so that anyone can have a look at it for themselves. Just let me know!

kt
ktharrisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bulletheadCommented:
Bit of an odd one this:

You need to go to Format\Cells and in the Number tab select Custom.

In the the custom format box you need to type:

;;;

That should do what you want I think.
0
MoliereCommented:
The easiest way is to use a VBA macro. Go to the VBA Editor and insert the code below. If you want, you can add cells to the range or add a Hot Key combination such as Ctrl-P. Instead of printing normally, print using this macro:

   Sub Print_Visible_Items()
      Dim CellClr as long

      ' Turn off the screen redraw.
      Application.ScreenUpdating = False

      ' Blank out "invisible" cell A1
      with Range("A1").font
           CellClr=.color
           .color=RGB(255,255,255)
 
      ' Print.
           ActiveWorkbook.Printout
           .color=CellClr
      end with

      ' Turn back on the screen redraw.
      Application.ScreenUpdating = True
   End Sub
         

0
ktharrisAuthor Commented:
wow, I've got two replies already!!

bullethead- That kind of works, but the text is no longer visible on the template unless you physically select the cell.  I'll keep playing around with this as it seem very close!

Moliere- I have no knowledge of macros but you sound as though you know exactly what you are talking about, is there any chance you could translate into lay mans terms? Or is it just too complicated ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MoliereCommented:
To enter a macro, go to Tools|Macro|Visual Basic Editor.

You will see a pane in the upper left that says "Project - VBAProject. Underneath, you will see a cascading tree with the name VBAProject (yourWorkbook.xls).

Right-click that name and choose Insert|Module. A blank sheet will open in the right pane. Paste the code there.

Go back to Excel. Go to Tools|Macro|Macros...
You will see a listbox with an entry for Print_Visible_Items. Select that and choose Options...

In Options, assign a shortcut key in the appropriate box and press OK.

Now, you can run the macro by pressing ctrl and the hotkey you assigned.

----------------------

One last thing: in the code, I placed the 'hidden' cell at A1. Write back and tell me which cells you want to 'hide' and I will adjust the code for you.
0
Steve KnightIT ConsultancyCommented:
If that works for you, great.  Just check (especially if it is used on different OS's or printers as some printers will still print white on white text as Black.... Both of my Laser printers do with the default W2K drivers since upgrading to W2K from W98...

You could also combine both of the above tricks by setting the range numberformat to ";;;" in the macro, e.g.:

Change the bit:

with Range("A1").font
          CellClr=.color
          .color=RGB(255,255,255)

to

Range("A1").numberformat=";;;"

and then the bit


        .color=CellClr
     end with

to just

Range("A1").numberformat="General"

or whatever other format you fancy for the cell.

If there is more than one cell then name them: Highlight all the cells (use Control Click or Shift click as needed) and type a name for the range into the area above A1 then hit Enter.  Then use that name in place of A1 and it will hide the whole range.

You could also add code into the Before_Printout macro, e.g. this will hide A1:A10 then printpreview then put them back to General again...

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Application.EnableEvents = False
   Range("A1:A10").NumberFormat = ";;;"
   ActiveWorkbook.PrintPreview
   Range("A1:A10").NumberFormat = "General"
Application.EnableEvents = True
Cancel = True
End Sub

That needs entering into VBA editor, ALT F11.  Then double click ThisWorkbook in the Project Explorer (Control R if not open) and Paste in the above code... If there is already a blank procedure entred for you delete the SUB and END SUB lines it puts in.

hth

Steve
0
criCommented:
What is the purpose ?

a) For background info, general help: Insert|Comments
b) For cellwise input help: Data|Validation: InputMessage
c) Annotating _inside_ formulas =2+2+N("This text will be added as zero")
d) Hidden columns/rows
0
ktharrisAuthor Commented:
Thankyou Moliere, I have managed to follow your suggestion and it works!  But of course things are never that easy!  As 'dragonit' suggested, I need to apply this Macro to more than one cell (about 25 dotted all over.  I have managed to follow dragonit's suggestions as far as:

'...and type a name for the range into the area above A1 then hit Enter.  '

Once I have highlighted all of the cells that I want to be 'invisible', I'm not sure what you mean by 'the area above A1 '.  This seems to be the final stumbling block, whoopee!! nearly there!!  Hope you can translate that last bit then I think I'm done (fingers crossed!)

Hope to hear from you soon

kt

0
MoliereCommented:
kt,

To reference multiple cells, use the following format. To select non-consecutive ranges, use a comma. To select consecutive ranges use a colon.

Sub MultipleCells()
    Worksheets(1).Range("A1,C1,E1:E5") = 1
End Sub

I hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve KnightIT ConsultancyCommented:
ktharris,

You can do as Moliere suggests or what I mean is giving a range a name.   You can either do this by typing into the box that is above the A1 cell (normally contains just A1 say but you can type into it if you try...) or choose the cells then do Insert | name | define and give it a name then click Add.

hth!

Steve
0
ktharrisAuthor Commented:
Hi again,

I hope you don't mind me bugging you about this again...

This is the macro after I editied it, but it won't run.  Have I missed a coma or something?

Sub Print_Visible_Items()
     Dim CellClr As Long

     ' Turn off the screen redraw.
     Application.ScreenUpdating = False

     ' Blank out "invisible" cell ,A4:A80,B5:B73,C1:C3,D3
     With Range("A4:A80,B5:B73,C1:C3,D3").Font
          CellClr = .Color
          .Color = RGB(255, 255, 255)

     ' Print.
          ActiveWorkbook.PrintOut
          .Color = CellClr
     End With

     ' Turn back on the screen redraw.
     Application.ScreenUpdating = True
  End Sub


-----------------------------------------------

I'm so close I can almost smell it!!  How do I do this points thing?  I think you deserve them the most for all your efforts.  Also, where are you? apparently it's 7.20 in the morning, never too early for a bit of expert-exchange Eh!

kt


0
MoliereCommented:
The macro is working for me. Where is it bombing out for you? Two notes, you may want to change ActiveWorkbook.PrintOut to ActiveSheet.PrintOut if you want just the sheet to print. Also, you may want to specify Worksheets("Desired Sheet").Activate so you do not inadvertantly affect the wrong sheet.


I am on the East Coast (NYC), so it is already 11:15 am. I guess you are elsewhere.
0
ktharrisAuthor Commented:
Don't know.  I have managed to highlight all of the required cells and name them as a group and then used this group name in the macro formula (replacing 'A1' of the original).  It works when I do a trial of just one or two cells but when I tried to do the lot it said:

Run time error'94:
Invalid use of Null

what's a null, Oh no, another can of worms!
0
MoliereCommented:
My guess is that some of the cells in your range have different colors. Select the named range and change the font color for al of them to black. The macro should run without problems then.
0
ktharrisAuthor Commented:
You were right, I saved the cells I wanted invisible as red, and a couple were just a slightly different shade!  Thankyou so much for all of your help.

I'm going to put a couple of quid in the homeless charity box outside work as a thankyou, unless you have a charitable preference?

thanks agin for all of your help, I have already reccommended this site to a few friends, it has been so useful.

kt
0
MoliereCommented:
kt,
To finish this question, select one of the proposed answers as an accepted answer. This closes out the question.

I am glad I could help.
0
geofflilleyCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
split points between moliere and dragon-it
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Geoff Lilley
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.