Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to print worksheet with validation circles in Excel?

In certain case, we may circle some invalid data to make them outstanding, but, when you need to print the worksheet with the validation circles, the circles will not be printed. Have you ever been suffered with this problem in Excel? Today, I will show you some quick tricks to solve this job.

Print worksheet with validation circles by pasting them as picture

Print worksheet with validation circles by using VBA code


arrow blue right bubble Print worksheet with validation circles by pasting them as picture


In Excel, we can’t print the validation circles directly, but, we can copy and paste the data range with validation circles as picture, and then print it.

1. Select the data range that you want to print the circles.

2. Press Ctrl + C keys on keyboard to copy it, and then go to a new worksheet, click one cell, and right click, choose Paste Special > Picture, see screenshot:

doc-print-circles-1

3. And then your selection has been pasted as picture, and now when you print the new worksheet, the circles will be printed successfully.

doc-print-circles-1


arrow blue right bubble Print worksheet with validation circles by using VBA code

If you don’t like to paste the data range as picture, the following VBA code also can help you.

1. After setting the data validation, please hold down the Alt + F11 keys in Excel to open the Microsoft Visual Basic for Applications window.

2. Then click Insert > Module, and paste the following macro in the Module Window.

VBA code: Insert red circles to the invalid data

Sub AddValidationCirclesForPrinting()
'Update 20140724
Dim Rng As Range
Dim WorkRng As Range
Dim xCount As Integer
Dim xShape As Shape
On Error Resume Next
Set WorkRng = Application.ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)
If WorkRng Is Nothing Then
    Exit Sub
End If
xCount = 0
For Each Rng In WorkRng
   If Not Rng.Validation.Value Then
       Set xShape = Application.ActiveSheet.Shapes.AddShape(msoShapeOval, Rng.Left - 2, Rng.Top - 2, Rng.Width + 4, Rng.Height + 4)
       xShape.Fill.Visible = msoFalse
       xShape.Line.ForeColor.SchemeColor = 10
       xShape.Line.Weight = 1.25
       xCount = xCount + 1
       xShape.Name = "InvalidData_" & xCount
   End If
Next
Exit Sub
End Sub

3. And then press F5 key to execute this code, and the invalid data has been circled with the red oval, see screenshot:

doc-print-circles-1

4. When you go to print this worksheet which contains the red circles, the circles will be printed as well.

Note:

With this VBA code, the circles will not be removed with the Excel’s Clear Validation Circles feature, if you want to delete the circles, please apply the below code.

VBA code: Remove validation circles in worksheet

Sub RemoveValidationCircles()
'Update 20140724
Dim xShape As Shape
For Each xShape In Application.ActiveSheet.Shapes
    If xShape.Name Like "InvalidData_*" Then
        xShape.Delete
    End If
Next
End Sub

Related article:

How to circle invalid data in Excel?



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.