How to make Excel 2010 checkbox checks darker when printed?

calbais
calbais used Ask the Experts™
on
When I print my worksheet, which has Excel 2010 form checkboxes, the checks are very small and difficult to see. Is there a way to make them bold or darker? Or is there a way to switch them to a Wingding, etc. when printed but still allow users to check the checkbox on screen?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

I don't think there is a way to change the size of the actual check box.

But there are two different ways of displaying a Forms control checkbox: 3-D or flat.  

Right-click the check box and select Format Control. On the Control tab uncheck the 3-D shading tick box.
With 3-D shading, the box will be very faint. Without the shading it will have a solid black border and will be easier to see.

An Active-X check box will show as 3-D by default, too. To change this, select the check box, right-click and select Properties. In the Properties list find the "Special Effect" entry and change it from "2 -  fmButtonEffectSunken" to "0 - fmButtonEffectFlat"

With an Active-X check box, you can also use the "Format Control" dialog and on the protection tab select "Move and size with cells". Then, when you adjust the height/width of the underlying cells, the whole box will change its dimensions. Most of the time that will look rather ugly, though.

Hope that helps a bit.

cheers, teylyn
Most Valuable Expert 2012
Top Expert 2012

Commented:
You can use some code with the forms control check box if the checkbox is checked.  A couple options include changing the color fill to something that really shows up on the printed page, but also is viewable by the user on the screen.  You can also change the border around the checkbox item when the check box is checked, and remove the border when it is not.  You can also change the size, rotation, etc., though that doesn't seem applicable, here.

You can also do this before printing, then reset back after printing, so the user is never impacted by changes on the screen.  For now, let's just have it viewable the same way on both the worksheet (or form) as well as the printed page.

For this example, all the checkboxes are keyed to the macro called alterCheckBoxAppearance().  There are other methods to trigger this, but let's go with the simplest for this go.

Here's the code in a public module for the attached example (code for forms controls.  alternate code would be needed for ActiveX checkbox):

Option Explicit

Sub alterCheckBoxAppearance()
Dim myCheck As CheckBox

    For Each myCheck In ActiveSheet.CheckBoxes

        If myCheck.Value = 1 Then
            'highlight so it can be seen
            myCheck.Interior.Color = vbCyan 'make blue to be seen
            myCheck.Border.LineStyle = 1 'draw a box around the checkbox
            myCheck.Border.Weight = 4 'make it a thick line
        Else
            'set back to defaults
            myCheck.Interior.Color = xlNone
            myCheck.Border.LineStyle = xlNone
        End If
    Next myCheck
End Sub

Open in new window


See attached.  Download it and check the check boxes and see their appearance change.

Cheers,

Dave
checkBoxMoreVisible-r1.xls

Author

Commented:
Teylyn, I already had it on Flat but it's just not very noticeable.

Dave, Your solution is very well done but not quite what I want. I want a checkmark but just a darker one than Exel provides. I have attached (I hope!) a picture of a checkmark that would be suitable. I was wondering if it's possible to replace the Excel checkbox with this one on the printed page.

My printed page is not the same one the user checks. It's hidden from their view but gets it's information from the cells and checkboxes they are allowed access to.

I couldn't attach the picture. (Ongoing problem for me!!) so here is a link to it.
https://filedb.experts-exchange.com/incoming/ee-stuff/8243-checkmark-fat-100-boxed.png

Thanks
Most Valuable Expert 2012
Top Expert 2012

Commented:
Well, just have a wingding font on the cell next to the checkbox you are printing, and have it check the state of the checkbox. When true, it displays your fancy check to the left of the checkbox.

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
You could even have two images, one for true/one for false, and superimpose the correct one on the page based on what the user selected.  However, if the WingDing font works, why not go that route?

Dave

Author

Commented:
For example, the checkbox to be clicked by the user in in cell S10. The checkmark to be printed is in cell X28. Both are on the same sheet called "Form".
I was wondering if a macro could be assigned to the checkbox click event that would do the following:
When Sheet("Form")S10=True then show the checkmark picture in cell Sheet("Form") X28
When S10=False then leave Checkbox as is.

Is such a thing even possible.... to show a checkmark picture or even if it could show a wingding checkmark that would be fine.

Time for bed!! I'll check back in the morning... Thanks again.
Most Valuable Expert 2012
Top Expert 2012
Commented:
Sure, that's do-able.  I created a checkbox on Sheet("Form") that puts its results in cell S10.  on the click of the checkbox, the macro called CheckBox1_Click() is run which checks the value of S10 (or it could just check the value of the checkbox), then makes the picture you uploaded (currently at X28) visible or invisible at that location.

Also, there's a WingDing formatted cell at X28 with an IF statement that looks at S10 for TRUE/FALSE to display the checkbox or null string.

In this latter case, the CheckBox1_Click() routine is not really needed.

This post then does both, makes the picture visible and puts the wingDing checkbox in if the value at S10 is true.

See attached,


Dave
checkBoxMoreVisible-r2.xls

Author

Commented:
Thanks, Dave. One of those will work for me. The Wingding is much simpler so I'll give it a try first.

I really appreciate your help!

Author

Commented:
Dave attached a workbook that demonstrated his solution which was very helpful.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial