Issue with running several VBA codes (error 13 - debugging)

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
87
Situation: I am building a excel sheet with several automated actions - done with macros.
The 1st macro is for creating a new project and it copies the last aktive row with contents and pastes it one row below and clears the cell contents of defined cells in the row.
The 2nd macro copies a row defined by the placement of the cursor in a row and pastes that row below the last row of cell with contents.
These macros work well.

By introducing a further macro (3rd macro) to automate an action in that, when you pick a specific case in a dropdown menu, a text is written into cell xx. The macro works fine and does what is expected.

Error: when I create a new project (runs the 1st macro) I get a debug error 13 and the line "Case "#6_qualified project":.. is highlighted yellow.

What am I doing wrong?

Thanks for your help.
Marc

Here are the 3 macros:
_______________________________ Private Sub CommandButton3_Click()
Sheets("Uebersicht").Unprotect Password:="xxx"
myCheck = MsgBox("new poject?", vbYesNo)
If myCheck = vbNo Then Exit Sub

Application.ScreenUpdating = False
ActiveSheet.Range("R65536").End(xlUp).EntireRow.Select
Selection.Copy
ActiveSheet.Range("R65536").End(xlUp).Offset(1, 0).EntireRow.Select
Selection.Insert

Range("P" & (ActiveCell.Row)).Value = Date
Intersect(Range("L:M,R:R,T:U,AB:BC"), ActiveCell.EntireRow).ClearContents
ActiveSheet.Range("R65536").End(xlUp).Offset(1, 0).EntireRow.Select
Range("L" & (ActiveCell.Row)).Select

Application.ScreenUpdating = True

End Sub
__________________________________________
Private Sub CommandButton4_Click()
Sheets("Uebersicht").Unprotect Password:="SBB"
myCheck = MsgBox("copy and paste row?", vbYesNo)
If myCheck = vbNo Then Exit Sub

Application.ScreenUpdating = False

ActiveCell.EntireRow.Copy
ActiveSheet.Range("R65536").End(xlUp).Offset(1, 0).EntireRow.Select
Selection.Insert
Range("P" & (ActiveCell.Row)).Value = Date

Intersect(Range("AB:AD,AL:AM,AO:BC"), ActiveCell.EntireRow).ClearContents

ActiveSheet.Range("R65536").End(xlUp).Offset(1, 0).EntireRow.Select
Range("L" & (ActiveCell.Row)).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.ScreenUpdating = True
'Sheets("Uebersicht").Protect Password:="SBB", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True

End Sub
_________________________________
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("U2:U65536")) Is Nothing Then
Select Case Target.Value
Case "#6_qualified project": Target.Offset(, 17).Value = "PA created and approve"
Case "#10_finance ready": Target.Offset(, 17).Value = "BP created and approve"
End Select
End If
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,745
Office Version
365
Platform
Windows
Re: Help needed to solve issue with running several VBA codes (error 13 - debugging)

Try adding
Code:
Application.EnableEvents = False
to the top of each macro & reset to True at the bottom
 

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
87
Re: Help needed to solve issue with running several VBA codes (error 13 - debugging)

Hello Fluff

Many thanks for this solution. I did not think it would be so easy. It works !
Wishing you a good weekend.
Marc
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,745
Office Version
365
Platform
Windows
Re: Help needed to solve issue with running several VBA codes (error 13 - debugging)

Glad to help & thanks for the feedback
Have a good weekend as well
 

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
87
Re: Help needed to solve issue with running several VBA codes (error 13 - debugging)

Good morning Fluff
Since my last reply to you I have done some testing to my excel with the adaptations to each macro (Application.EnableEvents = False).
Now when I want to delete a row I get the same error message in the debug mode (error 13).
Why is this and can you help?
Thanks for any advice.
Marc
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,745
Office Version
365
Platform
Windows
Re: Help needed to solve issue with running several VBA codes (error 13 - debugging)

Try this
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Not Intersect(Target, Range("U2:U65536")) Is Nothing And Target.Cells.Count = 1 Then
        Select Case Target.Value
            Case "#6_qualified project"
                Target.Offset(, 17).Value = "PA created and approve"
            Case "#10_finance ready"
                Target.Offset(, 17).Value = "BP created and approve"
        End Select
    End If
Application.EnableEvents = True
End Sub
When you delete a row, target is the entire row, so you cannot use target.value
 

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
87
Re: Help needed to solve issue with running several VBA codes (error 13 - debugging)

Good afternoon Fluff

You are great, thanks. Now it works beautifully and I tested most possibilities of potential users.
Thanks again and have a great day.
Marc
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,745
Office Version
365
Platform
Windows
Re: Help needed to solve issue with running several VBA codes (error 13 - debugging)

Glad to help & thanks for the feedback
 

Forum statistics

Threads
1,085,587
Messages
5,384,610
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top