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
 
Its working a treat now with the original code, is this an alternative code?
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Yes, it just needs to be run manually as requested. :)
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
Hi,
I recently enquired about a macro to display a list from a value selected in a validation drop-down list in an Excel worksheet and the tips you gave me worked perfectly, thank you.
I now want to run a macro to display a list from a value selected in a second drop-down list, i.e. first drop-down list would display department and the second drop-down list would display sub department. Is this possible? below is a copy of the change worksheet code.

Each Macro in the code displays different list

'Event code, Target variable contains the cell or range that has been changed
VBA Code:
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 Finance a macro named Finance is executed
        Case "Finance": Finance
       
        '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 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 PPNL a macro named PPNL is executed
        Case "PPNL": PPNL
       
         'If text in cell B3 is equal to Quality a macro named Quality is executed
        Case "Quality": Quality
       
         'If text in cell B3 is equal to Receiving Inspection a macro named Receiving Inspection is executed
        Case "Receiving Inspection": Receiving
       
        '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 Specialist a macro named Specialist is executed
        Case "Specialist": Specialist
       
        '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
I now want to run a macro to display a list from a value selected in a second drop-down list, i.e. first drop-down list would display department and the second drop-down list would display sub department. Is this possible? below is a copy of the change worksheet code.

Each Macro in the code displays different list
Brand new questions should be posted in new threads, so that they appear as new, unanswered questions (not many people are going to look at threads that already have 3 pages worth of posts, unless they are already involved in it, so the number of people who would see your new question would be very limited).
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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