VBA userform: modifying/deleting a selected row from a listbox

[mrspz]

Hi there,


I have a listbox which is displaying data from a table in my spreadsheet.


I'd like to be able to select a row and then select a command button to remove this row from the sheet (thus too removing it from the listbox).


In addition, I need to have the data in each collum, of the selected row displayed in textboxes which can be modified. When a textbox has been modified the user can click a command button and the data in the textboxes will be synchronised to the spreadsheet (thus also updating it on the listbox).


If any clarification is required, do let me know - I know this probably reads better myself.


Any help is appreciated! Thanks

 

[My Aswer Is This]

If you still keep getting errors show me the line of code heighted in yellow when you get the error.

 

[My Aswer Is This]

Is sheet2 the active sheet?

 

[mrspz]

By active, do you mean the sheet that is open currently? If so, yes it is.


Highlighted code: (line 33)

ListBox1.List = Data

 

[My Aswer Is This]

Well you will see when the Userform Initializes
There is that same line of code in there.

Not sure what you mean when you say all works well but you get this error.
So The form opens up OK with all you data displayed but any time you click a button the script works but you get this error.

And before you click on any button you choose a row in the listbox

And you have all your controls named like I mentioned in my ealier post.

 

[My Aswer Is This]

The sheet you see on the screen is the active sheet.

 

[mrspz]

Okay, so I did some more investigating and it turns out the 'update' button gives a different error; run-time error '1004'.

With this code highlighted after

        Cells(ListBox1.ListIndex + 1, i).Value = Controls("TextBox" & i).Value

It is only the delete row button which has the error I mentioned previously.

To clarify: yes, the buttons do still serve their function despite resulting in an error afterwards.

edit: quick thought, could it be because I have other textboxes that begin with the name 'textbox'?

 

[My Aswer Is This]

Sure. You can not have other textboxes named Text1 of 2 on 7 etc.

So you have to rename the other textboxes

And are you saying only the Delete button is causing the error.

Do not say it's working if your getting a error.

 

[mrspz]

There's no other textboxes name textbox1-7 however there are some of a greater number e.g. textbox8. Would this have an effect?

Both buttons result in an error. The update button results in error 1004 and the delete button results in error 70.

 

[My Aswer Is This]

Sure if we are working on 8 textboxes you cannot have another textbox name Textbox8

Originally I thought we were working with 7 like your image showed.
I guess I would think you should know that.

Did you name all the controls the way I said??????

Would it not make logic if we now want 8 textboxes to do this job the last one would be named TextBox8

 

[mrspz]

My bad - no I don't already have one named 8. I plucked that number out the air forgetting that this one of the numbers being used.

No I don't have another named 8 (although I may have another named 9).

Yes, everything is named as it should be.