Assistance with macro triggered by text in selected cell

[willow1985]

I currently have this code that calls a macro when you select open on column V for the row that is selected.
I would like to modify this to call another macro called Date_Stamp when the cell states "closed"
However every time I try and modify the below code it also calls "Open" as well

Note: There are cells above and below that may say "Open" in column V so that may be the issue....

Private Sub Worksheet_Change(ByVal target As Range)
   If target.CountLarge > 1 Then Exit Sub
   If Not Intersect(target, Range("I:I")) Is Nothing Then
      If target <> "" Then Call Issued_To
   ElseIf Not Intersect(target, Range("V:V")) Is Nothing Then
      If target.Text = "Open" Then Call Complete_File
   End If
End Sub

 

[DanteAmor]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("I:I")) Is Nothing Then
      If Target.Value <> "" Then Call Issued_To
   ElseIf Not Intersect(Target, Range("V:V")) Is Nothing Then
      Select Case Target.Text
        Case "Open":    Call Complete_File
        Case "Closed":  Call Date_Stamp
      End Select
   End If
End Sub

 

[willow1985]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("I:I")) Is Nothing Then
      If Target.Value <> "" Then Call Issued_To
   ElseIf Not Intersect(Target, Range("V:V")) Is Nothing Then
      Select Case Target.Text
        Case "Open":    Call Complete_File
        Case "Closed":  Call Date_Stamp
      End Select
   End If
End Sub

This is what I tried but it still calls "Complete_File" even though the cell that is activated was switched to Closed

 

[DanteAmor]

This is what I tried but it still calls "Complete_File" even though the cell that is activated was switched to Closed

Are you typing the word "Closed" with the first letter in uppercase and the rest in lowercase?

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim bi As Boolean, bv As Boolean
  If Target.CountLarge > 1 Then Exit Sub
  bi = Not Intersect(Target, Range("I:I")) Is Nothing
  bv = Not Intersect(Target, Range("V:V")) Is Nothing
  Select Case True
    Case bi And Target.Text <> "":      Call Issued_To
    Case bv And Target.Text = "Open":   Call Complete_File
    Case bv And Target.Text = "Closed": Call Date_Stamp
  End Select
End Sub

 

[willow1985]

Are you typing the word "Closed" with the first letter in uppercase and the rest in lowercase?

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim bi As Boolean, bv As Boolean
  If Target.CountLarge > 1 Then Exit Sub
  bi = Not Intersect(Target, Range("I:I")) Is Nothing
  bv = Not Intersect(Target, Range("V:V")) Is Nothing
  Select Case True
    Case bi And Target.Text <> "":      Call Issued_To
    Case bv And Target.Text = "Open":   Call Complete_File
    Case bv And Target.Text = "Closed": Call Date_Stamp
  End Select
End Sub

Closed is being selected from a drop down list and it is first letter in uppercase and the rest in lowercase: "Closed".

It is still calling "Date_Stamp" AND "Complete_File" When I select "Closed" at V902 when testing

CAPA Log.xlsm
	U	V	W
899		Closed
900	Past Due	Open
901		Open
902		Open
903		Open
904		Open
CAPA Database

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:X904	Expression	=$V3="Open"	text	NO
A3:X904	Expression	=$V3="Cancelled"	text	NO
A3:X904	Expression	=$V3="Closed"	text	NO

 

[DanteAmor]

It is still calling "Date_Stamp" AND "Complete_File" When I select "Closed" at V902 when testing

Are you copying my entire macro?

You can put your other macros to see what they have.


What you are commenting on is not possible with my macro.
Do you have other codes on the sheet?

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[willow1985]

Are you copying my entire macro?

You can put your other macros to see what they have.


What you are commenting on is not possible with my macro.
Do you have other codes on the sheet?

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

I apologize, I found out why it was calling the other file. The issue was not with your code but one of the codes it was calling. Apparently I had left record macro on and it added that as a line and I did not notice. Thank you so much for your help, the code works perfect