Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    How do I display a listbox in VB?

    I'm trying to get a list of tabs in a spreadsheet and display them for the user to select one.

    I can get the list of tabs (worksheets) and I've put them into an array. But how do I then display them for a user to pick the one he wants?

    This is what I have so far:

    OpenFileDialog1.ShowDialog()
    answer = OpenFileDialog1.FileName
    OpenFileDialog1.Dispose()
    xlsWB1 = xlsApp.Workbooks.Open(answer)
    II = 0
    For Each oSh In xlsWB1.Worksheets
    listoftabs(II) = oSh.name
    'MsgBox(oSh.Name)
    II = II + 1
    Next
    ListBox1.DataSource = listoftabs


    Listbox1 is a List Box. But how do I make it visible and get the user to check one item?

    Cheers

    Alan
    Last edited by alan sh; 2013-11-26 at 09:18.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You would need to add the items to the listbox. Something like:
    For Each oSh In xlsWB1.Worksheets
    listbox1.additem = oSh.name
    Next

    or put it them into a range of cells and read the range [part depends on what type of listbox you have and where it is located. [on a form, on a worksheet from the forms or the Active-X controls]

    Steve
    PS to answer the addon questions. If you have the ListBox hidden, you need to make it visible:
    ListBox1.Visible = True
    Last edited by sdckapr; 2013-11-26 at 09:45.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Thanks, but I'm still confused.

    Just making it visible doesn't seem to do anything.
    "Additem" isn't a valid component of Listbox. Edit - I've used " ListBox1.Items.Add(oSh.name)"
    Also, how do I find out which one they chose?

    Alan
    Last edited by alan sh; 2013-11-26 at 11:10.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Is it on a VBA form or is it in the worksheet (or is it somewhere else - Where?)
    If on the worksheet if you right-click the object the Form Control Listbox will have "Assign Macro" as an option, the one from the Active-X controls will have a Properties and a View Code, option.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Sorry - I should have explained - this is using Visual Studio Express.

    I'm trying to create a standalone executable to select a spreadsheet (I can do that), select from a list of tabs in that spreadsheet (I can get the list, but I don't know how to display them and get the user to select one) and then perform actions on that spreadsheet and save it (I can do that bit OK).

    regards

    Alan

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Unfortunately I can not help with that. I can only help with the Excel coding (that requiring the xlsApp).

    I presumed you were using listboxes in Excel (on a worksheet) or Excel VBA (in a form), not a listbox in separate program apart from those 2.

    Perhaps someone else will be able to help though this may be the place for the question since it is not about Spreadsheets...
    Steve

  7. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by alan sh View Post
    Sorry - I should have explained - this is using Visual Studio Express.

    I'm trying to create a standalone executable to select a spreadsheet (I can do that), select from a list of tabs in that spreadsheet (I can get the list, but I don't know how to display them and get the user to select one) and then perform actions on that spreadsheet and save it (I can do that bit OK).

    regards

    Alan
    I've been away from VB Express for a while but I think (as you have found) that if lb is a ListBox object then the correct syntax for adding an item should be:

    lb.Items.Add("sometext")

    If your listbox is placed on a VB form and is visible then the items you have added should show up.

    lb.SelectedItem returns the text of the currently selected item
    lb.SelectedIndex returns the index number of the currently selected item (the first item in the list has index 0; if no item is selected then this property returns -1).

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    So the syntax should be (assuming the name of the listbox is listbox1):

    Code:
    For Each oSh In xlsWB1.Worksheets
       listbox1.add (oSh.name)
    Next

  9. #9
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by jeremybarker View Post
    I've been away from VB Express for a while but I think (as you have found) that if lb is a ListBox object then the correct syntax for adding an item should be:

    lb.Items.Add("sometext")

    If your listbox is placed on a VB form and is visible then the items you have added should show up.

    lb.SelectedItem returns the text of the currently selected item
    lb.SelectedIndex returns the index number of the currently selected item (the first item in the list has index 0; if no item is selected then this property returns -1).
    OK - I need to do some work.

    The LB is on a VB form and it doesn't show up even when I make it visible. So, I am doing something wrong. Thanks for the hint regarding the selected.

    regards

    Alan

  10. #10
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    This is my complete code - I get an answer from the openfile dialogue box and it goes straight to the MsgBox at the end

    Code:
    Dim xlsApp As Object
            Dim xlsWB1 As Object
            Dim xlsWS1 As Object
            Dim myname, outvalue, answer As String
            Dim listoftabs(0 To 100) As String
            Dim II, JJ, KK As Integer
    
    
            xlsApp = CreateObject("Excel.Application")
    
    
            
            OpenFileDialog1.ShowDialog()
            answer = OpenFileDialog1.FileName
            OpenFileDialog1.Dispose()
            xlsWB1 = xlsApp.Workbooks.Open(answer)
            II = 0
            For Each oSh In xlsWB1.Worksheets
                
                ListBox1.Items.Add(oSh.name)
            Next
    
            ListBox1.Visible = True
          
    
            MsgBox("end")
    
            xlsWB1.close(savechanges:=False)
            xlsApp.Quit()
            Close()
    Do I have to make the form visible in some way?

    Alan
    Last edited by alan sh; 2013-11-28 at 12:25.

  11. #11
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by alan sh View Post
    This is my complete code - I get an answer from the openfile dialogue box and it goes straight to the MsgBox at the end

    Code:
    Dim xlsApp As Object
            Dim xlsWB1 As Object
            Dim xlsWS1 As Object
            Dim myname, outvalue, answer As String
            Dim listoftabs(0 To 100) As String
            Dim II, JJ, KK As Integer
    
    
            xlsApp = CreateObject("Excel.Application")
    
    
            
            OpenFileDialog1.ShowDialog()
            answer = OpenFileDialog1.FileName
            OpenFileDialog1.Dispose()
            xlsWB1 = xlsApp.Workbooks.Open(answer)
            II = 0
            For Each oSh In xlsWB1.Worksheets
                
                ListBox1.Items.Add(oSh.name)
            Next
    
            ListBox1.Visible = True
          
    
            MsgBox("end")
    
            xlsWB1.close(savechanges:=False)
            xlsApp.Quit()
            Close()
    Do I have to make the form visible in some way?

    Alan
    Where are you calling this code from? Do you have a VB main form with a ListBox object and a command button that runs this code?
    Not sure what that close() statement is doing towards the end of your code????

  12. #12
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Yes, I have a main app with a button that calls this code. I can't remember what the code() does either, but I don't think that's the issue.

    Alan

Posting Permissions

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