Macro Compile Error Sub or Function not defined

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
15
Hello,

I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile error when Im trying to run them from the validation list.
Any help would be greatly appreciated
I am using MS Office 2016 for Mac
Thank you in advance,
Paul

I have pasted the code below;

VBA Code:
'Event code, Target variable contains the cell or range that has been changed
Private Sub Worksheet_Change(ByVal Target As Range)

'Interesect method returns a range object of a rectangular intersection of two or more cell ranges
If Target.Address(True, True) = "$B$3" Then

    'Select Case statment performs one out of several groups of statements based on a condition
    Select Case Target

        'If text in cell B3 is equal to Automation a macro named Automation is executed
        Case "Automation"
        Call Automation

        'If text in cell B3 is equal to Basic Curriculum a macro named Basic is executed
        Case "Basic Curriculum": Basic
      
        'If text in cell B3 is equal to Cleaning a macro named Cleaning is executed
        Case "Cleaning": Cleaning
      
         'If text in cell B3 is equal to Engineering a macro named Engineering is executed
        Case "Engineering": Engineering

        'If text in cell B3 is equal to Facilities a macro named Facilities is executed
        Case "Facilities": Facilities
      
        'If text in cell B3 is equal to GSC a macro named GSC is executed
        Case "GSC": GSC
      
        'If text in cell B3 is equal to GSC Quality a macro named GSC Quality is executed
        Case "GSC Quality": GSCQuality

        'If text in cell B3 is equal to HR a macro named HR is executed
        Case "HR": HR
      
        'If text in cell B3 is equal to IT a macro named IT is executed
        Case "Cleaning": Cleaning
      
         'If text in cell B3 is equal to Engineering a macro named Engineering is executed
        Case "IT": IT

        'If text in cell B3 is equal to Labeling a macro named Labeling is executed
        Case "Labeling": Labeling
      
        'If text in cell B3 is equal to Maintenance a macro named Maintenance is executed
        Case "Maintenance": Maintenance
      
         'If text in cell B3 is equal to Materials a macro named Materials is executed
        Case "Materials": Materials

        'If text in cell B3 is equal to Operations a macro named Operations is executed
        Case "Operations": Operations
      
        'If text in cell B3 is equal to Quality macro named Quality is executed
        Case "Quality": Quality
      
         'If text in cell B3 is equal to Safety a macro named Safety is executed
        Case "Safety": Safety

        'If text in cell B3 is equal to Training a macro named Training is executed
        Case "Training": Training
      
        'If text in cell B3 is equal to Validation a macro named Validation is executed
        Case "Validation": Validation
      
    'Stops Select Case statement
    End Select
End If
End Sub
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,010
Office Version
2019
Platform
Windows
Are they in the same workbook?
 

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
15
Yes all on the same workbook called curriculum, with sheet 1 called Search, B3 is where the validation list is, and the macros copy the relevant info from sheet 2 Curriculum into Sheet 1 Search
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,010
Office Version
2019
Platform
Windows
Are they defined as Public Sub, not just Sub?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,010
Office Version
2019
Platform
Windows
Change them all to Public Sub and it should work.

A Sub can only be called by another procedure in the same module.
 

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
15
Including the Private Sub in the worksheet?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,010
Office Version
2019
Platform
Windows
No, only those not in the worksheet module that need to be called from the worksheet module.
 

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
15
Tried that and I'm still getting the compile error, Private Sub Worksheet_Change(ByVal Target As Range) is highlighted
 

Forum statistics

Threads
1,089,532
Messages
5,408,824
Members
403,233
Latest member
rajrajsha

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top