Editing worksheet data using a Userform listbox

[pmjb007]

Hi

I am developing an Excel userform that has a multi-columnlistbox. I intend to use this to display data from a worksheet, make some changesto it, write it back to the worksheet, and refresh the listbox. It’s alist of payments, and the idea is that I can change, say, the amount for apayee, or a payee name etc. Ultimately, I’ll want to add or delete payments,which means that the named range would need to be able to reduce or extend.Initially, though, I just want to get the edit working.

I've been able to successfully populate the listbox from anamed range, using the following code:

With lstPayments
.ColumnCount = 6
.ColumnWidths = "110;80;35;35;60;35"
.RowSource = Sheets(curBudgetMonth).Range("Payments").Address
End With

I’ve also written some code that enables me to identifywhich payment will be changed, from the selected row in the listbox. However, i'mnow kinda stuck trying to figure out the ideal approach to editing and writingback the changes, making sure i’m targetting the right row in the named range.I thought about using an excel table as my source, instead of a named range, thinking Itwould be easier to use table references to write the data back. However, aftermuch trawling through excel forums, I couldn’t find a way to populate thelistbox from an Excel table, so I’m still thinking it will be a named range.

I’m still a bit of a novice at Excel VBA, and part of doingthis little project was so I could learn more, so any help would be muchappreciated!

 

[My Aswer Is This]

To begin with I never use RowSource:

RowSource is not dynamic

Look how I do it:

The source in my example is column A to F

If looks down column A to the last used cell in column A

Now if this helps let me know what else I may be able to help you with.

Private Sub UserForm_Initialize()
'Modified  11/19/2018  7:01:38 AM  EST
Dim Lastrow As Long
Lastrow = Sheets("curBudgetMonth").Cells(Rows.Count, "A").End(xlUp).Row
With lstPayments
    .ColumnCount = 6
    .ColumnWidths = "110;80;35;35;60;35"
    .List = Sheets("curBudgetMonth").Range("A1:F" & Lastrow).Value
End With
End Sub

 

[pmjb007]

To begin with I never use RowSource:

RowSource is not dynamic

Look how I do it:

The source in my example is column A to F

If looks down column A to the last used cell in column A

Now if this helps let me know what else I may be able to help you with.

Private Sub UserForm_Initialize()
'Modified  11/19/2018  7:01:38 AM  EST
Dim Lastrow As Long
Lastrow = Sheets("curBudgetMonth").Cells(Rows.Count, "A").End(xlUp).Row
With lstPayments
    .ColumnCount = 6
    .ColumnWidths = "110;80;35;35;60;35"
    .List = Sheets("curBudgetMonth").Range("A1:F" & Lastrow).Value
End With
End Sub

Thanks, i will give that a try. Have you any suggestions on how i can edit a value, using the listbox, and then write the change back to the correct row?

 

[My Aswer Is This]

I would prefer we wait to see if the script I provided works for you.
Then you cannot edit values in a listbox.
We would need to load the values into TextBoxes.
Then edit the textbox and then reload the values into the proper cells.
Would this work for you?

But I just guessed earlier where your values were.
I guessed at columns A to F

I need to know if that was true before I proceed.

 

[pmjb007]

Hi

Thanks, that worked fine. I've also managed to get an edit process working using textboxes as you suggested, so back on track.

Many thanks
Peter

 

[My Aswer Is This]

Sounds like you have things worked out.
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Hi

Thanks, that worked fine. I've also managed to get an edit process working using textboxes as you suggested, so back on track.

Many thanks
Peter