PDA

View Full Version : Populating listbox with selected columns



johnnyfever
10-05-2019, 03:10 PM
I have a custom userform with a list box that displays the last 5 rows in the spreadsheet. By default the listbox only shows the specified number of columns from A to whatever the last specified column is. I want to be able to show 9 columns , but not the first 9.

I tried the solution given here: <Tried to insert link, but the site won't let me post it for some reason. The thread title was "VBA ListBox List - Want only selected columns to be displayed"> but this doesn't work for me and in fact nothing is shown in the listbox at all when I try this.

Here is the code I used to just show the last 5 rows with the first 9 columns:




Private Sub UserForm_Initialize()

Dim lastRow As Long

btnUpdate.Visible = False
btnCancel.Visible = False


lstShowRows.ColumnCount = 9
lstShowRows.RowSource = "A1:AB30000"


With Worksheets("Sheet1")
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
If (lastRow - 5) > 0 Then
Me.lstShowRows.TopIndex = lastRow - 5
End If
End With

' sn = Range("A2:AA100").Value
' sp = Application.Index(sn, [Row(1:99)], Array(1, 2, 3, 4, 5, 7, 9, 12, 13))
'
' lstShowRows.ColumnCount = 9
' lstShowRows.ColumnWidths = "10;40;40;60;60;60;40;40;60"

End Sub


The commented out portion is what I tried based on the link I tried to post above which results in nothing showing in the list box.

How can I get this to work?

p45cal
10-05-2019, 05:40 PM
Guessing: Take out the
lstShowRows.RowSource = "A1:AB30000"
Uncomment those commented out lines at the end
Add a final line:
lstShowRows.list=sp

I could be more definitive if I could test easily, but I'd have to try and duplicate your setup by guessing, probably wrongly, and it takes time. It's always better to attach a workbook with your setup.

johnnyfever
10-05-2019, 05:55 PM
Guessing: Take out the
lstShowRows.RowSource = "A1:AB30000"
Uncomment those commented out lines at the end


I was showing what works, but that part would have been commented out and the part I'm showing commented which came from another forum post ehre would have been uncommented.



Add a final line:
lstShowRows.list=sp

That fixed it. Thanks!

johnnyfever
10-05-2019, 06:59 PM
Add a final line:
lstShowRows.list=sp

While this fixed my problem it's had unintended consequences. I have an Edit button on my form that loads a selected record from the listbox into the text boxes on the form and the edit is done like this:



Private Sub btnEdit_Click()


Dim lRow As Long

btnEdit.Visible = False
btnReset.Visible = False
btnDelete.Visible = False
btnAddRecord.Visible = False
btnSave.Visible = False
btnOutputWord.Visible = False
btnUpdate.Visible = True
btnCancel.Visible = True


With lstShowRows
For lRow = 0 To .ListCount - 1
If .Selected(lRow) Then
txtDeedBook.Value = .List(lRow, 0)
txtPageNo.Value = .List(lRow, 1)
txtDeedNo = .List(lRow, 2)
txtDate = .List(lRow, 3)
txtGrantor = .List(lRow, 4)
txtGrantorLoc = .List(lRow, 5)
txtGrantee = .List(lRow, 6)
txtGranteeLoc = .List(lRow, 7)
txtSalePrice = .List(lRow, 8)
txtOrigGrantDate = .List(lRow, 9)
txtOrigGranted = .List(lRow, 10)
txtAcres = .List(lRow, 11)
txtDescription = .List(lRow, 12)
txtChainofTitle = .List(lRow, 13)
txtAdjoining = .List(lRow, 14)
txtSigOrSeal = .List(lRow, 15)
txtWitness = .List(lRow, 16)
txtRecordDate = .List(lRow, 17)
txtDower = .List(lRow, 18)
txtNotes = .List(lRow, 19)
txtFreeform = .List(lRow, 20)
txtProvenBy = .List(lRow, 21)
txtProvenBefore = .List(lRow, 22)
txtProvenDate = .List(lRow, 23)
txtDowerBefore = .List(lRow, 24)
txtDowerDate = .List(lRow, 25)
txtRecordedIn = .List(lRow, 26)
End If
Next lRow
End With


End Sub


With my listbox only showing 9 columns (not to mention the columns not being in sequence) now when it gets to the line that says txtOrigGrantDate = .List(lRow, 9) I get an error.

How can I load my text boxes for editing to get around this?

p45cal
10-05-2019, 07:18 PM
You really should attach a file - I can't experiment.

johnnyfever
10-05-2019, 07:42 PM
You really should attach a file - I can't experiment.

Select the one record in the bottom box and click the edit button.

paulked
10-06-2019, 05:16 AM
I think it is failing because there is no value for .List(lRow, 9).

You could get round it by



With lstShowRows
On Error Resume Next
For lRow = 0 To .ListCount - 1
If .Selected(lRow) Then
txtDeedBook.Value = .List(lRow, 0)
txtPageNo.Value = .List(lRow, 1)
txtDeedNo = .List(lRow, 2)
txtDate = .List(lRow, 3)
txtGrantor = .List(lRow, 4)
txtGrantorLoc = .List(lRow, 5)
txtGrantee = .List(lRow, 6)
txtGranteeLoc = .List(lRow, 7)
txtSalePrice = .List(lRow, 8)
txtOrigGrantDate = .List(lRow, 9)
txtOrigGranted = .List(lRow, 10)
txtAcres = .List(lRow, 11)
txtDescription = .List(lRow, 12)
txtChainofTitle = .List(lRow, 13)
txtAdjoining = .List(lRow, 14)
txtSigOrSeal = .List(lRow, 15)
txtWitness = .List(lRow, 16)
txtRecordDate = .List(lRow, 17)
txtDower = .List(lRow, 18)
txtNotes = .List(lRow, 19)
txtFreeform = .List(lRow, 20)
txtProvenBy = .List(lRow, 21)
txtProvenBefore = .List(lRow, 22)
txtProvenDate = .List(lRow, 23)
txtDowerBefore = .List(lRow, 24)
txtDowerDate = .List(lRow, 25)
txtRecordedIn = .List(lRow, 26)
End If
Next lRow
On Error GoTo 0
End With

johnnyfever
10-06-2019, 05:22 AM
I think it is failing because there is no value for .List(lRow, 9).

You could get round it by



[/CODE]

I put a value in last night and it didn't change anything. If you create a new record, just fill in a couple boxes randomly with 1's (there's no validation on any of them), put in a value for what .List(lRow, 9) refers to (the box with label "Grant Date:") then press the edit button while selecting the new record you get the error.

Note that .List(lRow, 9) is the 10th item. The listbox is only showing 9 from the code I showed up at the very top. I am no expert in VBA at all, but to me it would seem that it's failing because I created an array to make the listbox and the array only has 9 elements and that item .List(lRow, 9) is the 10th item or what should be an element if I had not restricted the listbox with 9 elements.

I hope that makes sense.

paulked
10-06-2019, 05:33 AM
But there are only 9 columns in your listbox, it can't find .List(lRow, 9) because it's not there!

johnnyfever
10-06-2019, 05:48 AM
But there are only 9 columns in your listbox, it can't find .List(lRow, 9) because it's not there!

Right, that's my point.

Originally my listbox was not formatted to only 9 columns. It was handled by this:


lstShowRows.ColumnCount = 9
lstShowRows.RowSource = "A1:AB30000"


With Worksheets("Sheet1")
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
If (lastRow - 5) > 0 Then
Me.lstShowRows.TopIndex = lastRow - 5
End If
End With


This just tells the listbox to only show 9 columns (the rest are there we just don't see them) and it makes the last 5 rows visible instead of starting from the top.

I didn't like that because the first 9 columns in the spreadsheet have a few things that I didn't need to see and a couple or three columns were further out that I did want to see. My options were to rearrange my spreadsheet or make the listbox only show what I wanted to see. I like to learn new things so I wanted to see if there was a way to make the listbox show exactly what I wanted. I found an option on these forums from 2017 and it was to do this instead of the above:


sn = Range("A2:AA2000").Value
sp = Application.Index(sn, [Row(1:1999)], Array(1, 2, 3, 4, 5, 7, 9, 12, 13))


lstShowRows.List = sp
lstShowRows.ColumnCount = 9
lstShowRows.ColumnWidths = "15;40;25;55;60;60;30;40;60"


With Worksheets("Sheet1")
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
If (lastRow - 5) > 0 Then
Me.lstShowRows.TopIndex = lastRow - 5
End If
End With


So you can see from the above the listbox is populated by an array that only shows columns (with numbering starting at 1 instead of 0) 1, 2, 3, 4, 5, 7, 9, 12, 13.

So, to me the problem is created because my listbox is an array of only 9 elements as shown above. I want to know if there is a way to keep the listbox ONLY showing the columns I want and not giving the error it gives. Or change my code in the area causing the problem. The problem only happens when I want to edit a row which I do by selecting the row in the listbox and populating the text boxes from that row in the spreadsheet.

I need a way to refer to that row in the spreadsheet without calling it the row that the listbox sees since the listbox only has 9 columns.

Does that make sense?

paulked
10-06-2019, 06:01 AM
I would put an unique index column on the sheet and have that in the listbox to use as a reference to pick the data from the row on the sheet.

johnnyfever
10-06-2019, 06:05 AM
I would put an unique index column on the sheet and have that in the listbox to use as a reference to pick the data from the row on the sheet.

OK, I understand the concept.... Does that mean add a new column with a unique identifier to the spreadsheet, show that in the listbox and then say "lookup row XX in the spreadsheet from the unique identifier in the listbox"?

How would I do that in practice?

Fluff
10-06-2019, 06:06 AM
Why not pull all the data into the listbox, but set the columnwidth to 0 for the columns you don't want to see?

paulked
10-06-2019, 06:15 AM
That's a better idea!

johnnyfever
10-06-2019, 06:16 AM
Why not pull all the data into the listbox, but set the columnwidth to 0 for the columns you don't want to see?

I had been tossing that around in my mind.

johnnyfever
10-06-2019, 06:25 AM
Why not pull all the data into the listbox, but set the columnwidth to 0 for the columns you don't want to see?

Yeah, that works great. Thanks! Sometimes it's the simplest things!

Fluff
10-06-2019, 06:31 AM
You're welcome & thanks for the feedback

p45cal
10-06-2019, 06:34 AM
When it comes to updating the edited values to the sheet you use a method to identify which row to update (Cells(i + 1, "A").Select); use the same method to grab the data from the sheet.

johnnyfever
10-06-2019, 06:44 AM
When it comes to updating the edited values to the sheet you use a method to identify which row to update (Cells(i + 1, "A").Select); use the same method to grab the data from the sheet.

OK, thank you. I still like knowing other ways in case I need to do this again with a different project.

snb
10-06-2019, 02:55 PM
Private Sub UserForm_Initialize()
With lstShowRows
.List = Sheet1.Cells(1).CurrentRegion.Value
.ColumnCount = 9
.ColumnWidths = "15;40;25;55;60;60;30;40;60"
.ListIndex = IIf(.ListCount > 5, .ListCount - 5, 0)
End With
End Sub


.Columncount and .columnwidths should be registered in design mode.

NB. your other code can be reduced to 5% of its present size.