Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Adding Items To A Listbox

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a listbox which is tied to a list in a worksheet. Is there anyway a user can add an item without adding it to the list.

    Thanks for the help

    Mareene

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sure, but that depends on how is it "tied"
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mareene

    You could use some code like this:

    Code:
    Dim strRange As String
    
    Private Sub UserForm_Initialize()
    Dim rcell As Range
    
      strRange = Me.ListBox1.RowSource
      Me.ListBox1.RowSource = ""
    
            For Each rcell In Range(strRange)
                ListBox1.AddItem rcell
            Next rcell
      ListBox1.AddItem "NotInList"
    
    End Sub

    When you wish to set the ListBox back to it's RowSource just use:

    Me.ListBox1.RowSource = strRange

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It was an oversight on my part not to mention that my dropdown list has 2 columns. This list is the name of banks with codes for each bank. I have the list in a1:b200 The codes run in alphabetical order, starting with A0:AZ for each branch of a particular bank and then on to B0 etc. in Column A and Column B has the bank description. The code makes the dropdown list blank. Is there any way to add the code and description of the bank without having to amend the list I have in another worksheet? The users that will be using this form will be at a different office - Overseas to be exact- so that means I will have to keep sending updated versions.

    Mareene

Some videos you may like

User Tag List

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
  •