witzph1
asked on
Excel field shading that doesn't print?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
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
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
ASKER
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?
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?
I just reread your question and I need to ask, is this a userform we're talking about?
ASKER
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?
Unfortunately yes since my "Solutions" were for userforms. Sorry.
ASKER
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.
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
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
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
.... Thinkpads_User
ASKER
I'm not sure what you mean by highlighting an input area without shading. How does that work?
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
.... Thinkpads_User
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
.... Thinkpads_User
Can you attach your workbook?
@witzph1 - I hope your form worked out all right for you. Thanks, .... Thinkpads_User
ASKER