Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Workbook_SheetChange for single column
What is the simplest way to use the "Private Sub Workbook_SheetChange(ByVal
Sh As Object, ByVal Target As Range)" for only one column? Or maybe this isn't the choice I want to use. I want to run a macro only if data has been added to a cell in column "C". Any help would be appreciated. Thanks in advance, Brad K |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Workbook_SheetChange for single column
Try this:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "Sheet1" Then If Not Intersect(Target, Columns("C")) Is Nothing Then Beep End If End If End Sub Be aware that each Worksheet has it's own events for handling Change So instead of receiving the Workbook event and working out which sheet it's for, you could just receive the Worksheet event. -- Rob van Gelder - http://www.vangelder.co.nz/excel "Brad K." wrote in message ... What is the simplest way to use the "Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)" for only one column? Or maybe this isn't the choice I want to use. I want to run a macro only if data has been added to a cell in column "C". Any help would be appreciated. Thanks in advance, Brad K |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Workbook_SheetChange for single column
If you want to trigger this event for changes in col C of ALL sheets then use
the Workbook_SheetChange event . If you want to trigger this event for changes in col C of JUST CERTAIN sheets then use the Worksheet_Change event . Here's the code: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count1 then Exit Sub If Not Intersect(Target, Range("C:C")) Is Nothing Then Application.EnableEvents=False 'this prevents continuous looping '''' Call your macro - placed in a Standard module '''' or add your code directly here End If Application.EnableEvents=True 'This is required, unlike ScreenUpdating End Sub "Brad K." wrote: What is the simplest way to use the "Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)" for only one column? Or maybe this isn't the choice I want to use. I want to run a macro only if data has been added to a cell in column "C". Any help would be appreciated. Thanks in advance, Brad K |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
From single cell variables to a single column serie | New Users to Excel | |||
Return Single Row of Numeric Data to Single Column | Excel Worksheet Functions | |||
Workbook_sheetChange event | Excel Programming | |||
workbook_sheetchange | Excel Programming | |||
Workbook_SheetChange will not fire | Excel Programming |