- Sep 17th 2019
- #5
As Roy said, List is likely the better way. I do hope that your are using code structure, indents.
If your rowsource string is not a continuous range, nothing will show. If it is, you can update the list using rowsource method this way.
- Private Sub UserForm_Initialize[]
- ListBox1.RowSource = [G1]
- End Sub
- Private Sub ListBox1_Click[]
- If ListBox1.ListIndex >= 0 Then UpdateLB1
- End Sub
- Private Sub UpdateLB1[]
- TextBox1.SetFocus
- With ListBox1
- '.RowSource = ""
- .RowSource = [G2]
- .SetFocus
- End With
- End Sub
In a project where I wanted to interact with the list from multiple sheets, I created a scratch sheet. I could use either List or RowSource then.
- Sep 17th 2019
- #6
Thanks Roy & Kenneth. The program has many user defined sheets which is set up from selections from user Form ComboBoxes, data will be entered in these sheets and this forms a data base. Once each sheet is defined; a dyamic variable with data range is setup and stored in maned manager. At the start of the the program data base will be listed in the list box. So the user will be able to select an item and the sheet's contents will be shown in the list box. Dynamic range name for each sheet is stored in G1. So changes to code [apart from Case statement, "it was a long day"] does not work. Sheet selection using ComboBox works absolutley fine, it is the line selection from the list box which is not working!
Even code Me.ListData.RowSource = "" is not clearing the box!!
- Sep 18th 2019
- #9
As I said earlier RowSource needs an address.I suspect your line of code above should be
- Me.ListData.RowSource = ws.Range["A2:M" & ws.Rows.Count].End[xlUp].Address
Although, I would use the List Property to populate the ListBox.
- Me.ListData.Lis = ws.Range["A2:M" & ws.Rows.Count].End[xlUp].Value
Also, I'm confused. You seem to be trying to create a database with multiple sheets, that is not a database.
Why not attach a small example of your workbook.
Hope that Helps
Roy
New users should read the Forum Rules before posting
For free Excel tools & articles visit my web site
Check out my new web site.
royUK's Database Form
Where to paste code from the Forum
About me.
- Sep 18th 2019
- #10
Hello Roy. Thank you for the suggestions, I had tried both options [.Value and .address] but I get a Run-time error. I have attached sample work book at different stages. I think List Box does not allow RowSource refresh whilst line selection is active.
Thanks
Files
sample.docx
[443.17 kB, downloaded 164 times, last: Wednesday, 8:22pm]
- Sep 19th 2019
- #11
You've attached a word documnet. I thought the question was for Excel.
Hope that Helps
Roy
New users should read the Forum Rules before posting
For free Excel tools & articles visit my web site
Check out my new web site.
royUK's Database Form
Where to paste code from the Forum
About me.