-
Dec 28th, 2018, 06:05 AM
#1
Thread Starter
New Member
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
-
Dec 28th, 2018, 09:19 PM
#2
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
-
Dec 30th, 2018, 04:01 PM
#3
Thread Starter
New Member
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
-
Dec 30th, 2018, 07:05 PM
#4
Thread Starter
New Member
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
-
Dec 31st, 2018, 04:54 AM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|