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

[MSchädler]

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]

Re: Help needed to solve issue with running several VBA codes (error 13 - debugging)

Try adding

Application.EnableEvents = False

to the top of each macro & reset to True at the bottom

 

[MSchädler]

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]

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]

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]

Re: Help needed to solve issue with running several VBA codes (error 13 - debugging)

Try this

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]

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]

Re: Help needed to solve issue with running several VBA codes (error 13 - debugging)

Glad to help & thanks for the feedback