data validation function (can someone tell me if this is po

[white6174]

Is it posible to make data validation work in a function. I'm trying to make some cells data validation range change, based on other cells condition. I've put together a function and it works, but only once. If I change some cells that the function is refering to the function gets an error.

I've heard that you can't use functions to change cells formats, does this also apply to data validation.

I could modify my function into a macro but I would rather have a function.

Sorry I couldn't post my code, my floppy disk isn't working (must of got damaged from work to home) I can post code tomorrow.

Thanks for any help you can provide

steve w

[Jay Petrulis]

I don't think this would be possible with a worksheet function (or a UDF called from a worksheet). However, an event macro might do the trick.

Please post the details of your request, so we can get a better feel of what exactly you are trying to do.

Thanks,
Jay

[white6174]

heres my code

If its not possible for this function to work, converting it to a macro will have to do.
Thanks for the help
steve w

Public Static Function DataValidate(validate_Range, dataRange) As Variant
Dim CellCount As Long
CellCount = validate_Range.count
For i = 1 To CellCount
With validate_Range(i).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & dataRange(i)
.IgnoreBlank = True
.InCellDropdown = True

End With

Next
End Function

[Jay Petrulis]

Hi,

I think I got it to work with a worksheet event.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim w, x, y, z, strValidate_Range, strDataRange
If Target.HasFormula Then
    x = WorksheetFunction.Substitute(Target.Formula, "=", "")
    y = WorksheetFunction.Find("(", x, 1)
    
    If UCase(Mid(x, 1, y - 1)) = "DATAVALIDATE" Then
        z = WorksheetFunction.Find(",", x, y)
        w = WorksheetFunction.Find(")", x, z)
        
        strValidate_Range = Mid(x, y + 1, z - y - 1)
        strDataRange = Mid(x, z + 1, w - z - 1)
    
    With Range(strValidate_Range).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & Range(strDataRange).Address(False, False)
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
    End If
End If
End Sub

Please test this and report back. The event trigger looks for the DATAVALIDATE function name, and then gets the ranges associated with it.

As I have it, the actual function result will return a zero, as it is doing nothing. However, the event macro is triggered by the specific formula, so the function is required in this case.

As an added bonus, you should be able to actually calculate with the function and it should reset the validation setting, too.

Please test this out and report your results. If you need specific details of what is occurring, please ask.

Thanks,
Jay

EDIT: For absolute referencing of the vaildation list, change

Range(strDataRange).Address(False, False)

to

Range(strDataRange).Address

[ This Message was edited by: Jay Petrulis on 2002-05-15 12:38 ]