Excel: Making text invisible inside a table being able to copy it to an external editor.

Question

0.00/5 (No votes)

See more:

Hello all,

I'm working in excel 2010 and with tables (Ctrl+T to create a table from a range).

I would like not to have a programming (vba) solution.

The table files background changes color (one is white the next one is blue, and start again...) and the row number is not fixed.

I don't want to put that column in a specific background colour, that column shoul be like all the other ones...

There is an autocalculated text that I need to paste it to a text editor.

I don't want the text to be seen into the excel sheet, but I want to be able to copy it from excel and paste it to a text editor (notepad).

If I change the column format to ;;; the text gets hidden and I still can see it if I put the cursor on that cell, but I can't copy it and paste it...

Any idea on how to get that?

As always thank you in advance... :thumbsup:

PS: just in case I've uploaded an image into imageshack in order to show you what I'm talking about ;)

link to the picture[^]

Here you can see the column in red, that is a result of some calculations and which I don't want to be seen in the excel, but should be available to copy and paste it outside. (like putting the forecolor equal than the background color). Rows can be added at any time.

Posted 30-May-12 23:25pm

Joan M

Updated 3-Jun-12 4:34am

v6

Add a Solution

Comments

db7uk 31-May-12 6:41am

silly question but could you not set the text colour to white or transparent?

Joan M 31-May-12 6:48am

fix the color to white won't help as file colors are alternating: one is white and the next one blue, the next one white... an putting the text to transparent... erm... I've never seen how to do it, could you enlighten me? (searching into the Internet now) Thank you!

Joan M 31-May-12 6:53am

Don't seem to be possible to make a text transparent... :(

db7uk 31-May-12 6:54am

well normal text I don't think you can but word art could be an option? This could be set to transparent.

Other solution (which I know you did not want) is to create a macro that changed the text color to the opposite of what is currently being set?

Joan M 31-May-12 7:04am

I don't think this could fit in my case, I'm putting an auto-calculated value into those cells, I don't know if I can use a wordart for that, but moreover it should be able to be created automatically if I add more rows to the table... which will be more complicated...
Thank you for your feedback!

Joan M 31-May-12 7:05am

oh... and the macro thing... I will send this excel sheet to different people by mail... I would like to avoid it in order to avoid all kind of problems... :(

db7uk 31-May-12 7:10am

mmmmm very tricky. I'll have a think. Sorry for not having a direct answer.

Joan M 31-May-12 8:58am

Don't worry! thank you for your time and help ;) :thumbsup:

Maciej Los 31-May-12 16:14pm

Is this question solved?

Joan M 31-May-12 16:50pm

Not at all... :(

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sunny_Kumar_ · Answer 1 · 2012-06-02T11:17:00

Solution 1

Hi Joan,

I've got a solution for you.
Just set the background color of that cell same as foreground color or vice versa. I mean if the background of your text cell is white, then change the foreground color also to white. I hope you know how to apply sheet protection if you don't want people to modify your settings, formatting, don't see formulas etc.

Hope it serves the purpose. For any clarification, please revert.

Happy Coding :)
Sunny_K

Posted 2-Jun-12 11:17am

Sunny_Kumar_

Comments

Joan M 3-Jun-12 6:10am

ok, and in an environment in which I work with a table that changes (alternates) the colour of each row and that I can have different number of rows, how should I get the font colour adapted to the background colour? without vba, of course...

Sunny_Kumar_ 3-Jun-12 6:22am

please share that worksheet so that we can get a clearer picture of the subject.

Joan M 3-Jun-12 8:03am

Updated the question, at the end you'll find more details and a link to a screenshot.

Sunny_Kumar_ 3-Jun-12 8:25am

Hi Joan,
In this case, you can use conditional formatting using "Format cells that contain- NoBlanks" with a color matching to any of your table row. I've done it for example and you can find this attachment here: http://www.mediafire.com/?gcwawpwwm7acbr5

Hope this helps.

Joan M 3-Jun-12 10:34am

You've changed the column colour... which is something I would like to avoid (this is not in my original requirements, I've updated it).