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

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
95
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Re: Help needed to solve issue with running several VBA codes (error 13 - debugging)

Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top