How to Save a ListBox Value in VBA

Written by bobson st. pierre
  • Share
  • Tweet
  • Share
  • Email

VBA is a programming language and integrated development environment. VBA is included in many of the Microsoft products, including Office. Using the VBA editor programmers can rapidly develop their applications by placing ActiveX controls into their programs. One control often used in VBA to create applications is the ListBox control. The ListBox control is used to display a list of values to users of a VBA program. Working with the ListBox control, programmers can save the ListBox values to a text file on the user's computer. Writing a program that saves values from a ListBox control to your hard drive takes some understanding of VBA and a few lines of code.

Skill level:
Moderately Easy

Other People Are Reading

Instructions

  1. 1

    Open Microsoft Excel.

  2. 2

    Click "Tools," "Macro" and then click "Visual Basic." This will open the VBA editor.

  3. 3

    Click "Insert," "UserForm." You are now in a programming code module.

  4. 4

    Select the "ListBox" control from the VBA ToolBox, drag and draw it to your UserForm.

  5. 5

    Select "CommandButton" control from the VBA ToolBox, drag and draw it to your UserForm.

  6. 6

    Double-click on the CommandButton. This action will take you into the CommandButton's Click event procedure. Type or copy and paste the following code between the "Private Sub CommandButton1_Click()" and "End Sub" procedures.

    Your code should look like the following:

    Private Sub CommandButton1_Click()

    Dim I As Integer

      ListBox1.AddItem "This is test 1"
    
      ListBox1.AddItem "This is test 2"
    
      ListBox1.AddItem "This is test 3"
    
      ListBox1.AddItem "This is test 4"
    
        Open "C:\list.txt" For Append As #1
    
         For I = 0 To ListBox1.ListCount - 1
    
                Print #1, ListBox1.List(I)
    
        Next
    
        Close #1
    

    End Sub

    The code adds values to a ListBox object than save the values to a file on your computer call: list.txt under your C: drive.

  7. 7

    Press "F5" to run the program. A form should popup click the "Button." Go to your C: drive and look for list.txt, you should see the ListBox's values in that text file.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.