Runtime Filling of Combo in Validation

If I understood it right, you need to create a dynamic named range. The name so defined must then be entered as the source for your dropdown list.

Suppose you have the values in F from F1 on, which you want to show up in a dropdown list. It's required that no other values/formulas appear in column F.

Activate the option Insert|Name|Define, enter DynList as name for Names in workbook and

=OFFSET(x!$F$1,0,0,COUNTA(x!$F:$F),1),

where x is the name of the sheet where the values are.

Activate the option Data|Validation, choose List for Allow on Settings tab and enter =DynList for Source.

After these steps, you can add/remove values to column F on sheet x at will.

Aladin

Hi Anand

Here is how you would do it at Run time.

Sub TryThis()
Dim MyList1 As Range

Set MyList1 = Range("A65536").End(xlUp)
Set MyList1 = Range("A1", MyList1.Address)

With Range("B1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & MyList1.Address
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Just substitute the ranges to suit.

Dave

OzGrid Business Applications

Hi Dave & Aladin ,

Thanks a lot for your responses , I have a feeling theyll work but i am trying it out only tomorrow .. Daves soln seems to be like passing the parmeters that the wizard asks when using validation . Aldins soln seems to be done at design time.. and i have to try it out. Anyways I am sure one of them will work.. Thanks a lot for your time .

Regards

Anand

Hi Aladin,

Your Solution was great cos I had no idea of doing this and like a genie you sorted this with no code at all .. i tried it and it works.. Thanks a lot.

cheers

Anand