Macro Compile Error Sub or Function not defined

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS
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
 

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS
Should I change Select Case Target to Select Case Value, my compile error isn't even getting as far as that line
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS
1585156493807.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,048
Office Version
  1. 2019
Platform
  1. Windows
Then that would suggest that one of the names is not valid, that is not the Private Sub line, it will be one of the others listed in the procedure.
 

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS

ADVERTISEMENT

I should say, I'm very new to VBA so I don't know what your referring to,
Code below, if you could point out where the error is. the code is in worksheet 1 under the fields worksheet and change
I have created the macros referred to in the code and they are in Modules folder of the same worksheet and they work fine run on there own.

Thank you.

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

'Intersect 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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,929
Office Version
  1. 365
Platform
  1. Windows
The error message is telling you that it does not recognize at least one of the names of the macros you are trying to call.
The macros you are trying call are:
- Automation
- Basic
- Cleaning
- Engineering
- Facilities
- GSC
- GSCQuality
- HR
- Cleaning
- IT
- Labeling
- Maintenance
- Materials
- Operations
- Quality
- Safety
- Training
- Validation

First, make sure that they all do, indeed exist.
Then, make sure that there aren't any typos in there names, so that there names match EXACTLY.
Then make sure that none of them have the word "Private" in front of the word "Sub" where they are defined (mentioned previously).
Lastly, please tell us exactly where all these procedures exist? Which module have you placed them in?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,048
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Taking a different approach, see if this alternative code works for 'any' of the procedures, if I've done this correctly then it should run for any valid procedure names, but error on the last line for any that are not valid.
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim proc_name As String
    If Target.Address(True, True) = "$B$3" Then
        Select Case Target.Value
            Case "Basic Curriculum"
                proc_name = "Basic"
            Case "GSC Quality"
                proc_name = "GSCQuality"
            Case Else
                proc_name = Target.Value
        End Select
    End If
    If proc_name <> "" Then Call proc_name
End Sub
 

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS
thank you all for your prompt responses, I will go over it all tomorrow, head melted now, need wine!
 

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS
Guys, thank you all for your help, the last suggestion "having the correct amount of macros in my modules that are called from the worksheet", I didn't have the correct amount.
So I've sorted that and I'm up and running now.

One more question, can I assign the worksheet macro to a button rather than activating when I select a value from the validation list?

Thanks again
Paul
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub paulfitz()
   Select Case Range("B3").Value
      '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": Call Basic
      'If text in cell B3 is equal to Cleaning a macro named Cleaning is executed
      Case "Cleaning": Call Cleaning
      'If text in cell B3 is equal to Engineering a macro named Engineering is executed
      Case "Engineering": Call Engineering
      'If text in cell B3 is equal to Facilities a macro named Facilities is executed
      Case "Facilities": Call Facilities
      'If text in cell B3 is equal to GSC a macro named GSC is executed
      Case "GSC": Call GSC
      'If text in cell B3 is equal to GSC Quality a macro named GSC Quality is executed
      Case "GSC Quality": Call GSCQuality
      'If text in cell B3 is equal to HR a macro named HR is executed
      Case "HR": Call HR
      'If text in cell B3 is equal to IT a macro named IT is executed
      Case "Cleaning": Call Cleaning
      'If text in cell B3 is equal to Engineering a macro named Engineering is executed
      Case "IT": Call IT
      'If text in cell B3 is equal to Labeling a macro named Labeling is executed
      Case "Labeling": Call Labeling
      'If text in cell B3 is equal to Maintenance a macro named Maintenance is executed
      Case "Maintenance": Call Maintenance
      'If text in cell B3 is equal to Materials a macro named Materials is executed
      Case "Materials": Call Materials
      'If text in cell B3 is equal to Operations a macro named Operations is executed
      Case "Operations": Call Operations
      'If text in cell B3 is equal to Quality macro named Quality is executed
      Case "Quality": Call Quality
      'If text in cell B3 is equal to Safety a macro named Safety is executed
      Case "Safety": Call Safety
      'If text in cell B3 is equal to Training a macro named Training is executed
      Case "Training": Call Training
      'If text in cell B3 is equal to Validation a macro named Validation is executed
      Case "Validation": Call Validation
   'Stops Select Case statement
   End Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,518
Messages
5,548,521
Members
410,844
Latest member
Juno49
Top