Excel Data List Validation in Powershell
I have a range named "STATE". I want to set data validation in range ("A1") to only take value within this range using Powershell.
Below is what I have tried. Does not work. I dont know what to put as 4th and 5th parameters. The first 3 are Excel constants equivalent to xlValidateList, xlValidAlertStop and xlBetween respectively.
$ws.Range("A1").Validation开发者_运维知识库.Add(3, 1, 1, "=STATE", 0)
Please help. Thanks.
Found the answer after trial and error.
$missing = [system.type]::missing
$ws.Range("A1").Validation.Delete()
$ws.Range("A1").Validation.Add(3, 1, $missing, "=STATE", $missing)
A1 cell will show a dropdown list populated by values within STATE range.
Note: Dont forget to clear any existing validation rule before applying. Use Validation.Delete(). I spent countless hours because of this.
精彩评论