Value of Object Range Failed...HELP

I would be eternally grateful if someone is able to identify the problem with my attached sheet, it seemed to work fine, then all of a sudden it did not. I have tried winding it back and commenting out code to try and identify the cause, but no success so far.

I think it has something to do with either identifying last row in sheet and/or then transferring data from form to the table.

The error I get is 'Value of Object Range Failed' and it just crashes.

What are the steps we can take to reproduce the error? I click the button and a form comes up. Then what?

Originally Posted by 6StringJazzer

What are the steps we can take to reproduce the error? I click the button and a form comes up. Then what?

Yes, it would help if I described the error better.

So when the form loads, I press button on form in top right and it fills with data, up to this point it works fine. Then if I go to the new action section at the bottom and select values from list boxes, dates etc and click button at the bottom (with the expectation that data is added to action table) this is when the error comes.

There is no need to perform a search on the sheet for actual values when all you want is the last row of a range.
However your sheets contain tables which is good - so therefore work with table referencing methods.
The code below was all that you needed to find the last row of your range.

Please Login or Register  to view this content.

The attached file is your sheet/userform set out to use the listbox as your search method.
The form then allows you to add/remove/update selections to and from the listbox.
Note all textboxes and labels retain their default names this allows looping through to populate.
The sequence order follows the table header names from left to right.
You will see with table referencing there is no need to find lastrow - the add newrow method does this automatically.
Also no need to search the table as there is a direct link between the table row and the listbox listindex.
Hope some of the content is benificial.
torachan.

Wow, thank you so much, you far exceeded my expectations, not only solving my original problem, you also seem to be on my wavelength and helped me out with the next few steps of my project.

I was really tearing my hair out yesterday with this, thank you again.

Hi torachan, I notice on your version you have Column Heads set to False on the listbox, I changed this to True, but it does not show the column names, they are all blank. Is there a way to show these?

If you use RowSource to set the listbox data, and set ColumnHeads to True, it will use the first row of RowSource as the column heads. You have to do both.

In your project, the listbox is being loaded by copying an array into List so ColumnHeads will have no effect. I didn't have to time to sift through the code to see whether it would be easy to change. BTW I highly recommend using indentation in the code to show control structures. It makes it much easier to read.

@chris01395, column heads are only accessible when loading with the rowsource method.
rowsource unfortunately is the least stable method of list population and not advisable for serious apps.
the instability is as a result of its constant reading back and forth to the sheet causing stack overload in busy apps.
if necessary it is usual to put a label bar above the listbox.
I do my coding by word processor then paste straight in, for structure reference I save to VBAcodeprint for structure analysis - pdf attached.
torachan

update to include column heads over listbox.
the header label and listbox columns move in sync with the horizontal scroll bar.
you will have to spend a few minutes fine adjusting the line-up by altering the individual column widths.
torachan.

Thanks again torachan, again exceeding my expectations.

Shame about the instability of the rowsource method, however I think your solution will also work well with some tweeking. Out of interest what do you regard as a serious app, my file is likely to only have circa 1,000 lines I think, as I will start a new one for each individual project.

Thanks again for the help

what do you regard as a serious app

The 'how long is a piece of elastic' question, I presume you mean 1,000 rows of data on a sheet or do you mean 1,000 lines of code.
That amount of data is minuscule - no problem - coding is another ball game - 1,000 lines of inefficient code could be a nightmare.
Just speculation, your app does not appear to be the sort that would be accepting data entry on a continuous basis, therefore hardly likely to suffer stack overload.
Normally the memory stack resets each time you reload the program, although repetitive reading of combo/listboxes that use rowsource can overload quickly on older computers - again not too familiar with Mac - all my work has/is mainly MS.