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

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.

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!

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?

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.

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.

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.

Originally Posted by ByteMarks

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.

Originally Posted by ByteMarks

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?

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.

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.

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.

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.

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.

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.

I think you can just change it to this

Please Login or Register  to view this content.

I also tried this but it didn't bite:

Because the hyperlink is in the column 4 not 5.

Originally Posted by ByteMarks

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.

Originally Posted by 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.

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?