Calling Plettieri - Print range with validation circles

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Hello Plettieri,

On 22 October 02, you posted a question regarding printing a range with validation circles.

http://216.92.17.166/board/viewtopic.php?topic=26183&forum=2

I was cruising around the Microsoft Knowledge Base (as one does on a Sunday morning), and came across the following macros. They may be useful to you (or have I missed the point again?).


Procedure I adopted to get the "Print Validation Circles" macro to work:

1 Enter list of data to validate
2 Go to Data menu/Validation
3 In the "Allow" drop-down, select a criteria e.g.
Whole Number
Between
Minimum = 5
Maximum = 50

4 Run the Validation Circles macro


<pre>
Sub AddValidationCirclesForPrinting()
' http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q190246&
'Macro to Create Data Validation Circles for Printing

Dim DataRange As Range
Dim c As Range
Dim count As Integer
Dim o As Shape

' Set an object variable to all of the cells on the active
' sheet that have data validation -- if an error occurs, run
' the error handler and end the procedure
On Error GoTo errhandler
Set DataRange = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

count = 0

' Loop through each cell that has data validation
For Each c In DataRange

' If the validation value for the cell is false, then draw
' a circle around the cell. Set the circle's fill to
' invisible, the line color to red and the line weight to
' 1.25
If Not c.Validation.Value Then
Set o = ActiveSheet.Shapes.AddShape(msoShapeOval, _
c.Left - 2, c.Top - 2, c.Width + 4, c.Height + 4)
o.Fill.Visible = msoFalse
o.Line.ForeColor.SchemeColor = 10
o.Line.Weight = 1.25

' Change the name of the shape to InvalidData_ + count
count = count + 1
o.Name = "InvalidData_" & count
End If
Next
Exit Sub

errhandler:
MsgBox "There are no cells with data validation on this sheet."

End Sub



Sub RemoveValidationCircles()

Dim shp As Shape

' Remove each shape on the active sheet that has a name starting
' with InvalidData_

For Each shp In ActiveSheet.Shapes
If shp.Name Like "InvalidData_*" Then shp.Delete
Next

End Sub

</pre>

Regards,

Mike
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Mike

I saw you post and was happliy surprised that some actually responded....i thought this was a dead issue...thank you very much...i played with the line thickness and line positioning to fit my idea of presentration and it works like a charm....You answered my query in a very complete and easily understandable answer... tks again

pll
This message was edited by plettieri on 2002-11-03 09:45
 

KerryBruce

New Member
Joined
Jun 7, 2005
Messages
25
Hi,

I found this recently and it works perfect for me, until I tried it on Excel 95, *.xlw, files. When it gets to a cell which is validation false, a Run-time error '1004' "Application-defined or object-defined error". If stops on the line beginning with "Set o = ActiveSheet..."

Does anyone know why this can't run on a *.xlw file, or what modifications need to be made?

Thanks.
 

KerryBruce

New Member
Joined
Jun 7, 2005
Messages
25
Sorry to bring this up again, but I am still having a problem with this. Basically everything about the data validation is working well, except the code about printing the circles on spreadsheets which are *.xlw files. I know that data validation wasn't introduced until excel 97, but I am actually running Office XP, its just the spreadsheets I am running it over are saved as excel 95, or *.xlw versions (don't ask whay long story, but cannot change it!!).

I suspect that the error is something to do with the syntax "sheets" rather than something to do with a problem running over *.xlw files.

Any ides would be appreciated!
 

Forum statistics

Threads
1,144,510
Messages
5,724,777
Members
422,576
Latest member
kayth891

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top