Event: Workbook_Change or Worksheet_Change
 

I am attempting to have some code executed whenever any row in column
"S" contains the letter "B". In other words, I would like the code to
run whenever the user enters a "B" in any row in column "S".

Nothing is happening when I enter a "B" in a random row in column "S"
to the best of my knowledge.

Promise, I have not exited the "Design Mode". Besides, my other macros
are running fine.

Note: I also attempted using worksheet_change event (nothing happened
still). A couple variations in code I have tried are below.
-----------------------------------------------------------------------------------
Private Sub Workbook_Change(ByVal Target As Range)

If Target.Column < 19 Then Exit Sub
If Cells(Target.Row, Target.Column).Value = "B" Then
MsgBox "It works...Not"
End If

End Sub
-----------------------------------------------------------------------------------
Also tried this among many other variations...
-----------------------------------------------------------------------------------
Private Sub Workbook_Change(ByVal Target As Range)

If Target.Column = 19 Then
If Cells(Target.Row, Target.Column).Value = "B" Then
MsgBox "It works...JK"
Else
Exit Sub
End If
End If

End Sub
-----------------------------------------------------------------------------------

Thoughts as to why the code doesn't seem to be doing anything?

Event: Workbook_Change or Worksheet_Change
 

Works fine for me. I added the UCase function just in case users enter a
small b instead of a Capital B.

'/---------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo err_Sub

If Target.Column < 19 Then Exit Sub

If UCase(Cells(Target.Row, Target.Column).Value) = "B" Then
MsgBox "It works."
End If

End Sub
'/---------------------------------------------------------------

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Han" wrote:

I am attempting to have some code executed whenever any row in column
"S" contains the letter "B". In other words, I would like the code to
run whenever the user enters a "B" in any row in column "S".

Nothing is happening when I enter a "B" in a random row in column "S"
to the best of my knowledge.

Promise, I have not exited the "Design Mode". Besides, my other macros
are running fine.

Note: I also attempted using worksheet_change event (nothing happened
still). A couple variations in code I have tried are below.
-----------------------------------------------------------------------------------
Private Sub Workbook_Change(ByVal Target As Range)

If Target.Column < 19 Then Exit Sub
If Cells(Target.Row, Target.Column).Value = "B" Then
MsgBox "It works...Not"
End If

End Sub
-----------------------------------------------------------------------------------
Also tried this among many other variations...
-----------------------------------------------------------------------------------
Private Sub Workbook_Change(ByVal Target As Range)

If Target.Column = 19 Then
If Cells(Target.Row, Target.Column).Value = "B" Then
MsgBox "It works...JK"
Else
Exit Sub
End If
End If

End Sub
-----------------------------------------------------------------------------------

Thoughts as to why the code doesn't seem to be doing anything?

Event: Workbook_Change or Worksheet_Change
 

There is not Change event for a workbook. That is why your code
doesn't run. You need to use the SheetChange event:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target
As Range)
' your code here
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Han" wrote in message
oups.com...
I am attempting to have some code executed whenever any row in
column
"S" contains the letter "B". In other words, I would like the
code to
run whenever the user enters a "B" in any row in column "S".

Nothing is happening when I enter a "B" in a random row in
column "S"
to the best of my knowledge.

Promise, I have not exited the "Design Mode". Besides, my
other macros
are running fine.

Note: I also attempted using worksheet_change event (nothing
happened
still). A couple variations in code I have tried are below.
-----------------------------------------------------------------------------------
Private Sub Workbook_Change(ByVal Target As Range)

If Target.Column < 19 Then Exit Sub
If Cells(Target.Row, Target.Column).Value = "B" Then
MsgBox "It works...Not"
End If

End Sub
-----------------------------------------------------------------------------------
Also tried this among many other variations...
-----------------------------------------------------------------------------------
Private Sub Workbook_Change(ByVal Target As Range)

If Target.Column = 19 Then
If Cells(Target.Row, Target.Column).Value = "B" Then
MsgBox "It works...JK"
Else
Exit Sub
End If
End If

End Sub
-----------------------------------------------------------------------------------

Thoughts as to why the code doesn't seem to be doing anything?

Event: Workbook_Change or Worksheet_Change
 

Thank you both for your input. I forgot that it's not Workbook_Change,
but Workbook_SheetChange. Although, it does not explain why
Worksheet_Change didn't do anything. No matter, I will be using the
Workbook_SheetChange event.
--------------------------------------------------------------------
Just in case someone reads this later on, the parameter "ByVal Sh As
Object" is mandatory for the Workbook_SheetChange event. I receive a
compile error if it is not included. My final working code looks like
this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

If Target.Column < 19 Then Exit Sub
If Cells(Target.Row, Target.Column).Value = "B" Then
MsgBox "OMG! It works!"
End If

End Sub

Once again, thank you for you input!

Event: Workbook_Change or Worksheet_Change
 

Worksheet_Change should work fine.
With WorkBook_SheetChange the code runs every time you change ANY worksheet.

Don.
"Han" wrote in message
ups.com...
Thank you both for your input. I forgot that it's not Workbook_Change,
but Workbook_SheetChange. Although, it does not explain why
Worksheet_Change didn't do anything. No matter, I will be using the
Workbook_SheetChange event.
--------------------------------------------------------------------
Just in case someone reads this later on, the parameter "ByVal Sh As
Object" is mandatory for the Workbook_SheetChange event. I receive a
compile error if it is not included. My final working code looks like
this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

If Target.Column < 19 Then Exit Sub
If Cells(Target.Row, Target.Column).Value = "B" Then
MsgBox "OMG! It works!"
End If

End Sub

Once again, thank you for you input!