Validations in Macros

[wren17]

I am new to the VBA world and i need a
little help on writing a Validation Macro.

I have gotten this far:


Range("B17").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$B$111:$B$225"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

However, when this is ran The cell (B17) will show the data in Cell (B111), which is the first cell in my validation list. I dont want (B111) to show up in (B17). I want to be able to name whatever Cell In my List Range that i want to come up. Like for instance. Cell (B112) to appear in (B17) when the macro executes.

If someone could help me i would really appreciate it. The book i have is a little too basic and does not cover validations. I have gotten this far with the Excel Help only.

Matthew Bertrand

[Nimrod]

Mayber i'm not understanding the problem correctly but ...
If you want to select a particular value from the list you've created ... And you know what that value is then put this code after your validation code. That should select the item that you require from the list.

range("A17").value = "TheValue"

Here's that one line of code appended to your's....

Public Sub thevalue()
        With Range("B17").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=$B1:$B5"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
    Range("B17").Value = "the value"
End Sub

[ This Message was edited by: nimrod on 2002-05-26 14:15 ]

[ This Message was edited by: Nimrod on 2002-05-26 14:20 ]