[RESOLVED] Excel drop down lists

[JavaJesus05]

I know it's possible to turn a cell into a drop down list manually by going through the options, but I was wondering if anyone knew of a way to create the drop down list using code.

[dannymking]

If you mean the Filter option in the Data Menu.. then

Select the cell to where you to apply the filter and then

ActiveCell.AutoFilter

You can also set it with the Range object..

For further information and the parameters look AutoFiter up in either the Object Browser (F2) in the VBE (ALT+F11)

[JavaJesus05]

Close, but that's not quite what I'm aiming for...what I'm looking for is in the Validation option in the Data menu.

In this report I'm working on, the user needs to type data in, which is then used in another macro I've made to get the totals. The only problem is, since I can't watch over the people who will have to take over this report, and no one can protect against typos, I want to make a drop down list so that they only have the choices that I program in.

[dannymking]

Yes you can in the same format as above...

ActiveCell.Validation

Extract from Excel VBA Help

Using the Validation Object
Use the Validation property to return the Validation object. The following example changes the data validation for cell E5.

Range("e5").Validation _
    .Modify xlValidateList, xlValidAlertStop, "=$A$1:$A$10"

Use the Add method to add data validation to a range and create a new Validation object. The following example adds data validation to cell E5.

With Range("e5").Validation
    .Add Type:=xlValidateWholeNumber, _
        AlertStyle:=xlValidAlertInformation, _
        Minimum:="5", Maximum:="10"
    .InputTitle = "Integers"
    .ErrorTitle = "Integers"
    .InputMessage = "Enter an integer from five to ten"
    .ErrorMessage = "You must enter a number from five to ten"
End With

[JavaJesus05]

With Range("e5").Validation
    .Add Type:=xlValidateWholeNumber, _
        AlertStyle:=xlValidAlertInformation, _
        Minimum:="5", Maximum:="10"
    .InputTitle = "Integers"
    .ErrorTitle = "Integers"
    .InputMessage = "Enter an integer from five to ten"
    .ErrorMessage = "You must enter a number from five to ten"
End With

Do you think you could explain this in a little more detail? I just recently started using VBA, and until today, I haven't even worked with validation through the menu....

[dannymking]

I'll try but this was taken from the VBA help file in Excel..

'The with object allows you to use a object and it's properties by pressing .
With Range("e5").Validation
     'we are adding a validation rule to the spreadsheet at one cell..
    .Add Type:=xlValidateWholeNumber, _ 'Specifies the whole cell content to check
        AlertStyle:=xlValidAlertInformation, _ 'this is the alert style that will be shown
        Formula1:="5", Formula2:="10" 'This is the range of values that are valid
    'The next four objects are the message settings to be returned to the user
    .InputTitle = "Integers"
    .ErrorTitle = "Integers"
    .InputMessage = "Enter an integer from five to ten"
    .ErrorMessage = "You must enter a number from five to ten"
End With

The code was slightly wrong

The easiest way to understand this is to create a sub in a module and then place the code.. next attempt to use the cell to which the validation is being set..

[JavaJesus05]

Unfortunately, my Excel help file doesn't seem to have any information on VBA....does yours have a code example for validating a List as opposed to Whole Numbers?

[dannymking]

To validate list you need to change the type to xlValidateList and in Formula one place a comma delimted list...

You can also specify an operator after the alertstyle..for example

With Range("A14").Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Formula1:="5,6,7,10"
        .InputTitle = "Integers"
        .ErrorTitle = "Integers"
        .InputMessage = "Enter either 5,6,7 or 10 only"
        .ErrorMessage = "You must enter either 5,6,7 or 10"
End With

[JavaJesus05]

Run-time error '1004':
Application-defined or object-defined error

...any ideas on what it's yelling at me for?

*Note* When I hit 'Debug' it highlights the following line:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Formula1:="5,6,7,10"

[dannymking]

If its already been added you will need to modify it instead..

[JavaJesus05]

Ok...one last question (hopefully). Is there a way to check to see if a cell already has validation or not?

[JavaJesus05]

Nevermind...I found a way to fix it. Thanks for all your help.