?
Solved

'hidden text' in micosoft excel

Posted on 2003-03-10
18
Medium Priority
?
367 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:ktharris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +3
18 Comments
 
LVL 5

Expert Comment

by:bullethead
ID: 8103701
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
 
LVL 3

Expert Comment

by:Moliere
ID: 8104319
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
 

Author Comment

by:ktharris
ID: 8104669
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 3

Expert Comment

by:Moliere
ID: 8104811
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
 
LVL 43

Expert Comment

by:Steve Knight
ID: 8105256
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
 
LVL 13

Expert Comment

by:cri
ID: 8105902
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
 

Author Comment

by:ktharris
ID: 8118495
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
 
LVL 3

Accepted Solution

by:
Moliere earned 300 total points
ID: 8120008
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
 
LVL 43

Expert Comment

by:Steve Knight
ID: 8120344
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
 

Author Comment

by:ktharris
ID: 8120351
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
 
LVL 3

Expert Comment

by:Moliere
ID: 8120506
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
 

Author Comment

by:ktharris
ID: 8120669
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
 
LVL 3

Expert Comment

by:Moliere
ID: 8120896
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
 

Author Comment

by:ktharris
ID: 8121348
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
 
LVL 3

Expert Comment

by:Moliere
ID: 8122462
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
 
LVL 1

Expert Comment

by:geofflilley
ID: 10032538
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

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
New style of hardware planning for Microsoft Exchange server.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question