Compile Error on Private Sub (Sub or Function not defined), when executing Macro

stevobo

New Member
Joined
Aug 7, 2013
Messages
8
Hi all,

I have got 3 macros in place. 1 private sub on a worksheet to make a calculation when a cell value changes as per below:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$F$6" Then
        Call Herberekenen_Resources
    End If
End Sub

This 1st macro calls the following 2nd macro to recalculate certain fields as per below:
VBA Code:
Sub Herberekenen_Resources()
'
    Application.ScreenUpdating = False
    Sheets("Master").Select
    Range("CO2").Select
    Selection.Copy
    Range("CO5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("CO5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("CO3").Select
    Application.CutCopyMode = False
   
    ThisWorkbook.RefreshAll
    Sheets("Resource Dashboard").Select
    Range("B2").Select
    Application.ScreenUpdating = True
   
End Sub

The 3rd macro copies a worksheet to another workbook, as per below:

VBA Code:
Sub ResourceSheet_Voorbereiden()
'
' ResourceSheet_Voorbereiden
'
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
   
    Range("B6").Select
    Selection.Copy
    Range("b7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("Resource dashboard").Select
    Sheets("Resource dashboard").Copy
    Range("F5").Select
    Selection.Hyperlinks.Delete
    Range("F5").Font.Size = 9
    Range("B6").Select
    Selection.Clear
    Range("F5:J250").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveSheet.Shapes.Range(Array("Gebruikt in ConOps 1", "Cluster 1", "Categorie 1", "CONOPS 1", "Beheerder 1", "Type resource 1", "Knop 1", "Knop 2")).Select
    Selection.Delete
   
    Sheets("Resource dashboard").Select
    Sheets("Resource dashboard").Copy After:=Sheets(1)
    Range("A1").Select
    Worksheets("Resource dashboard (2)").Visible = False
   
    Sheets("Resource dashboard").Select
    Range("F10:J250").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=NIET(F10='Resource dashboard (2)'!F10)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16777024
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13421823
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Rows("3:4").Select
    Selection.EntireRow.Hidden = True

    Range("A1").Select
    Application.DisplayAlerts = True
   
    Dim Naam As String
    Naam = "BCM Resource Overview " & Range("b7") & ".xlsx"
   
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=Environ("userprofile") & "\Downloads\" & Naam, FileFormat:=51, CreateBackup:=False
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   
End Sub

When running the 3rd macro I get an error that brings me to the private sub and indicates that there is a compilation error: "Sub or Function is not defined.

Can anyone help me resolve this error?

Many thanks.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,065
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Which module is the Herberekenen_Resources code in?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,065
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Ah. The problem is that you have copied that sheet to a new workbook, then changed it, so its worksheet_change code tries to call a routine that is not in that workbook. You should disable events to stop that happening. Add Application.Enableevents = False to the start of the ResourceSheet_Voorbereiden, then reset it back to True at the end.
 
Solution

stevobo

New Member
Joined
Aug 7, 2013
Messages
8
Ah. The problem is that you have copied that sheet to a new workbook, then changed it, so its worksheet_change code tries to call a routine that is not in that workbook. You should disable events to stop that happening. Add Application.Enableevents = False to the start of the ResourceSheet_Voorbereiden, then reset it back to True at the end.
That's perfect! Problem solved. Thank you so much.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,065
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,363
Messages
5,595,705
Members
414,013
Latest member
tnobbs

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
Top