Invalid Circles - Can I force their display?

[Dreiding]

Excel 2003, I'm doing some data comparison via macros and would like to flag
problem cells using the validation circles. Previously I used cell shading
for highlighting, but this would remove user shading.

Can I select a cell and place a Invalid Circle around it?
Is this possible? Suggestions appreciated.
- Pat

 

[Gary''s Student]

Try:

Sub circleIt()
ActiveSheet.CircleInvalid
End Sub

 

[Rick Rothstein]

If I read your question correctly, you want to be able to single out a cell
based on your own coded validation testing (not using Excel's built-in
Validation feature) and circle it using the red Validation Circle. To do
that , you would need to force a Validation error onto the cell and then
call for the Validation Circle to be displayed. Below is code to do that BUT
using it means the cell you process with it cannot have any of Excel's
built-in Validation rules applied to it.

' ******************** START OF CODE ********************
Dim TheCircledCell As Range

Sub CircleCells(CellToCircle As Range)
If Not CellToCircle Is Nothing Then
With CellToCircle
If .Count > 1 Then Exit Sub
Set TheCircledCell = CellToCircle
.Validation.Delete
.Validation.Add xlValidateTextLength, xlValidAlertInformation,
xlEqual, 2147483647#
.Validation.IgnoreBlank = False
.Parent.CircleInvalid
End With
End If
End Sub

Sub ClearCircles()
If Not TheCircledCell Is Nothing Then
With TheCircledCell
.Validation.Delete
.Parent.ClearCircles
End With
End If
End Sub
' ******************** END OF CODE ********************

To use the above, simple call the CircleCells subroutine from your own code
specifying any cell on any sheet (it doesn't have to be the active cell nor
the active worksheet); for example...

CircleCells Worksheets("Sheet4").Range("F9")

will circle F9 on Sheet4 no matter what sheet is currently active. To remove
the Validation Circle, run the ClearCircles subroutine. It is important that
you run the ClearCircles subroutine sometime before you try to circle
another cell, or before the workbook is saved, in order to clear the bogus
Validation Rule that was imposed on the cell.

 

[Rick Rothstein]

.Validation.Add xlValidateTextLength, xlValidAlertInformation,

xlEqual, 2147483647#

The above line word wrapped onto two lines (at least it did in my
newsreader)... they should both be on one single line.

 

[Dreiding]

Thanks Rick! You understood me correctly and your code will lead me to my
solution.
Thanks again,
- Pat

 

[StickThatInYourPipeAndSmokeIt]

The above line word wrapped onto two lines (at least it did in my
newsreader)... they should both be on one single line.

This post is not directed to Rick, rather all of you.


This is because you guys are in USENET, regardless of whether you access
this forum from a web interface or not.

These groups are Usenet Newgroups.

You should limit line lengths of your replies to 72 characters, NOT the
old 80 Character wide DOS screen width setting. That NEVER worked in
Usenet, which IS where this forum is.

In the old days, if you didn't limit it, it would get concatenated. At
least now, they wrap it to the next line. That *should* be a clue to
folks, but most "modern" folks these days are oblivious to any of the
real details behind any system. Just checking their email equates to a
"complex operation".

None of you dopes should be top posting your replies here like this is
some lame, badly written email app that everyone followed the dumb
paradigm of. In Usenet, the proper convention was interspersed responses
or bottom posted responses, reflecting the chronology of the written
material involved.

It took our "modern" lazy bastard, Parent defying,"pants on the ground"
idiots these days to screw it all up.

These groups should ALL have sub groups for each of the releases, as in
a 2003 group, a 2007 group, a 2010 group, etc. Then one could reasonably
assume that the poster is using the same version without all the wasted
bandwidth (and personal time) involved with asking the idiot to declare
what he is using.

You all just got too goddamned pushbutton lazy, and like to ignore or
buck convention. That is really sad.

You all being too lazy to scroll down and place a response where it
belongs, and even have the gall to argue about it finish it all off.
You don't take the cake. There is no damned cake.