Results 1 to 5 of 5

Thread: Display listbox values based on another listbox selection

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    3

    Display listbox values based on another listbox selection

    Hi All,

    Thanks for taking the time to read and hopefully assist. I'm new to VB scripting and struggling with what I think should be a pretty easy (however the task is defeating me). I have searched the internet and there is seems to multiple answers but nothing that is straight forward that I can make sense of to help decipher what is actually happening.

    I have a userform that is appearing when a word template is opened to gather some user data and then insert into some bookmarks that I have placed inside the document. I have a listbox called Company with a selection of 10 different companies (can only select one option) I now need to generate the content of another listbox called "Award" based on which "Company" is selected, however I'm struggling to populate this listbox called "Award" (again only choose one option), then have that "Award" value inserted into the bookmark.

    I was trying to hard code the listbox rather than using an external source like doc, xls, db or xml however open to options. Currently using for my Company listbox;

    Code:
    Private Sub UserForm_Initialize()
        With Company
            .AddItem "Company 1"
            .AddItem "Company 2"
            .AddItem "Company 3"
            .AddItem "Company 4"
            .AddItem "Company 5"
            .AddItem "Company 6"
            .AddItem "Company 7"
            .AddItem "Company 8"
            .AddItem "Company 9"
            .AddItem "Company 10"
        End With
    Appreciate the help.

    Cheers, Jason

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Display listbox values based on another listbox selection

    you need to populate the second (award) listbox in the click event of the company listbox, so when a company is selected the awards for that company will automatically load

    use company.text (or the listindex) as criteria for which awards are loaded into the award listbox

    if you have your companies and awards listed in some type of file (text, .xls or whatever, even customdocumentproperties of the word document). you can just open the file and read the items to add to the listboxes in a loop, making the code much shorter with out the long lists, also the companies and awards can be changed without rewriting code, just edit the lists
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    3

    Re: Display listbox values based on another listbox selection

    Thanks Westconn1, that is what I needed I can now populate the "Award" listbox based on the Company selection listbox. The only minor problem that I can't resolve is that if a user changes their mind or selects the wrong Company then the award just keeps populating the list, it doesn't dynamically change based on the value of the Company listbox.

    I tried using award.items.clear() in my code to clear the listbox at each time it is clicked but I keep getting errors. Any suggestions?

    Can you maybe point me in the direction with your external lists?

    Thanks Jason

  4. #4

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    3

    Re: Display listbox values based on another listbox selection

    OK, so I have managed to the company and award being populated from a table in word. The selection in the award listbox update fine now when a selection is changed in the "Company" listbox . The problem I have now is that when using that value in the Award to update a bookmark it doesn't work. I have a confirm button that prints the value for both company and award correctly into a message box but when I click on OK, the company writes to the bookmark correctly but the value for award doesn't.

    This is my code;

    Code:
    Private Sub CommandButton1_Click()
      If Company.ListIndex > -1 And Award.ListIndex > -1 Then
        If MsgBox("You selected Company " & Company.Text & " and award " _
             & Award.Text & ". Is this correct?", vbQuestion + vbYesNo, "Selection") = vbYes Then
          MsgBox "Correct"
        End If
      Else
        MsgBox "Please Correct"
      End If
    lbl_Exit:
      Exit Sub
    End Sub
    
    Private Sub Submit_Click()
        Dim Company As Range
        Set Company = ActiveDocument.Bookmarks("Company").Range
        Company.Text = Me.Company.Value
        
        Dim Award As Range
        Set Award = ActiveDocument.Bookmarks("Award").Range
        If Award.Text = Manufacturing Then
            Award.Text = "Manufacturing and Associated Industries and Occupations Award 2010."
        ElseIf Award.Text = Retail Then
            Award.Text = "General Retail Industy Award 2010."
        Else
            Award.Text = "Need to update the if statement"
        End If
        
        Me.Repaint
        UserForm1.Hide
        
    End Sub
    Private Sub UserForm_Initialize()
    
    Company.Clear
    Award.Clear
    
    Dim myArray() As Variant
    Dim sourcedoc As Document
    Dim i As Long
    Dim j As Long
    Dim myitem As Range
    Dim m As Long
    Dim n As Long
      Application.ScreenUpdating = False
      Set sourcedoc = Documents.Open(FileName:="C:\Users\jking\Desktop\datastore.docx", Visible:=False)
      i = sourcedoc.Tables(1).Rows.Count - 1
      j = sourcedoc.Tables(1).Columns.Count
      Company.ColumnCount = j
      'Hide column 2
      Company.ColumnWidths = "75;0"
      ReDim myArray(i - 1, j - 1)
      For n = 0 To j - 1
        For m = 0 To i - 1
          Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
          myitem.End = myitem.End - 1
          myArray(m, n) = myitem.Text
        Next m
      Next n
      'Load data into Company
      Company.List = myArray
      sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
    
    lbl_Exit:
            Exit Sub
    End Sub
    Private Sub Company_Change()
    Dim myArray As Variant
      'Use Split function to create an array of data
      myArray = Split(Company.List(Company.ListIndex, 1), Chr(13))
      'Populate listbox2
      Award.List = myArray
    lbl_Exit:
      Exit Sub
    End Sub
    If I use "" around my award.text = "Manufacturing" it always put the else text, if i don't use "" then it always puts the first if text in. Any idea?

    Thanks Jason

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Display listbox values based on another listbox selection

    If I use "" around my award.text = "Manufacturing" it always put the else text, if i don't use "" then it always puts the first if text in. Any idea?
    i am not really sure what the problem is, but without the "" manufacturing and reward are both treated as empty variables
    i would test the actual value of award.text, but it may contain hidden characters, such as paragraph marks, probably the best test to print the text and the length of the text to see they appear to match, though from your comment maybe award.text is empty and hence matches the empty variable
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width