use VBA code to create Excel drop down list

[raschwab]

I am endeavouring to create and store a dropdown list in a worksheet cell as opposed to using the traditional excel 2007 methodology. The list currently contains seven items. I have played with a few lines of code and cannot seem to get it to work. Also, I would like to be able to wrap the list of list elements in more than one line of code to reduce code width. One of the pieces of code that I have attempted is;

  With Range("A5").Validation
        .Delete
        .Add Type:=xlValidateList, Operator:=xlBetween, Formula:="ListItem1," & _
                "ListItem2," & _
                "ListItem3," & _
                "ListItem4," & _
                "ListItem5," & _
                "ListItem6," & _
                "ListItem7"
    End With

Any help would be very much appreciated.

Rick

 

[Tom Urtis]

This will place Data Validation in cell A5 with a list of items ListItem1 through ListItem7 as you asked.

Sub DVraschwab()
Dim myList$, i%
myList = ""
For i = 1 To 7
myList = myList & "ListItem" & i & ","
Next i
myList = Mid(myList, 1, Len(myList) - 1)
With Range("A5").Validation
.Delete
.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:=myList
End With
End Sub

 

[raschwab]

Thanks, Tom
I am in the throes of creating my first code in VBA, which of course means that I am on a bit of a learning curve. I have two questions regarding the code so that I understand it and possibly use it again in the future.

First, could you tell me what the"$, i%" in the Dim statement signify/does?

Second, I may have inadvertently misconstrued the situation. In my code I used ListItem1, ListItem2 and so on. These were substitutes for account names that I am using in the spreadsheet, e.g. "Questrade TFSA", "TD Waterhouse Spousal SDRSP", etc. It appears as though the code you provided concantenates "ListItem" and "i", which is not quite what I had in mind. In the end I will want is a series of uniquely worded list items that are quite different from one another. Note: There is a possibility that I could be changing/adding items to the list in the future but I can worry about that later. I should point out that the spreadsheet contains two other drop-down lists and my plan was to mimic the code for those.

Now that I have clarified things a bit, can you help me a bit further with the code? Perhaps the list of account names can go in an array or something?

I really appreciate the quick response and the help.

Rick

 

[Tom Urtis]

The $ is an abbreviation for saying "as String".
The % is an abbreviation for saying "as Integer".

Those abbreviation characters have been around for awhile, here is a general overview of variable types, their abbreviation characters, and their description. The intervening dots are my attempt to enhance readability for the fields on this html page.

Type...............Memory....Character...........Description
Byte...............1 byte....none................Positive whole number ranging from 0 through 255 that can be represented as a binary value.
Boolean............2 bytes...none................True or False value
Integer............2 bytes...%...................Whole numbers ranging from -32,768 through 32,767.
Long...............4 bytes...&...................Whole numbers ranging from -2,147,483,648 through 2,147,483,647.
Single.............4 bytes...!...................Single-precision floating-point number (with decimal points) ranging from -3.402823E38 to 3.402823E38.
Double.............8 bytes...#...................Double-precision floating-point number ranging from -1.79769313486232E308 to 1.79769313486232E308.
Currency...........8 bytes...@...................Large numbers between -922,337,203,685,477.5808 and 922,337,203,685,477.5807.
Date...............8 bytes...none................Represents dates from January 1, 100 through December 31, 9999.
Object.............4 bytes...none................An instance of a class or object reference.
String.............10 bytes + 1 byte per char...$...Series of any ASCII characters.
String (fix len)...length of string..............none...Series of any ASCII characters, of a pre-defined length.
Variant............min 16 bytes..................none...Any kind of data except fixed-length String data and user-defined types.

Regarding your question about creating a list of items programmatically, you can either loop through each item in the range and amass the list that way with literal strings, or you can name the range and refer to the named range as the list source. It's hard for me to be more specific than that without more info from you about what you are really working with. Amassing literal strings for data validation will max out at 255 characters, so if your range of list items is large, I'd name the list, which you can do dynamically with or without code, and refer to the named range in your code.

Post back with specifics on what you have in what worksheet(s), and what you want for data validation in what cell(s), and someone can assist.

 

[raschwab]

Tom,
Sorry for taking so long to respond. You were very helpful and, although it took me a little time, I managed to get it to work (albeit I have uncovered errors in other areas of my code). I seem to have trouble with what appears to be some of the simplest things. I suspect it boils down to not really having a good understanding of objected oriented programming. It's new to me.

I was wondering if there is a protocol for indicating that an issue has been resolved. I notced on another site that they insert the code "[Solved]" somwhere in the thread.

Again, much appreciated.

Rick

 

[abhisharma]

Hi users,

is there any way I can create a list of numbers using loops. I want to create a dropdown list which -

1. Says "Month" by default
2. shows numbers from 1 to 12 in the list
3. does not require for me to select this data from any column in the worksheet (can I program a loop i.e. for i = 1 to 12)?

 

[Tom Urtis]

Yes there is a way, in fact more than one way, to do what you are asking. Assuming by "dropdown list" you are referring to a combobox on a userform for example, here are some examples that you can place in the userform's module, further assuming the name of the combobox is ComboBox1.

Load numbers from 1 to 12 into ComboBox1
Dim i%
For i = 1 To 12
ComboBox1.AddItem i
Next i
ComboBox1.ListIndex = 0 'Optional to show the first item

Load 12 months of the year into ComboBox1
Dim i%
For i = 1 To 12
ComboBox1.AddItem Format(DateSerial(1, i, 1), "mmmm")
Next i
ComboBox1.ListIndex = 0 'Optional to show the first item

Load 12 months of year using SPLIT into Combobox1
Dim myarray As Variant
myarray = Split("Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday", "|")
ComboBox1.List = myarray
ComboBox1.ListIndex = 0 'Optional to show the first item

 

[pawcoyote]

Can this be set to a Range instead of just A5 what if you want to use the drop down in all of the cells in Column A?

What if you wanted Storage, Vacation and Not There as your items to select? I know how to do a named range and data validate but I would love t learn how to do this in VB instead of named ranges..

The $ is an abbreviation for saying "as String".
The % is an abbreviation for saying "as Integer".

Those abbreviation characters have been around for awhile, here is a general overview of variable types, their abbreviation characters, and their description. The intervening dots are my attempt to enhance readability for the fields on this html page.

Type...............Memory....Character...........Description
Byte...............1 byte....none................Positive whole number ranging from 0 through 255 that can be represented as a binary value.
Boolean............2 bytes...none................True or False value
Integer............2 bytes...%...................Whole numbers ranging from -32,768 through 32,767.
Long...............4 bytes...&...................Whole numbers ranging from -2,147,483,648 through 2,147,483,647.
Single.............4 bytes...!...................Single-precision floating-point number (with decimal points) ranging from -3.402823E38 to 3.402823E38.
Double.............8 bytes...#...................Double-precision floating-point number ranging from -1.79769313486232E308 to 1.79769313486232E308.
Currency...........8 bytes...@...................Large numbers between -922,337,203,685,477.5808 and 922,337,203,685,477.5807.
Date...............8 bytes...none................Represents dates from January 1, 100 through December 31, 9999.
Object.............4 bytes...none................An instance of a class or object reference.
String.............10 bytes + 1 byte per char...$...Series of any ASCII characters.
String (fix len)...length of string..............none...Series of any ASCII characters, of a pre-defined length.
Variant............min 16 bytes..................none...Any kind of data except fixed-length String data and user-defined types.

Regarding your question about creating a list of items programmatically, you can either loop through each item in the range and amass the list that way with literal strings, or you can name the range and refer to the named range as the list source. It's hard for me to be more specific than that without more info from you about what you are really working with. Amassing literal strings for data validation will max out at 255 characters, so if your range of list items is large, I'd name the list, which you can do dynamically with or without code, and refer to the named range in your code.

Post back with specifics on what you have in what worksheet(s), and what you want for data validation in what cell(s), and someone can assist.

 

[Tom Urtis]

For a macro to do that for all of column A with the 3 optional entry items as you said, this macro would do that. You did say "all of the cells in column A" which are 1,048,576 cells and likely not what you really need, but that's what this code does because that's what you asked for.

Sub Test1()
With Columns(1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Storage,Vacation,Not There"
.InCellDropdown = True
.InputTitle = "Select an optional entry"
.InputMessage = "from the dropdown list."
.ErrorMessage = "Please select one of the" & Chr(10) & "optional entries from" & Chr(10) & "the drop down list."
.ShowInput = True
.ShowError = True
End With
End Sub

 

[pawcoyote]

Thank you very much. You are correct I did say "All" which you know I would not be using the million cells. The max would most likely be 5000 but this is very cool...