question

NickSuy-7494 avatar image
0 Votes"
NickSuy-7494 asked ·

Excel crashes when launching macro

Hi,

Excel keeps on crashing when running this macro.
For some context.
- the user pushes a button VERZENDEN en makes it run.
- There are multiple small other buttons that just delete a field
- For every small 'reset' button, there is a sellection option of 5 buttons


What does the main button do?
- copy certain info to another sheet
- make sure the next time the button is pressed a new collumn is started on the left of the previous one.(by adding a collumn)
- delete certain fields to reset the input sheet completely
- There are simple scores that are copied, just as dates and comments (comments go from 5 cells to 1)


Any idea why it keeps crashing?


Here's the code (+ de code of 1 of these buttons)

Sub VERZENDEN()

 'Controle verzenden
 Dim Antwoord As VbMsgBoxResult
 answer = MsgBox("Ben je zeker?", vbYesNo + vbGuestion + vbDefaultButton1, "VERZENDEN")
 If answer = vbYes Then
    
 'Voeg kolom toe
 Worksheets("Resultaten").Columns("B:Z").Copy Worksheets("Resultaten").Columns("C")
 Worksheets("Resultaten").Columns("B").ClearContents

    
 'kopieer en plak waardes

'Deel 1
Worksheets("Mentor").Range("J3:J8").Value = Worksheets("Resultaten").Range("B3").Value

 Worksheets("Mentor").Range("C9:G9").Copy
 Worksheets("Resultaten").Range("B9").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False
        
 Worksheets("Mentor").Range("O9").Copy
 Worksheets("Resultaten").Range("B2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False

'Deel 2
Worksheets("Mentor").Range("J12:J23").Value = Worksheets("Resultaten").Range("B12").Value

 Worksheets("Mentor").Range("C24:G24").Copy
 Worksheets("Resultaten").Range("B24").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False
    
 Worksheets("Mentor").Range("O24").Copy
 Worksheets("Resultaten").Range("B11").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False

'Deel 3
Worksheets("Mentor").Range("J27:J29").Value = Worksheets("Resultaten").Range("B27").Value

 Worksheets("Mentor").Range("C31:G31").Copy
 Worksheets("Resultaten").Range("B31").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False
    
 Worksheets("Mentor").Range("O31").Copy
 Worksheets("Resultaten").Range("B26").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False

'Deel 4
Worksheets("Mentor").Range("J34:J35").Value = Worksheets("Resultaten").Range("B34").Value

 Worksheets("Mentor").Range("C36:G36").Copy
 Worksheets("Resultaten").Range("B36").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False
    
 Worksheets("Mentor").Range("O36").Copy
 Worksheets("Resultaten").Range("B33").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False

'Deel 5
Worksheets("Mentor").Range("J39:J40").Value = Worksheets("Resultaten").Range("B39").Value

 Worksheets("Mentor").Range("C41:G41").Copy
 Worksheets("Resultaten").Range("B41").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False
    
 Worksheets("Mentor").Range("O41").Copy
 Worksheets("Resultaten").Range("B38").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False

'Deel 6
Worksheets("Mentor").Range("J44:J45").Value = Worksheets("Resultaten").Range("B44").Value

 Worksheets("Mentor").Range("C46:G46").Copy
 Worksheets("Resultaten").Range("B46").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False
    
 Worksheets("Mentor").Range("O46").Copy
 Worksheets("Resultaten").Range("B43").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False

'Deel 7
Worksheets("Mentor").Range("J49:J50").Value = Worksheets("Resultaten").Range("B49").Value

 Worksheets("Mentor").Range("C51:G51").Copy
 Worksheets("Resultaten").Range("B51").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False
    
 Worksheets("Mentor").Range("O51").Copy
 Worksheets("Resultaten").Range("B48").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False

'Deel 8
Worksheets("Mentor").Range("J54:J56").Value = Worksheets("Resultaten").Range("B54").Value

 Worksheets("Mentor").Range("C57:G57").Copy
 Worksheets("Resultaten").Range("B57").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False
    
 Worksheets("Mentor").Range("O57").Copy
 Worksheets("Resultaten").Range("B53").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False

'Deel 9
Worksheets("Mentor").Range("J60:J63").Value = Worksheets("Resultaten").Range("B60").Value

 Worksheets("Mentor").Range("C64:G64").Copy
 Worksheets("Resultaten").Range("B64").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False
    
 Worksheets("Mentor").Range("O64").Copy
 Worksheets("Resultaten").Range("B59").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False
         
         
 'Kopieer en plak datum
 Worksheets("Mentor").Range("G1").Copy
 Worksheets("Resultaten").Range("B1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=False
    
 'Leeg cellen
 Sheets("Mentor").Select
 Range("J3:J8,C9:G9,J12:J23,C24:G24,J27:J30,C31:G31,J34:J35,C36:G36,J39:J40,C41:G41,J44:J45,C46:G46,J49:J50,C51:G51,J54:J56,C57:G57,J60:J63,C64:G64").Select
 Application.CutCopyMode = False
 Selection.ClearContents
    
 'Box bevestiging verzenden
 VBA.Interaction.MsgBox "Gegevens verzonden", , "VERZENDEN"
    
 Else
     Exit Sub
 End If

End Sub



Sub Reset1_1()
Range("J3").Select
Selection.ClearContents
End Sub









not-supported
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

DSPatrick avatar image
0 Votes"
DSPatrick answered ·

Excel is not currently supported here on QnA. They're actively answering question in dedicated forums here.


https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel


https://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel


--please don't forget to Accept as answer if the reply is helpful--


Regards, Dave Patrick ....
Microsoft Certified Professional
Microsoft MVP [Windows Server] Datacenter Management


Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.







·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.