Delete row source from selected line in ListBox

1 What exactly is the problem?

Is it that the listbox isn't populating correctly?

That could be because the listbox ColumnCount is 1, try changing it to 9.

2 There doesn't appear to be any code to delete from the worksheet?

3 See above.

1. Huh?? Could you rewrite that portion of the code?? (Yes, the issue is that the listbox is not populating correctly - it only shows the date [column A], but should be followed by the other columns as per my OP.)

2. The code to delete is posted above and also in the attachment. Try opening the attachment and clicking on "ADD TRANSACTION", then "Find", then select something from the listbox, then click "Delete" and you will see what I mean. It deletes the row in the sheet based on the activecell (the cell selected by the "Find" macro), and not the row I want (the activecell should be the one relating to the item selected in the listbox).

1 Rewrite what code? All you need to do is change the ColumnCount of the listbox from 1 to 9 which can be done in design view.

2 Oops, missed the delete code - - I was looking at the code in the workbook

The selecting and deleting code have a similar problem.

That is that you aren't working with the row of data on the worksheet that corresponds to the row selected in the listbox.

What makes that hard is that you won't necessarily be dealing with contiguous rows of data so you can't use listindex.

For example you have Cash in rows 402, 404 and 408.

In the listbox those rows will have the listindices 0,1 and 2 respectively - so there's no direct relation between listindex and row number.

What you'll need to do is store the row numbers, in a hidden column, in the listbox along with the rest of the data.

Then when you want to delete the data you can refer to that column to find the row.

Hope that makes some sort of sense.

Have a look at the attached.

1. Ok - done, thanks

2. In your example, I am not sure what you have used as a reference? The amount in column J (value in Euros)? What happens if I have two entries with that value??

It looks fine to me, apart from no currency symbol.

Mind you I don't see where it's being formatted.

If I click on any item that has an FX rate, the formulas gives "stupid" responses, like "-87,666666" for EUR "-10.52 / 1.2" (should be -8.7666666 - note the decimal)


ETA - it's not formatted... It just calculates TextBox3 / TextBox4 (amount / exchange rate).

Sorry I can't even find the numbers you've quoted.

Just remembered a slight problem - the sheet is password protected so I can't make certain changes in it.

For example I was going to test the latest problem with some dummy values but Excel wasn't having it.

Yes - sorry... I added lines to test the formula. See attached (click on first line - ITEM 4).

ETA - to unprotect in column AA (grouped) click on the "lock" icon... This unprotects the sheet (temporarily whilst I test the file). Refresh formulas to re-protect.

There's no button but I found the code to unprotect.

Just tried the form again and values in textbox5 look fine and tally to what's in the listbox.

They could do with rounding/formatting I suppose.

PS ETA?

That's so weird... When I click, it goes retarded... If there is nothing in the exchange rate, it comes out especially stupid...

But as I said, there is no formatting in the code, just "TextBox5.Value = TextBox3.Value / TextBox4.Value"... Could it be to do with my local/country excel settings? Friggin French formatting with their ";" for commas and and "," for periods...


"ETA" = Edited To Add

This might not help but try changing the code for TextBox3 and Textbox4 to this.

Please Login or Register  to view this content.

By the way, why do you have 2 sets of code for finding matching rows?

The first one actually just seems to be counting the no of rows that match.

If that is all it's doing it can be replaced with Application.CountIf.

Please Login or Register  to view this content.

I have done all of this by googling how to write VBA and amending codes to my needs... Occasionally, I get stuck and can't find the answers without asking the questions with my exact issues... Hence why the codes are not as efficient as they could/should be... It's people like you who help ignorant people like me when we can't rely on anyone around us to know what we're talking about!!! So THANK YOU!!! I've tweaked it a bit, and it all seems to work - I've learned a lot today!

No problem.