VBA Help - Runtime error -2147417848 (80010108) - Method has failed

[PTSD]

Hello,

I'm relatively new to VBA and Excel programming. I've tried looking for solutions on google and here, but oftentimes they mention an update that happened when the error showed up, not specific solutions.

I am building a small datasheet where we can store our Production information - and for the life of me I can't figure out why it's not working. I have uploaded the sample sheet, please excuse the german names for variables and such - we work in German.

The macro Ctrl+n runs the data input form, which works fine unless you want to save multiple products one after another - then it fails with the above mentioned error and crashes the whole Excel. That happens VERY rarely, but it happened twice now.

The worst thing is, I cannot add a product after ID 6. I don't know why, but then it throws that runtime error.
I can add Data in all of the boxes till I get to ID 6. Then it explodes.

Any advice is much appreciated. I really tried to fix it on my own and search for solutions.

I have also posted on another forum, here's the crosspost link: mrexcel.com/board/threads/vba-help-runtime-error-2147417848-80010108-method-has-failed.1252967/

[PTSD]

Adding to that, right now it started doing the same by Entry ID 5 OR when I put in a long string (like an address to our PDF). I have no clue what is going on.

[Andy Pope]

Not sure why but the use of RowSource appears to be causing the crash. I don't get an error message, just a crash when closing the main form.

Try just loading the GetRange information to the .List property.
And refresh the list once new records have been added.

[PTSD]

I'm sorry, could you walk me through what you mean? I really meant it when I said I'm a beginner. I understand programming as a concept but I don't know VBA as good as I know another language, so excuse my lack of knowledge.

When in the AddDataToListBox, after removing the Rowsource, how do I load and refresh the data?

Thanks for your input!

[Andy Pope]

I tweaked the routine like this.

Please Login or Register  to view this content.

The after adding new records you can refresh the list

Please Login or Register  to view this content.

[PTSD]

So I made the changes to the AddDataToListBox, and it threw a wrench in the whole Form. I'm not even sure how to describe it so I made a screenshot. The error is gone, and I can add multiple items one after another, but the whole entry form broke - headers disappeared, formatting changed from minutes to numbers.

Also attached the updated Sheet (I work on it continuously).

Thank you for fixing the error. But how do I fix it without breaking everything?

[ByteMarks]

Here are some changes you can make to continue with the rowsource.


1) Remove the Private from this sub in the Liste form

Please Login or Register  to view this content.

2) Modify the Neu form as follows.
This, if the Liste form is loaded, removes the rowsource before adding the data, then puts it back after.

Please Login or Register  to view this content.

[Andy Pope]

Unfortunately the built in list headers are only populated when RowSource is used.

I tried using the table as the rowsource but it started crashing again.

Simplest approach is to use the .List method to load data, although you will need to use formatted values.
And add labels to the user form

In the attached I read the formatted data from the table and populate the listbox.

[PTSD]

Here are some changes you can make to continue with the rowsource.


1) Remove the Private from this sub in the Liste form
2) Modify the Neu form as follows.
This, if the Liste form is loaded, removes the rowsource before adding the data, then puts it back after.

So this works wonderfully in VBA environment, when I run it through F5. It completely crashes excel with no error when I start the macro in the worksheet.

That's great that it's working, but how to make it work within the spreadsheet? I attached the modified excel file since I can't even give you any error codes.

Unfortunately the built in list headers are only populated when RowSource is used.

I tried using the table as the rowsource but it started crashing again.

Simplest approach is to use the .List method to load data, although you will need to use formatted values.
And add labels to the user form

In the attached I read the formatted data from the table and populate the listbox.

I can't get it to run, whenever I click "Neu" and try to add anything it crashes the whole excel. It does look as it should so if I can get this to work it would be awesome, but I don't even get an error code somehow.

[ByteMarks]

Please make these changes:

Change FormArtikelListe to frm

Please Login or Register  to view this content.

[PTSD]

This works!!!!!! THANK YOU.

So..what happened? Can you shortly explain what error was that and what did you change to make it work?

Also I have a probably dumb question. Somehow, in my edit form, my Close button "Zurück" literally needs to be clicked twice. There is nothing in the code that suggest it should not work, so I'm not sure. I noticed it was the same name as the button for closing in the "Artikel Neu" Form. It works perfectly when in F5 VBA mode, it has to be clicked two times (once to close and reappear the Edit form, then again to finally close it).

How is that even happening, that Excel manages to throw error VBA Editor doesn't? I thought it works together, so it's weird that something that works in VBA F5 stops working in Excel after Macro. If you could shed some light on why is that it would be much appreciated.

Here's the code for the button (or you can check in the sample sheet):

Please Login or Register  to view this content.

[ByteMarks]

So..what happened? Can you shortly explain what error was that and what did you change to make it work?

Basically a bug. Rowsources are always erratically problematic in VBA.

Since the offending rowsource was in the Liste form, I checked if that userform was loaded and if it was removed the rowsource before adding the data, then restablished it afterwards.


Re the Zuruck
On the Liste form, line 10 of buttonEdit calls EditRow which loads the ArtikelEdit form.
The code in the buttonEdit stops there until ArtikelEdit is unloaded.
It then resumes and reloads ArtikelEdit on line 20

So, so solve it you need to remove line 20 (and the declaration above) as EditRow already loads ArtikelEdit

Please Login or Register  to view this content.

[PTSD]

Basically a bug. Rowsources are always erratically problematic in VBA.

Since the offending rowsource was in the Liste form, I checked if that userform was loaded and if it was removed the rowsource before adding the data, then restablished it afterwards.

Thank you for the explanation.

Re the Zuruck
On the Liste form, line 10 of buttonEdit calls EditRow which loads the ArtikelEdit form.
The code in the buttonEdit stops there until ArtikelEdit is unloaded.
It then resumes and reloads ArtikelEdit on line 20

So, so solve it you need to remove line 20 (and the declaration above) as EditRow already loads ArtikelEdit

Please Login or Register  to view this content.

Thanks again it works!

I really hope you don't mind me asking more questions but you're the biggest resource of knowledge that actually works I have found so far.

If you don't mind, here's another question - why can I not scroll through the list with my mouse wheel? I googled a solution here:
mrexcel.com/board/threads/combobox-scroll-down-enabled.559658/page-3#post-4749194

But it threw an error and fully crashed my excel (again), along with red lines in some places. Is it really this complex to use a scroll wheel inside the form?

[ByteMarks]

Great

why can I not scroll through the list with my mouse wheel?

Because they can't natively intercept mousewheel events. I suspect this probably stems from the origins of VBA being before mouse wheels were a thing.

In any case, to overcome this you have to use Window APIs to trap these events yourself by hooking the control, but you need to be diligent about unhooking because it can result in crashes.

I've attached a copy of your sheet with this done using the code from that link in a new module. I shortened the listbox so that there's something to scroll.

The events are underneath existing code:

Please Login or Register  to view this content.

[PTSD]

Man I tried to cram the whole code INSIDE one of my modules and freaked out when every formatting I made there moved around and I got lost. I tried to format it together with any other module in Range but I NEVER thought about putting it in another module for neatness and code visibility. Cheers, it works, you're amazing.

Now I am fighting (actively) with number formatting inside the textboxes. Right now The Table in the sheet looks allright with minutes (although I STILL hate I can't just put 5 there and it'll read as 5 minutes instead of date/hour, so if you have a workaround for that that would be insanely helpful, as I understand Excel really likes having Hour before minutes) but inside the New/Edit forms they're displayed as general numbers.

I've tried the followinq code:

Please Login or Register  to view this content.

But instead of 00:06 I get 12:06. Excel is really driving home how little I know about it. So a couple questions:

1)Can I somehow have it so that if I type a number (say 10:30), it'll automatically take it as "10:30 Min."
and have it as actual minutes for calculation purposes?

2)Can I (in the sheet) click on the table, type in "5" and it'll be taken as 5 minutes (00:05:00) for calculation purposes?

3)How to actually input minutes there? When I format it like I mentioned above, I literally cannot type in the box. One thing that happens is the minutes ticker just goes up and up and up, I can't delete or override it. I have attached the updated spreadsheet (with all your bells and whistles you coded).

4)That's probably not doable BUT: I have a place for the link for our PDF technical drawings there, that's then automatically taken from the Column Zeichnungspfad and formulated into a Hyperlink in the Column Zeichnungslink. Is it in any way possible to have the hyperlink be clickable INSIDE the form? I want to use the form as a databank, so ideally it stays open the whole time someone is using this (instead of going to the table, I want them to stay in that main Form and use this). I can display the text "Zeichnungslink" but I can't click it or anything. I can make a hyperlink on a form itself, but not procdeural (as in I want to take any new link, womble it into hyperlink and display as clickable link inside the listbox). If point 5 was possible, I could then somehow maybe display new text as hyperlink inside that form (that would change depending on the clicked ID from the list), but point 5 is still in question.

5)I just figured I'd rather have the functions from the New/Edit forms inside the main form - forgoing the whole buttons and instead just having them on top. I've attached a screenshot to show you crudely what I meant. Now, after all this work, how much time would it cost to just put those forms inside and have them function the same as they do now? I tried working it out but it throws so many debug errors and crashes for Excel I think I'd have to build it from scratch inside and hope I don't mess it up. I guess I hope you maybe know a way to put that form inside the main form? If not I'll just build it from scratch, but there are so many things that ONLY happen inside the "edit" form for example (the whole data loading, data changing, data overriding) that I don't know how to insulate the rest of the form from those things so that it actually works.
You're insanely helpful. I know it costs your own time, but just so you know you're saving me HOURS of working out what's wrong or how to do something. Being able to ask tailored questions and get answers...just. Really. I appreciate it.

[ByteMarks]

1)
You probably need to use the BeforeUpdate event of the textbox which allows you to cancel. Also vba minutes is nn. mm is months.

e.g. although given the number of textboxes it might be more practical to validate them as part of the update button.

Please Login or Register  to view this content.

2) Not that I know of.

3) Because of using the Change event instead of BeforeUpdate

4) If you had the link address, you could Evaluate the FollowHyperlink formula

5) Probably possible, but a lot more involved. Maybe for a version 2.

[PTSD]

Allright thanks, can I please pick your brain on point 1 and 4 a bit still?

1) The code for minutes work insofar as it displays the proper string - when I type in 15, it shows 15:00. Perfect, but then it shows "00:00 Min." in the actual database, so I checked the entry cell and it sadly takes it as 15:00:00. If I try to type in "0:15" it kind of goes fully blank and doesn't let me click anything - even the "Fertig" button. Gotta use escape to run away.

4) The link address will always be put into the article. The perfect scenario would be having an extra "column" with clickable links on the right side of "Packen" - I was thinking maybe I can somehow add another listbox that'll handle hyperlinks but I did not find a way to make specific cells in specific columns of listbox clickable - as in, when I click on "Schneiden" cell of specific article, it highlights the whole row, and after googling I still came up blank. So I think maybe a better option would be something like this:

Have a hyperlink next to the buttons called "Zeichnungslink", have it automatically update whenever an item is highlighted by reading the "Zeichnungspfad" and wombling it into itself so that I have only one button for every single drawing. Right now I figured out how to make the text actually be a hyperlink, but still didn't find how to make the text read the data entry from "Zeichnungspfad" and womble it into a hyperlink. If you have any tips it would be great.

And you're right, I scraped the whole "forms inside forms" thing 10 minutes after I posted, it would probably take me building it all again because I don't fully comprehend the code that's there and its interactions.

On a side note, I keep putting code one under the other - does VBA have any cascading issues? From the structure I see I can't fully say it goes up to down or just hops around picking stuff that's needed when it needs it.

[ByteMarks]

1) One way is to format the value before adding it since it's only going to be minutes and seconds.

Please Login or Register  to view this content.

4) You could maybe have something in the double-click event of the listbox

Please Login or Register  to view this content.

does VBA have any cascading issues?

It doesn't really in as much that the order the routines are shown doesn't make any difference. However, there are size limits for things like the modules.
Probably more importantly is the scope of the routines and variables and how visible they need to be to other modules.

For example, worksheet events can only be written in the WorkSheet modules. It's also why I removed "Private" from the Sub in post #7 because it needed not to be private to be visible to the other userform.

Excel will also make certain inferences based on where the code is, so as a general rule, unless it's a worksheet/book event it's better to write code in modules and properly qualify the objects being referenced, particularly if you have code that handles many sheets or objects outside of the current workbook.

[PTSD]

So the code works! But not exactly. It pushes only the minutes into the table, which is great, but if you leave any textbox blank it throws an Error 13.

I tried to use that same code to display the data in the ArtikelFormEdit like this:

Please Login or Register  to view this content.

But it doesn't work.

[ByteMarks]

Please Login or Register  to view this content.

Add this function to one of the modules.

Please Login or Register  to view this content.

Then to load data

Please Login or Register  to view this content.

To add data

Please Login or Register  to view this content.

[PTSD]

Uh so I had to actually rebuild the thing as one formular. I managed to do that, and it all works as far as I can tell. Where I could I didn't change anything, but I had to make some changes to the program (reason being I need to add a search/filter function later on and had no clue how to do it with many forms). I am now trying to add the function you mentioned above but it doesn't seem to work.
I used this code to display the minutes inside the textboxes (Gotta click Artikel Speichern to load the data from list to boxes):

Please Login or Register  to view this content.

It works perfectly on the first entry, it falls apart on everything else and throws the Error 13. I've tried to adjust your function but it's out of my depth.

[ByteMarks]

I think you can just change it to this

Please Login or Register  to view this content.

[PTSD]

Hey that's perfect! It works! Thanks.

For clarification about the hyperlink code, does this go inside the lst Database(former ListboxArtikel) code?

Please Login or Register  to view this content.

I adjusted the variables for the new file (ListBoxArtikel->lstDatabase, Index 5 instead of 4) but no dice, nothing happens on double click. I also tried

Please Login or Register  to view this content.

since that code loads everything else in the form but it doesn't do anything. I condensed the code to basically just activation so I can check if the link opens, and it DOES try to open the file, but gets a runtime Error -2147221014 (800401ea). I am trying to google a solution but so far I got people with similar issues that hadn't been able to solve it (or didn't post about it).

I tried opening the website (I put in google.com) and from the sheet level it works and opens up the website, but doesn't work when on form.

I also tried this but it didn't bite:

Please Login or Register  to view this content.

The attached file is the updated one.

[MikeVol]

Hello @PTSD.
Try this code (in your userform):

Please Login or Register  to view this content.

And delete the procedure from your userform: Private Sub lstDatabase_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Update!
Next: Launch your user form, select the desired line in the listbox with a mouse click and press the cmdHyperlink button. Good luck.

[ByteMarks]

I also tried this but it didn't bite:

Because the hyperlink is in the column 4 not 5.

[PTSD]

Because the hyperlink is in the column 4 not 5.

True, it starts with 0. I changed it to 4 and it still threw error 13.

Hello @PTSD.
Try this code (in your userform):

Please Login or Register  to view this content.

And delete the procedure from your userform: Private Sub lstDatabase_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Update!
Next: Launch your user form, select the desired line in the listbox with a mouse click and press the cmdHyperlink button. Good luck.

That works! Wait, but why does THIS work? What's the difference? It works perfectly! I don't even have to have the hyperlink in a hyperlink formula, it can just sit there as a string. That's excatly what I needed. Can you please explain why it works but didn't in the first code?