Results 1 to 10 of 10
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Circle invalid values (Excel2000)

    One of the drawbacks with Data Validation is that a User can copy and paste a value into a cell that has Data validation applied. The copied value may not conform to the validation rule applied.
    The good thing is that Excel provides a tool on the Auditing Toolbar (Tools-Auditing-Show Auditing Toolbar) (why isn't this listed with the other toolbars when you right-click in the top panel?????) - which will circle any such invalid cells.
    I like this feature - even if it only shows up to 255 such errors at a time (why???).
    I want to use this 'circle' feature for other cells for which I have determined as being changed from previous values.
    I can use conditional formatting to highlight such 'changed' cells but I need to retain the original cell formats which vary a lot. The closest I seem to get is to change the cell border outline to red. I would really like to use the circle method.
    Any ideas????

    zeddy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Circle invalid values (Excel2000)

    The following function will create an oval around the specified range. It returns the oval as a shape object for later manipulation.

    Function CircleRange(aRange As Range) As Shape
    ' Factors can be adapted
    Const xFactor As Single = 0.2
    Const yFactor As Single = 0.2
    Dim shp As Shape
    Dim L As Single
    Dim W As Single
    Dim T As Single
    Dim H As Single
    With aRange
    L = .Left
    W = .Width
    T = .Top
    H = .Height
    End With
    Set shp = ActiveSheet.Shapes.AddShape(msoShapeOval, _
    L - W * xFactor, T - H * yFactor, W * (1 + 2 * xFactor), H * (1 + 2 * yFactor))
    With shp
    .Line.ForeColor.SchemeColor = 10
    .Line.Style = msoLineSingle
    .Line.Weight = 1.5
    .Line.Visible = True
    .Fill.Visible = False
    End With
    Set CircleRange = shp
    End Function

    Call it as a procedure if you don't need the result:

    CircleRange Range("D3")

    or call it as a function if you want to keep the result:

    Dim shp As Shape
    Set shp = CircleRange(Range("D3:F5"))
    ...
    ...
    shp.Delete

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Circle invalid values (Excel2000)

    Most of your questions you will have to ask Microsoft on why they do certain things.

    Some comments:
    1) Conditional format RETAINS the original (EXPLICIT) formatting that you put on them. It does not overwrite or change it at all. The Cond Format "format" just takes precedence over the display, BUT the explicit format remains and IS the FORMAT of the cell. If you want to use Cond FOrmat and have the option to turn it off/on at the "Flick of a switch" , add an AND to cond formula which looks for the contents a cell. Then put true or false in that cell. NO matter what other conditions are met if that cell is FALSE the AND formula will be FALSE. Even if the cell is TRUE the other condition STILL must be met. You could have a radio button linked to this cell to or a toggle button for on/off. I have used this to have my spreadsheets color or B/W so they will display in color, but I can set it to B/W to print without using a lot of colored ink.

    2) Hans wrote some code <post#=271593>post 271593</post#> which shows how you could circle a cell. The tough part would now be how to define "changed cells" so that routine would call the procedure from Hans. Some ideas:
    You could create a separate sheet with the ranges and conditions to compare. The routine would go through this table, search the ranges for "bad data" and if bad, call Hans' procedure to draw a circle. You might have to keep track of the names so you can delete them when you do not want them marked anymore or when you check again. Your table setup will depend on how much you want to validate. You could just give min/max or be very general and use items like conditional formatting uses to have any generic formula.

    You could even have different color ovals for different types of errors if desired.

    I think the built-in "cond formatting" will be easier to use and maintain (adding a colored border) would be easier than trying to tell excel when and where to draw and erase ovals in cells, though this could be done with programming

    Steve

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Circle invalid values (Excel2000)

    Many thanks Hans.
    I will try this out and see how long it takes to process.
    (There may be up to 100,000 cells to check).

    zeddy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Circle invalid values (Excel2000)

    Hi Zeddy,

    I don't think "my" method is realistic if you have to go through 100,000 cells, especially if a large number of cells is to be "circled". As Steve (sdckapr) pointed out in his reply, you'd have to store a list of circled cells to be able to remove them selectively.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Circle invalid values (Excel2000)

    Steve - I love the idea of a conditional format toggle!
    Will certainly try this out.
    You are correct - the orginal 'formats' are inherently retained - but it's what the cell looks like that matters here.
    In principle I know which cells I want to circle - each one has a corresponding check cell set to True or False depending on whether a change has been detected.

    zeddy

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Circle invalid values (Excel2000)

    ..instead of storing the list of circled cells to remove them I thought I could get rid of all of them with something like an Activesheet drawing objects type of thing??

    zeddy

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Circle invalid values (Excel2000)

    Hi Zeddy,

    It wouldn't be difficult to delete all drawing objects (or all of type msoShapeOval). You don't need to store a list of them for that:

    Sub RemoveOvals(wsh As Worksheet)
    Dim sh As Shape
    Dim i As Long
    For i = wsh.Shapes.Count To 1 Step -1
    Set sh = wsh.Shapes(i)
    If sh.AutoShapeType = msoShapeOval Then
    sh.Delete
    End If
    Next i
    End Sub

    But if you want to be able to remove them selectively if some values change, you would need to know where they are, unless you remove all and start from scratch. That might be slow.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Circle invalid values (Excel2000)

    This might be a "crazy idea":
    But if you potentially could have 250 INVALID data validations (the limit as you pointed out) and you want to check 100,000 cells for "valid data" I think your approach to a "solution" might be flawed.

    Perhaps instead of testing for invalid data after the fact, build the application/workbook to be "more protected" so that there is a lower probablilty of the user entering invalid data. Perhaps protect everything and then use userforms to add, delete, edit (with replacement), edit (Save as new record), etc and have the userform and code UPFRONT to prevent the invalid data in the first place.

    If you have the potential for 100,000 cells which you think could be invalid, once you find them, will be very tedious to fix, better to prevent the problems than to fix the problems after the fact.

    Steve

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Circle invalid values (Excel2000)

    Steve you are of course correct - prevention is better than cure.
    However, if you inherit a problem sometimes it's too late to tell them how they should've done it in the first place.
    Now the point I originally raised was that I liked the circle feature of the auditing toolbar and wanted to use this type of indicator for other purposes.
    In particular, I am trying to give some users the capabilty to analyse weekly produced spreadsheet dumps.
    I wanted to circle cells that had their contents changed compared with say, the previous weeks file.
    Although I may be looking at 100,000 cells, the actually number of changes may be just a few percent.
    We have a unique ID reference to identify records for comparison. Formulas can easily be used to cross-reference corresponding cells etc. So I 'know' wich cells have been 'updated' i.e. changed. It was just a case of the best way of showing this. I didn't want to use conditional formats for the display as they already rely on display formats for other purposes.
    I like this forum for the way alternative solutions and ideas pop up!

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •