• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Populate Excel Table into Listbox and reflect it's changes back to the Table

inddon

Member
Hello There,

I have an excel table and I would like to do certain operation using a Userform Listbox.

When the Userform is loaded, it should populate the rows into the Listbox. When the Userform is active, certain operations on the Listbox (eg. update, delete, change sequence of row, etc.) are performed on it's rows via Fields. On completion of these operations, the Listbox should be reflected back into the excel table.

I have attached a complete working of this requirement in the attached sample workbook for your reference.

I am looking in achieving this requirement with the actual VBA code. Could you please help?

Thank you and look forward to hearing from you.

Regards,
Don
 

Attachments

inddon

Member
See attached.
You can open the userform with shortcut Ctrl + q
Hi Belleke,

Thank you for taking the time to make this work.:):awesome:

There is one small change required:
-In the Userform, when I change any value and click button 'Send to List' it should do the update only in the Listbox. Whereas now it does update in the table 'Tbl_Checkin' as well, this should be avoided.

Only When the button 'OK' is clicked then the Listbox should be reflected to the table.

Could you please apply this modification for me?

Thanks again

Regards,
Don
 

inddon

Member
Here you go:)
Thank you Belleke,

This version is instead adding a new row to the Listbox, where as it should do an update.
- if there is a Check In Number in the Listbox --> then it should update it in the Listbox
-else it should insert a new row in the Listbox

70918


Thanks again and look forward to hearing from you

regards,
Don
 

p45cal

Well-Known Member
I haven't checked/tested this properly (someone else's code and all that) but this is the entire code of the userform with changes highlighted in red:
Code:
Private m_clsListMoveUpDown As CListMover
Dim Blocked As Boolean

Private Sub Cmd_00_Click()
With LB_00
  Blocked = True
  .List(.ListIndex, 0) = T_00.Value
  .List(.ListIndex, 1) = T_01.Value
  .List(.ListIndex, 2) = T_02.Value
  Blocked = False
End With
End Sub
Private Sub Cmd_01_Click()
With Sheets("Admy")
  .ListObjects(1).DataBodyRange.Delete
  .ListObjects(1).ListRows.Add
  .Range("A" & .Rows.Count).End(xlUp).Resize(LB_00.ListCount, 3) = LB_00.List
End With
Reset
End Sub
Private Sub Cmd_02_Click()
Unload Me
End Sub
Private Sub Cmd_03_Click()
For cnt = Me.LB_00.ListCount - 1 To 0 Step -1
  If Me.LB_00.Selected(cnt) Then
    Me.LB_00.RemoveItem cnt
    Exit Sub
  End If
Next cnt
End Sub
Private Sub LB_00_Click()
If Not Blocked Then
  For i = 0 To 2
    Me("T_0" & i) = LB_00.Column(i)
  Next
End If
End Sub
Private Sub UserForm_Initialize()
With LB_00
  .List = [Tbl_Checkin].Value
  .ColumnCount = 3
  .ColumnWidths = "110;150;150"
End With
Set m_clsListMoveUpDown = New CListMover
With m_clsListMoveUpDown
  Set .MoveDownButton = Me.Cmd_05
  Set .MoveUpButton = Me.Cmd_04
  Set .UpDownList = Me.LB_00
End With
End Sub
Sub Reset()
For Each Ctrl In Controls
  If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
Next Ctrl
LB_00.ListIndex = -1
LB_00.List = [Tbl_Checkin].Value
End Sub
Oh, the colouring doesn't show; plain text:
Private m_clsListMoveUpDown As CListMover
Dim Blocked As Boolean

Private Sub Cmd_00_Click()
With LB_00
Blocked = True
.List(.ListIndex, 0) = T_00.Value
.List(.ListIndex, 1) = T_01.Value
.List(.ListIndex, 2) = T_02.Value
Blocked = False

End With
End Sub

Private Sub LB_00_Click()
If Not Blocked Then
For i = 0 To 2
Me("T_0" & i) = LB_00.Column(i)
Next
End If
End Sub
 

kabirr

New Member
Which line is highlighted when you click Debug?
Best attach your workbook.
Just Delete every List item from Listbox. When the listbox is empty click "OK". Will give an error.

Could you please suggest vba for listbox item or table item with more than 500 rows. Provided excel sample work slower for 500 items
Instead could you do this, only new or updated rows in listbox is added/updated to table each time new entry made or updated existing data. Not whole table must be copied from Listbox but only required rows.
 

Attachments

Last edited:

p45cal

Well-Known Member
Quick fix without looking too coseley at possible consequences:
Change:
Code:
    .Range("A" & .Rows.Count).End(xlUp). _
    Resize(LB_00.ListCount, 3) = LB_00.List
to:
Code:
If UBound(LB_00.List) > -1 Then .Range("A" & .Rows.Count).End(xlUp).Resize(LB_00.ListCount, 3) = LB_00.List
 

kabirr

New Member
Quick fix without looking too coseley at possible consequences:
Change:
Code:
    .Range("A" & .Rows.Count).End(xlUp). _
    Resize(LB_00.ListCount, 3) = LB_00.List
to:
Code:
If UBound(LB_00.List) > -1 Then .Range("A" & .Rows.Count).End(xlUp).Resize(LB_00.ListCount, 3) = LB_00.List
Its here, see the attachment created search textbox from list box but the index which I delete or update in list box changes whole sheet. Can u help deleting only the particular index or row from table. Don't want to update whole sheet.
 

Attachments

p45cal

Well-Known Member
Updating the whole table takes a fraction of a second. Any slowness is from elsewhere.
I don't have time to re-write the code.
 

kabirr

New Member
Yes correct. Here I attached screenshot for my concern. Don't want whole program code to re-write. Just the required portion.
1. Select Listbox Item on table using "Select" button.
2. Searched "Bastard" in search textbox to get filtered item in Listbox.
3. Also, on listbox select and the row selected is same, BUT item selected is different.
4. want to select item serial 308 in table, as I selected 308 in listbox and delect it perticularly without changing other items available in table on OK button press.

Please anyone help to select the particular item from Table rows too.
 

Attachments

Top