Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Red face Unanswered: Correct use of ListBox

    I am using a listbox in Excel 2000 for the first time and can't figure out how to save the value selected in a variable and then close the listbox without losing the saved value. My form has the listbox, an OK command button and a CANCEL command button. A command button on a spreadsheet causes the form with the listbox to be displayed. If CANCEL is clicked, I unload the form; no problem. If OK is clicked, the form just sits there. If I click OK and the code behind the OK button closes the form, I can't tell what item was picked on the list because the form and list are no longer available. If I click the OK and assign the value picked to a variable before unloading the form, the value vanishes when the process returns to the initial VBA subroutine (the code behind the spreadsheet command button that opened the form).

    Thanks for some expert advice.

    Jerry

  2. #2
    Join Date
    Sep 2003
    Location
    Cincinnati, Oh USA
    Posts
    203

    Lightbulb Re: Correct use of ListBox

    Well, I foresee several possibilites that could be causing the problem.
    But on face value I think your VBA code may not contain a Dim statement that would hold your 'list' selection and pass it to where you want it diplayed.
    For example: a userForm with one listbox (ListBox1) that contained "Cats, Dogs, Horses" and one command button (cmdSave)..you want the results to display in sheet1,cell A1 would go something like this:

    Private Sub UserForm_Activate()
    With ListBox1
    .AddItem "cats"
    .AddItem "dogs"
    .AddItem "horses"
    End With
    End Sub

    Private Sub cmdSave_Click()
    Dim strlist As String
    strlist = ListBox1.Text
    Sheet1.Cells(1, 1) = strlist
    End Sub




    [------------------
    I am using a listbox in Excel 2000 for the first time and can't figure out how to save the value selected in a variable and then close the listbox without losing the saved value. My form has the listbox, an OK command button and a CANCEL command button. A command button on a spreadsheet causes the form with the listbox to be displayed. If CANCEL is clicked, I unload the form; no problem. If OK is clicked, the form just sits there. If I click OK and the code behind the OK button closes the form, I can't tell what item was picked on the list because the form and list are no longer available. If I click the OK and assign the value picked to a variable before unloading the form, the value vanishes when the process returns to the initial VBA subroutine (the code behind the spreadsheet command button that opened the form).

    Thanks for some expert advice.

    Jerry [/SIZE][/QUOTE]

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Thanks, your reply has the solution I used: assign the value of the listbox item selected to an unused cell, close the form with the list box and the value is saved for the next steps. I used "DIM" and "PUBLIC" every which way I could think of but could not pass a variable between the code behind the Excel form (with listbox) after closing the form and returning to the VBA that opened the form.

    This issue was resolved by storing a value in in unused cell, assigning the value to a variable and clearing the cell.

    Jerry

  4. #4
    Join Date
    Sep 2003
    Location
    Cincinnati, Oh USA
    Posts
    203
    You are passing the info before the cmdSave finishes its routine and well before the form closes.
    rick


    -----------
    Thanks, your reply has the solution I used: assign the value of the listbox item selected to an unused cell, close the form with the list box and the value is saved for the next steps. I used "DIM" and "PUBLIC" every which way I could think of but could not pass a variable between the code behind the Excel form (with listbox) after closing the form and returning to the VBA that opened the form.

    This issue was resolved by storing a value in in unused cell, assigning the value to a variable and clearing the cell.

    Jerry [/SIZE][/QUOTE]

Posting Permissions

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