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
 
Should I change Select Case Target to Select Case Value, my compile error isn't even getting as far as that line
 
Upvote 0

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.
1585156493807.png
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
thank you all for your prompt responses, I will go over it all tomorrow, head melted now, need wine!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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