Results 1 to 7 of 7

use VBA code to create Excel drop down list

This is a discussion on use VBA code to create Excel drop down list within the Excel Questions forums, part of the Question Forums category; I am endeavouring to create and store a dropdown list in a worksheet cell as opposed to using the traditional ...

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    15

    Cool use VBA code to create Excel drop down list

    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;
    Code:
      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

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,982

    Default Re: use VBA code to create Excel drop down list

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


    Code:
    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

  3. #3
    New Member
    Join Date
    Mar 2011
    Posts
    15

    Default Re: use VBA code to create Excel drop down list

    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
    Last edited by raschwab; Mar 31st, 2011 at 03:24 AM.

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,982

    Default Re: use VBA code to create Excel drop down list

    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.

  5. #5
    New Member
    Join Date
    Mar 2011
    Posts
    15

    Default Re: use VBA code to create Excel drop down list

    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

  6. #6
    New Member
    Join Date
    Feb 2013
    Posts
    1

    Default Re: use VBA code to create Excel drop down list

    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)?

  7. #7
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,982

    Default Re: use VBA code to create Excel drop down list

    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

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com