Call a macro based on text in column C (same row that is active).

willow1985 · Oct 30, 2024

Hi I was hoping someone could help me with this.

I am looking to call different macros depending on what text is in column C on the same row of a selected cell.

So when I select "Open" on Cell Y2, the macro will check text in cell C2 (-22) and call a macro depending on the text in that cell.

If ActiveCell.Offset(0, -22).Range("A1") = "Customer Concern" Call CC
If ActiveCell.Offset(0, -22).Range("A1") = "CAPA" Call CAPA
If ActiveCell.Offset(0, -22).Range("A1") = "Interruptions" Call Interruptions
If ActiveCell.Offset(0, -22).Range("A1") = "Supplier Non-Conformance" Call NCR
If ActiveCell.Offset(0, -22).Range("A1") = 'anything else "Please select a valid Quality Issue Type"
End sub

I know this is not correct but I am looking for something like this.

Thank you to anyone who can help.

What I have so far:

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)



If Intersect(Target, Range("Y:Y")) Is Nothing Then Exit Sub

Select Case Target.Text

Case "Open": Call Complete_File

Case "Closed": Call Verify_Form

Case Else: Exit Sub

End Select



End Sub



Sub Complete_File()

'

' Complete_File Macro

'

Dim msg As String, ans As Variant, lr As Long, Sh As Worksheet, lo As ListObject



If Application.CountIf(Sheets("CC Database").Range(Cells(Selection.Row, 1), Cells(Selection.Row, 20)), "") > 0 Then

MsgBox "Please Complete all manditory fields (Columns B to T)"

Range(Cells(Selection.Row, 25), Cells(Selection.Row, 25)).ClearContents

Exit Sub

End If

‘*****Add requested code here*****

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

1

New Identifier

Original Identifier

Type

Date Opened

Opened by

Estimated Completion Date

Division

Area/Department/Cell

Source

Discrepancy/Issue/Reason

Work Order/Job #

PO#

Model #

Qty

Corresponding NCR/CC/CAPA/Interruption #

Customer/Vendor

Part Name / Description

Part Number / Document Reference

Serial # / Process

Owner

Verification/Action/Repaired

Warranty Granted Y/N /Reprocess

Comments/Notes

Flag Date

Status

Closed Date

Man Hours

Labour Cost

Material Cost

Total Cost

Work done in good faith

Total Days Active

2

Q5000

CC1234

Customer Concern

30-Oct-24

1st Division

Front

Error

N/A

123456

N/A

1

5555

Customer

Part

444444

Open

Sheet1

Cells with Data Validation
Cell	Allow	Criteria
Y2	List	=Sheet2!$A$1:$A$2

6StringJazzer · Oct 30, 2024

I haven't tried to validate your existing code but this will add the functionality you described:

VBA Code:

         Select Case Cells(Selection.Row, "C")
         
            Case "Customer Concern": CC
            Case "CAPA": CAPA
            Case "Interruptions": Interruptions
            Case "Supplier Non-Conformance": NCR
            Case Else: MsgBox "Please select a valid Quality Issue Type"
            
         End Select

You don't need to use "Call", it's obsolete and included in the language for backwards compatibility to old code.

HighAndWilder · Oct 30, 2024

willow1985 said:
Hi I was hoping someone could help me with this.

I am looking to call different macros depending on what text is in column C on the same row of a selected cell.

So when I select "Open" on Cell Y2, the macro will check text in cell C2 (-22) and call a macro depending on the text in that cell.

If ActiveCell.Offset(0, -22).Range("A1") = "Customer Concern" Call CC
If ActiveCell.Offset(0, -22).Range("A1") = "CAPA" Call CAPA
If ActiveCell.Offset(0, -22).Range("A1") = "Interruptions" Call Interruptions
If ActiveCell.Offset(0, -22).Range("A1") = "Supplier Non-Conformance" Call NCR
If ActiveCell.Offset(0, -22).Range("A1") = 'anything else "Please select a valid Quality Issue Type"
End sub

I know this is not correct but I am looking for something like this.

Thank you to anyone who can help.

What I have so far:

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("Y:Y")) Is Nothing Then Exit Sub Select Case Target.Text Case "Open": Call Complete_File Case "Closed": Call Verify_Form Case Else: Exit Sub End Select End Sub Sub Complete_File() ' ' Complete_File Macro ' Dim msg As String, ans As Variant, lr As Long, Sh As Worksheet, lo As ListObject If Application.CountIf(Sheets("CC Database").Range(Cells(Selection.Row, 1), Cells(Selection.Row, 20)), "") > 0 Then MsgBox "Please Complete all manditory fields (Columns B to T)" Range(Cells(Selection.Row, 25), Cells(Selection.Row, 25)).ClearContents Exit Sub End If ‘*****Add requested code here*****

Book1
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF
1 New Identifier Original Identifier Type Date Opened Opened by Estimated Completion Date Division Area/Department/Cell Source Discrepancy/Issue/Reason Work Order/Job # PO# Model # Qty Corresponding NCR/CC/CAPA/Interruption # Customer/Vendor Part Name / Description Part Number / Document Reference Serial # / Process Owner Verification/Action/Repaired Warranty Granted Y/N /Reprocess Comments/Notes Flag Date Status Closed Date Man Hours Labour Cost Material Cost Total Cost Work done in good faith Total Days Active
2 Q5000 CC1234 Customer Concern 30-Oct-24 1st Division Front Error N/A 123456 N/A 1 5555 Customer Part 444444 Open
Sheet1
Cells with Data Validation
Cell Allow Criteria
Y2 List =Sheet2!$A$1:$A$2

This will check what you have entered into column Y and call the procedure depending on what is in column C.

Call a macro based on text in column C.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

1

New Identifier

Original Identifier

Type

Date Opened

Opened by

Estimated Completion Date

Division

Area/Department/Cell

Source

Discrepancy/Issue/Reason

Work Order/Job #

PO#

Model #

Qty

Corresponding NCR/CC/CAPA/Interruption #

Customer/Vendor

Part Name / Description

Part Number / Document Reference

Serial # / Process

Owner

Verification/Action/Repaired

Warranty Granted Y/N /Reprocess

Comments/Notes

Flag Date

Status

Closed Date

Man Hours

Labour Cost

Material Cost

Total Cost

Work done in good faith

Total Days Active

2

Q5000

CC1234

Customer Concern

30-Oct-24

1st Division

Front

Error

N/A

123456

N/A

1

5555

Customer

Part

444444

Open

3

Q5001

CC1235

Interruptions

31-Oct-24

2nd Division

Front

Error

N/A

123457

N/A

1

5555

Customer

Part

444445

Open

4

Q5002

CC1236

Supplier Non-Conformance

01-Nov-24

3rd Division

Front

Error

N/A

123458

N/A

1

5555

Customer

Part

444446

Open

5

Q5003

CC1237

CAPA

02-Nov-24

4th Division

Front

Error

N/A

123459

N/A

1

5555

Customer

Part

444447

Open

6

Q5004

CC1238

Customer Concern

03-Nov-24

5th Division

Front

Error

N/A

123460

N/A

1

5555

Customer

Part

444448

Open

7

Q5005

CC1239

Customer Concern

04-Nov-24

6th Division

Front

Error

N/A

123461

N/A

1

5555

Customer

Part

444449

Open

8

Q5006

CC1240

Customer Concern

05-Nov-24

7th Division

Front

Error

N/A

123462

N/A

1

5555

Customer

Part

444450

Open

Sheet2

Cells with Data Validation
Cell	Allow	Criteria
Y2:Y8	List	Open,Closed

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.CountLarge > 1 Then
    Exit Sub
  End If

  If Not Intersect(Target, Range("Y:Y")) Is Nothing Then
  
    If Target.Value <> "Open" Then
      Exit Sub
    End If

    Select Case Target.Offset(0, -22).Value
    
      Case "Customer Concern": CC

      Case "CAPA": CAPA
      
      Case "Interruptions": Interruptions
      
      Case "Supplier Non-Conformance": NCR
      
      Case Else:
        
        MsgBox "Please select a valid Quality Issue Type", vbOKOnly, "Warning!"
        
    End Select

  End If

End Sub

Public Sub CC()
  MsgBox "CC"
End Sub

Public Sub CAPA()
  MsgBox "CAPA"
End Sub

Public Sub Interruptions()
  MsgBox "Interruptions"
End Sub

Public Sub NCR()
  MsgBox "NCR"
End Sub

willow1985 · Oct 30, 2024

6StringJazzer said:
I haven't tried to validate your existing code but this will add the functionality you described:

VBA Code:

Select Case Cells(Selection.Row, "C") Case "Customer Concern": CC Case "CAPA": CAPA Case "Interruptions": Interruptions Case "Supplier Non-Conformance": NCR Case Else: MsgBox "Please select a valid Quality Issue Type" End Select

You don't need to use "Call", it's obsolete and included in the language for backwards compatibility to old code.

I implemented the above code but got a Compile error listed below:

VBA Code:

Sub Complete_File()
'
' Complete_File Macro
'
  Dim msg As String, ans As Variant, lr As Long, Sh As Worksheet, lo As ListObject
 
  If Application.CountIf(Sheets("Q Log").Range(Cells(Selection.Row, 1), Cells(Selection.Row, 20)), "") > 0 Then
  MsgBox "Please Complete all manditory fields (Columns B to T)"
Range(Cells(Selection.Row, 25), Cells(Selection.Row, 25)).ClearContents
    Exit Sub
  End If

msg = "Your Quality File will be finalized," & vbCrLf & "" & vbCrLf & " Do you wish to continue?"

    ans = MsgBox(msg, vbYesNo)

    Select Case ans

         Select Case Cells(Selection.Row, "C") '****Error on this line****
         
            Case "Customer Concern": CC
            Case "CAPA": CAPA
            Case "Interruptions": Interruptions
            Case "Supplier Non-Conformance": NCR
            Case Else: MsgBox "Please select a valid Quality Issue Type"
            
         End Select
         
            Case vbNo
        GoTo Quit:
    End Select

Quit:
End Sub

willow1985 · Oct 30, 2024

HighAndWilder said:
This will check what you have entered into column Y and call the procedure depending on what is in column C.

Call a macro based on text in column C.xlsm
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF
1 New Identifier Original Identifier Type Date Opened Opened by Estimated Completion Date Division Area/Department/Cell Source Discrepancy/Issue/Reason Work Order/Job # PO# Model # Qty Corresponding NCR/CC/CAPA/Interruption # Customer/Vendor Part Name / Description Part Number / Document Reference Serial # / Process Owner Verification/Action/Repaired Warranty Granted Y/N /Reprocess Comments/Notes Flag Date Status Closed Date Man Hours Labour Cost Material Cost Total Cost Work done in good faith Total Days Active
2 Q5000 CC1234 Customer Concern 30-Oct-24 1st Division Front Error N/A 123456 N/A 1 5555 Customer Part 444444 Open
3 Q5001 CC1235 Interruptions 31-Oct-24 2nd Division Front Error N/A 123457 N/A 1 5555 Customer Part 444445 Open
4 Q5002 CC1236 Supplier Non-Conformance 01-Nov-24 3rd Division Front Error N/A 123458 N/A 1 5555 Customer Part 444446 Open
5 Q5003 CC1237 CAPA 02-Nov-24 4th Division Front Error N/A 123459 N/A 1 5555 Customer Part 444447 Open
6 Q5004 CC1238 Customer Concern 03-Nov-24 5th Division Front Error N/A 123460 N/A 1 5555 Customer Part 444448 Open
7 Q5005 CC1239 Customer Concern 04-Nov-24 6th Division Front Error N/A 123461 N/A 1 5555 Customer Part 444449 Open
8 Q5006 CC1240 Customer Concern 05-Nov-24 7th Division Front Error N/A 123462 N/A 1 5555 Customer Part 444450 Open
Sheet2
Cells with Data Validation
Cell Allow Criteria
Y2:Y8 List Open,Closed

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub End If If Not Intersect(Target, Range("Y:Y")) Is Nothing Then If Target.Value <> "Open" Then Exit Sub End If Select Case Target.Offset(0, -22).Value Case "Customer Concern": CC Case "CAPA": CAPA Case "Interruptions": Interruptions Case "Supplier Non-Conformance": NCR Case Else: MsgBox "Please select a valid Quality Issue Type", vbOKOnly, "Warning!" End Select End If End Sub Public Sub CC() MsgBox "CC" End Sub Public Sub CAPA() MsgBox "CAPA" End Sub Public Sub Interruptions() MsgBox "Interruptions" End Sub Public Sub NCR() MsgBox "NCR" End Sub

This works but does not account for another option in Column Y like my original. I do need another Macro called for if the cell states "Closed". I am not sure how to modify this to account for that unfortunately.

6StringJazzer · Oct 30, 2024

That because you put my code in an invalid location, which is described in the error message.

Rich (BB code):

    ans = MsgBox(msg, vbYesNo)

    Select Case ans
You cannot put any code here===>
         Select Case Cells(Selection.Row, "C") 
       
            Case "Customer Concern": CC
            Case "CAPA": CAPA
            Case "Interruptions": Interruptions
            Case "Supplier Non-Conformance": NCR
            Case Else: MsgBox "Please select a valid Quality Issue Type"
          
         End Select
<===to here       
            Case vbNo
        GoTo Quit:
    End Select

Quit:
End Sub

Also, don't use GoTo. Ever.

I think you want this (remove ans)

VBA Code:

    If MsgBox(msg, vbYesNo) = vbYes Then

         Select Case Cells(Selection.Row, "C") '****Error on this line****
       
            Case "Customer Concern": CC
            Case "CAPA": CAPA
            Case "Interruptions": Interruptions
            Case "Supplier Non-Conformance": NCR
            Case Else: MsgBox "Please select a valid Quality Issue Type"
          
         End Select

   End If

End Sub

willow1985 · Oct 30, 2024

6StringJazzer said:

That because you put my code in an invalid location, which is described in the error message.

Rich (BB code):

    ans = MsgBox(msg, vbYesNo)

    Select Case ans
You cannot put any code here===>
         Select Case Cells(Selection.Row, "C")
      
            Case "Customer Concern": CC
            Case "CAPA": CAPA
            Case "Interruptions": Interruptions
            Case "Supplier Non-Conformance": NCR
            Case Else: MsgBox "Please select a valid Quality Issue Type"
         
         End Select
<===to here      
            Case vbNo
        GoTo Quit:
    End Select

Quit:
End Sub

Also, don't use GoTo. Ever.

I think you want this (remove ans)

VBA Code:

    If MsgBox(msg, vbYesNo) = vbYes Then

         Select Case Cells(Selection.Row, "C") '****Error on this line****
      
            Case "Customer Concern": CC
            Case "CAPA": CAPA
            Case "Interruptions": Interruptions
            Case "Supplier Non-Conformance": NCR
            Case Else: MsgBox "Please select a valid Quality Issue Type"
         
         End Select

   End If

End Sub

I do require that code at the beginning to make sure the user has filled out Columns B to T and to verify if they wish to continue before even calling another Macro. I do not see how to accomplish this if your code is first...

VBA Code:

Sub Complete_File()
'
' Complete_File Macro
'
  Dim msg As String, ans As Variant, lr As Long, Sh As Worksheet, lo As ListObject
 
  If Application.CountIf(Sheets("Q Log").Range(Cells(Selection.Row, 1), Cells(Selection.Row, 20)), "") > 0 Then
  MsgBox "Please Complete all manditory fields (Columns B to T)"
Range(Cells(Selection.Row, 25), Cells(Selection.Row, 25)).ClearContents
    Exit Sub
  End If

msg = "Your Quality File will be finalized," & vbCrLf & "" & vbCrLf & " Do you wish to continue?"

    If MsgBox(msg, vbYesNo) = vbYes Then

    Select Case ans

         Select Case Cells(Selection.Row, "C")
         
            Case "Customer Concern": CC
            Case "CAPA": CAPA
            Case "Interruptions": Interruptions
            Case "Supplier Non-Conformance": NCR
            Case Else: MsgBox "Please select a valid Quality Issue Type"
            
         End Select
         
End If

End Sub

willow1985 · Oct 30, 2024

willow1985 said:

I do require that code at the beginning to make sure the user has filled out Columns B to T and to verify if they wish to continue before even calling another Macro. I do not see how to accomplish this if your code is first...

VBA Code:

Sub Complete_File()
'
' Complete_File Macro
'
  Dim msg As String, ans As Variant, lr As Long, Sh As Worksheet, lo As ListObject
 
  If Application.CountIf(Sheets("Q Log").Range(Cells(Selection.Row, 1), Cells(Selection.Row, 20)), "") > 0 Then
  MsgBox "Please Complete all manditory fields (Columns B to T)"
Range(Cells(Selection.Row, 25), Cells(Selection.Row, 25)).ClearContents
    Exit Sub
  End If

msg = "Your Quality File will be finalized," & vbCrLf & "" & vbCrLf & " Do you wish to continue?"

    If MsgBox(msg, vbYesNo) = vbYes Then

    Select Case ans

         Select Case Cells(Selection.Row, "C")
        
            Case "Customer Concern": CC
            Case "CAPA": CAPA
            Case "Interruptions": Interruptions
            Case "Supplier Non-Conformance": NCR
            Case Else: MsgBox "Please select a valid Quality Issue Type"
           
         End Select
        
End If

End Sub

Never mind. I see the line I missed removing. Thank you very much!

6StringJazzer · Oct 30, 2024

willow1985 said:
I do require that code at the beginning to make sure the user has filled out Columns B to T and to verify if they wish to continue before even calling another Macro. I do not see how to accomplish this if your code is first...

I wrote it for you. See the code in my most recent post.

Call a macro based on text in column C (same row that is active).

willow1985

Well-known Member

Excel Facts

6StringJazzer

Well-known Member

HighAndWilder

Well-known Member

willow1985

Well-known Member

willow1985

Well-known Member

6StringJazzer

Well-known Member

willow1985

Well-known Member

willow1985

Well-known Member

6StringJazzer

Well-known Member

Similar threads

Forum statistics

Share this page

Call a macro based on text in column C (same row that is active).

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock