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 know I can attach VBA code, but there's multiple forms and modules in my file, so please advise on how to do that if you can.

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 stops working.

Any advice is much appreciated. I really tried to fix it on my own and search for solutions. I can't really attach the file here, so I'm not sure how I should post the whole data form.

 

[RoryA]

Welcome to the forum.

When you save multiple products one after the other, do you keep the form open the whole time and just keep adding data and pressing a Save button or equivalent, or do you reload the form multiple times?

 

[RoryA]

Please take a minute to read the forum rules.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

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

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...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[PTSD]

Hey, thanks for the welcome and I apologize for the cross post. I admit I did only glance at the rules, I never even thought you guys check that other forum. Which is kind of blessing in disguise since I uploaded my spreadsheet there. I will update the main post with information about the cross posting.

The form stays open. I kind of don't know how to tell it to close, so it's not specifically made so. I'd rather have it close, but didn't figure it out yet. I am in "learn as you need" phase, that was another point on my list I wanted to do.

Right now it even stops working at ID 5 very often.

 

[RoryA]

I suggest you convert your Product table to a normal range. Then add code to update the rowsource to the newly expanded range whenever you want it reflected in the listbox. (Personally, I would hide the list form while editing/adding new records, then show it again afterwards)

You should also let the other forum know about the cross-posting. They have basically the same rule about it as we do.

 

[PTSD]

Thanks, I have.
Could you perhaps help me out with the code to do that? I'm sorry, VBA and excel is not something I usually do, so this Data form is cobbled together from things I managed to understand, youtube tutorials, things I managed to formulate into a google search and apply. Should the converting, adding and updating the rowsource be done in this private Sub AddData?

 

[RoryA]

When it happens is up to you. As I mentioned, I would personally hide the list form while adding/editing, so you could then update the listbox by adding a line in the list form directly after the line that shows the other form:

ListBoxArtikel.RowSource = GetRange.Address(external:=True)

You could also make this into a separate routine that you call from any code that needs to update it.

 

[PTSD]

Allright, so let me know if I understood you correctly.
1) Hide the Main form (I am having difficulties doing that somehow - I went into the "Neu" and "Edit" buttons and added "FormArtikelListe.Hide", so it looks like this:

Private Sub buttonNew_Click()
      FormArtikelListe.Hide
    Dim frm As New FormArtikelNeu
    frm.Show
    Call AddDataToListBox
End Sub

And it works, but only in VBA environment - when I run the main form using F5, it hides the main form and leaves only the add/edit form. If I start a macro in the worksheet, it throws Runtime 402.
I assume it has something to do with this code (after trying to figure out what a modal is and z order, and not really succeding):

Private Sub EditRow()
    Dim frm As New FormArtikelEdit
    frm.Currentrow = ListBoxArtikel.ListIndex
        frm.Show vbModal
End Sub

Why does it work in VBA after F5 but not in worksheet though?
2) Put the following code AFTER "frm.Show vbModal" in private Sub EditRow().

    ListBoxArtikel.RowSource = GetRange.Address(external:=True)

I don't think I got it correctly.
I want to thank you the time you take to answer my questions, it is much appreciated.
Is there a way to dumb what you're saying enough so I can understand it?

 

[PTSD]

For future users sake, I am posting the solution that worked (crossposted from excelforum.com) by user ByteMarks:

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

[B] Sub AddDataToListBox()[/B]
'grab the range
    Dim rg As Range
    Set rg = GetRange()
    
'link the range to the list box
    With ListBoxArtikel
   
    .RowSource = rg.Address(external:=True)
    
    .ColumnCount = rg.Columns.Count
    .ColumnWidths = "30;100;80;310;0;0;70;70;70;70;70;70;70;70;70;0;0;0;0;0;0;0"
    .ColumnHeads = True
    .ListIndex = 0
    
    End With

End Sub

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.

'


'little gremlin helpers
Private Sub WriteDataToSheet()
Dim newRow As Long
Dim blnListe As Boolean
Dim frm As Object

For Each frm In UserForms
If frm.Name = "FormArtikelListe" Then
blnListe = True
Exit For
End If
Next


If blnListe Then frm.ListBoxArtikel.RowSource = ""

With shProdukte

newRow = .Cells(.Rows.Count, 1).End(xlUp).row + 1
.Cells(newRow, 1).Value = textboxID.Value
.Cells(newRow, 2).Value = textboxArtikelnr.Value
.Cells(newRow, 3).Value = comboTyp.Value
.Cells(newRow, 4).Value = textboxProduktName.Value
.Cells(newRow, 5).Value = textboxZeichnungsPfad.Value
.Cells(newRow, 7).Value = textboxSchneiden.Value
.Cells(newRow, 8).Value = textboxStanzen.Value
.Cells(newRow, 9).Value = textboxAusklinken.Value
.Cells(newRow, 10).Value = textboxKanten.Value
.Cells(newRow, 11).Value = textboxSagen.Value
.Cells(newRow, 12).Value = textboxBohren.Value
.Cells(newRow, 13).Value = textboxSchweissen.Value
.Cells(newRow, 14).Value = textboxSchleifen.Value
.Cells(newRow, 15).Value = textboxPacken.Value
.Cells(newRow, 16).Value = textboxRkSchneiden.Value
.Cells(newRow, 17).Value = textboxRkStanzen.Value
.Cells(newRow, 18).Value = textboxRkAusklinken.Value
.Cells(newRow, 19).Value = textboxRkKanten.Value
.Cells(newRow, 20).Value = textboxRkSagen.Value
.Cells(newRow, 21).Value = textboxRkSchweissen.Value

If blnListe Then Call frm.AddDataToListBox

End With
End Sub

This solution worked and Excel is no longer crashing. I don't completely understand why it works, but I hope it helps other people in my situation.