Solved

Excel field shading that doesn't print?

Posted on 2013-01-17
17
1,180 Views
Last Modified: 2013-01-25
I've created a fillable form using Excel 2010.  I've protected the form for filling.  What I would like to do is shade the fields that a user can fill, but not have that shading print.  Unfortunately, there ARE fields that have shading that should print, so I can't turn off all printing of field shading.  Is there a way to shade a field and not have that shading print?  Or is there another way to highlight fillable fields?
0
Comment
Question by:witzph1
[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
  • 6
  • 5
17 Comments
 
LVL 95

Accepted Solution

by:
John Hurst earned 500 total points
ID: 38788992
Since you want some shaded fields printed, then the way I approach this is to have very light shading where I do not need it printed and darker shading where I want it printed. This normally works really well as light shading tends not to show up at all.

You could also use a very light colour, but generally I find the lightest shading works best.

I do not know of any "non-printing" shading - just light shades and colours.

... Thinkads_User
0
 

Author Comment

by:witzph1
ID: 38789004
You are probably right about that.  I'll wait to see if anyone else weighs in on this before I close it out.  But that sounds like a reasonable work-around if what I want isn't possible.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38789025
Dim ctl As Control

For Each ctl In Controls
    if ctl.BackColor = 'specify your shading color
        ctl.Visible = False
    End If
Next
' Do printing

For Each ctl In Controls
    ctl.Visible = True
Next
0
Don't Miss ATEN at InfoComm 2017!

Visit booth #2167 to see the  new ATEN VM3200 32 x 32 Modular Matrix Switch. Other highlights include the VE8950 4K HDMI Over IP Extender, VS1912 12-Port DP Video Wall Media Player  and VK2100 ATEN Control System. Register now with Free Pass Code ATEN288!

 
LVL 47

Expert Comment

by:Martin Liss
ID: 38789044
Or

Dim ctl As Control

For Each ctl In Controls
    if ctl.BackColor = 'specify your shading color
        ctl.BackColor = ' specify normal color
ctl.Tag = "Fill me"
    End If
Next
' Do printing

For Each ctl In Controls
    If ctl.Tag = "Fill me" Then
        ctl.Backcolor = ' specify shading color
    End If
Next
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38789049
Or set the Tag property of the shaded controls in the IDE and then

Dim ctl As Control

For Each ctl In Controls
    if ctl.Tag = "Fill me"
        ctl.BackColor = ' specify normal color
    End If
Next
' Do printing

For Each ctl In Controls
    If ctl.Tag = "Fill me" Then
        ctl.Backcolor = ' specify shading color
    End If
Next
0
 

Author Comment

by:witzph1
ID: 38789094
MartinLiss, I think you are on the right track for what I need!  

I'm a little rusty of VB, so just to clarify, depending on which of your options I choose, would I enter the code in the VBAProject | ThisWorkbook sheet of VB Editor?

And which of your three options do you think would work best?  

Also, how do I specify a color shade? By RBG number?
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38789105
I just reread your question and I need to ask, is this a userform we're talking about?
0
 

Author Comment

by:witzph1
ID: 38789141
It's an Excel 2010 template (xltx) file.  The template is an employee appraisal form.  It is intended to filled out by managers when getting ready for an employee review.  There are quite a few fields that are not editable by the manager once I protect the document for forms.  To help guide their eye (even though they could simply press tab to go to the next fillable field) I would like the fillable fields to be shaded, but not have the shading print.  Make sense?
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38789149
Unfortunately yes since my "Solutions" were for userforms. Sorry.
0
 

Author Comment

by:witzph1
ID: 38789447
Thinkads_User, my printer must be too good.  No matter how light I get the shading it prints.  And I've got it so light I can barely see it on-screen, which is where I want to see it plainly.
0
 
LVL 95

Expert Comment

by:John Hurst
ID: 38789473
I am not sure what else to suggest here especially given the other expert input.

  What about putting a coloured border around fields to fill but zero shading. Then use shading where you need and can allow it to print?
.... Thinkpads_User
0
 
LVL 95

Expert Comment

by:John Hurst
ID: 38789531
I thought some more about this. I am thinking that if you did some form redesign, you probably could highlight the input area without using shading. Let us know what you think.
.... Thinkpads_User
0
 

Author Comment

by:witzph1
ID: 38789555
I'm not sure what you mean by highlighting an input area without shading.  How does that work?
0
 
LVL 95

Expert Comment

by:John Hurst
ID: 38789570
What I mean't was a coloured border so that it would be clear where the input fields were. If all the inputs were in the same area, a coloured border could be used effectively.
.... Thinkpads_User
0
 
LVL 95

Expert Comment

by:John Hurst
ID: 38789602
I should note that I see border highlighting in some government forms where shading is not used. The borders are thick black and not coloured.

.... Thinkpads_User
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38789617
Can you attach your workbook?
0
 
LVL 95

Expert Comment

by:John Hurst
ID: 38819553
@witzph1 - I hope your form worked out all right for you. Thanks, .... Thinkpads_User
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

729 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