edit code for list box only have data

[mazan2010]

need to edit the following code that show only data in the range not empty
not like attached photo

[Winon]

Hello mazan2010,

Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[mazan2010]

thanks for your reply
please kindly find attached file

Hello mazan2010,

Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Norie]

Try this.

Please Login or Register  to view this content.

[mazan2010]

dear Norie
its work good
i have note if can make it
in column E the data with decimal i need in listbox not show decimal number for data in column E
also can explain how can calculated column width as
.ColumnWidths = "100;20;50;50;20"

Try this.

Please Login or Register  to view this content.

[Norie]

For the first part try this.

Please Login or Register  to view this content.

Not sure about the second part, what do you want to base the column widths on?

[mazan2010]

i mean what mean in this line

.ColumnWidths = "100;20;50;50;20"

also do you can look to button for delete what the error (not delete after show the message)

[mazan2010]

also if applicable to show head of the table in list box or not
Product name Size Batch Volume Number

[Norie]

This line is setting the widths of the 5 columns, in points (pts)W, in the listbox.

Please Login or Register  to view this content.

It sets the first column to a width 100 pts, the second to 20 pts, the 3rd to 50 pts and so on.

What's the problem with the delete button?

If you just want to delete without a message try this.

Please Login or Register  to view this content.

[mazan2010]

no i need this message but after press ok nothing is happen (not delete any thing

[Norie]

When I click the delete button the selected row is deleted from the listbox and the sheet.

[mazan2010]

as you can see in my attached photo after click ok nothing is happen but when i use the code without message its work for deletion good but i didn't know why

When I click the delete button the selected row is deleted from the listbox and the sheet.

[mazan2010]

just message coming without any effect

[mazan2010]

and can make this message pop
give yes and not instead of ok only

[Norie]

Your message box can only return vbOk, not vbYes or anything else.

[mazan2010]

ok what about head of table can show in listbox or not

[Norie]

Not unless you use RowSource to populate the listbox.

[mazan2010]

you mean
instead start from A2 start from A1 in the following line
Set rngSource = Worksheets("Waiting list").Range("A2:E" & .Range("A" & Rows.Count).End(xlUp).Row)

[Norie]

No, I mean use the RowSource property of the listbox to populate the listbox.

[mazan2010]

im sorry but not know how can do this

No, I mean use the RowSource property of the listbox to populate the listbox.

[Norie]

There are 3 main ways to populate a listbox:

1 using the RowSource property

2 using List

3 using AddItem

For 1 you set the RowSource property of the listbox to a reference to the range you want to populate it with.

For example if you wanted to populate a listbox, ListBox1 from A1:D20 on Sheet1 you would set the RowSource property to Sheet1!A1:D20.

That can be done manually but here's the code to do it anyway.

Please Login or Register  to view this content.

If you want headers in your listbox you have to use this method to populate it.