Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Datavalidation (XP)

    Is it possible to reference a rangename when setting datavalidation? Such as replacing minimum and maximum reference of L1 to a rangename.

    Thanks,
    John

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Datavalidation (XP)

    As long as that Named range is one cell.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Datavalidation (XP)

    Thanks,

    I thought it was more complicated than simply "=RangeNameHere". I was trying "=Range("RangeNameHere")"

    John

  4. #4
    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: Datavalidation (XP)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>It does not have to be just 1 cell, if it is more than 1 cell you will "use" the 1st cell in the named range.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Datavalidation (XP)

    Thanks, Steve. That's why I enjoy and try to learn as I go. [img]/forums/images/smilies/smile.gif[/img]

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Datavalidation (XP)

    Additionally the named range can also be the result of a calculation eg =AVERAGE(a5:c55) or =PI/3.
    It (the named range)does not have to refer to a range, it needs to define a value.

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Datavalidation (XP)

    Kieran,

    I'd agree there is value in defining a Data Validation range based on a formula like the AVERAGE function. My question would be how does the cell with the validation get re-validated if the cells in the formula change? I checked the precedents of the cell with the validation with a formula but it had none. Is there some VBA solution that could address this?

    Fred

  8. #8
    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: Datavalidation (XP)

    They are validated only on entry.

    You can circle invalid entries by displaying the auditing toolbar (tools - auditing - show auditing toolbar) and selecting the circle invalid data button.

    What do you want the macro to do when/if it finds invalid data? It could slow you down, since it would have to be run all the time, probably on a calculation event.

    Steve

  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: Datavalidation (XP)

    Here is some code that you could add to the "thisworkbook" object (not a module). It will run whenever a change is made and loop thru each worksheet and delete any invalid entries.

    Steve

    <pre>Option Explicit
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Dim rValidation As Range
    Dim rCell As Range
    Dim wks As Worksheet
    For Each wks In Worksheets
    On Error Resume Next
    Set rValidation = wks.Cells.SpecialCells(xlCellTypeAllValidation)
    If Err.Number <> 0 Then Exit For
    On Error GoTo 0
    For Each rCell In rValidation
    If Not rCell.Validation.Value Then
    Application.ScreenUpdating = False
    rCell.ClearContents
    Application.ScreenUpdating = True
    End If
    Next
    Next
    End Sub</pre>


  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Datavalidation (XP)

    Steve,

    Thks for the code. 3 quick questions:
    - the 2 statements
    Set rValidation = wks.Cells.SpecialCells(xlCellTypeAllValidation)
    If Err.Number <> 0 Then Exit For
    I would assume the first stmt is setting the range of cells that have validation. If there are none, is that the error you're testing in the 2nd statement?

    - the inner loop tests
    If Not rCell.Validation.Value
    Not sure I understand the test given all the types of validation. I think my interest would only be concerned with validation rules that were invalidated for some numerical entry - not for example, lists or text length validations (altho the latter might depend on some other cell also). Anyway, does this statement cover all validations or only numerical ones

    - the statement
    rCell.ClearContents
    I guess this is what to change if you just don't want to clear the contents. It may not be obvious after the macro runs which cells were cleared. So I'd want to do something special to the cell (fill it red or raise the ISERR condition for the cell or ...). Just confirming this is where the change would be made.

    THks

    Fred

  11. #11
    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: Datavalidation (XP)

    1) Yes, your surmise correctly. If there are no cells of the type, when you try to SET the range, it will generate an error. This just ignores the error generated and then goes onto the next sheet

    2) The line:
    If Not rCell.Validation.Value

    Checks if the cell has a valid entry. It does not check the validation type. If the validation object of the cell has its value property of FALSE then then the value in the cell is not valid. It could occur due to the cells changing the validation or adding a validation after cell input. It doesn't matter.

    3) Yes
    rCell.ClearContents
    Clears the cells and you could do what you want. If your goal is only to mark the invalid entries, you do not need a macro. As I mentioned there is a button to mark invalid data (on the audit toolbar) and it will put a red circle around invalid entries

    Steve

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Datavalidation (XP)

    Steve,

    Thanks for the answers.

    It looks like there are a few other properties of the Validation object that could be examined if interested in looking only at cells that had a certain type of validation.

    Fred

Posting Permissions

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