Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_change event | Excel Discussion (Misc queries) | |||
Worksheet_change event. | Excel Programming | |||
Worksheet_change event | Excel Programming | |||
Worksheet_Change Event | Excel Programming | |||
Worksheet_Change Event | Excel Programming |