trigger event if any data in modified on ONLY the current workshee

Is there a way to trigger an event if any data in modified on ONLY the
current worksheet. If changes are made on another Worksheet, it should NOT
test TRUE so
"if ThisWorkbook.Saved = true then" does not seem to help... Also, I would
not want it to test TRUE if the cursor is moved. Only when any cell is
changed.

If (ActiveWorkSheetDataChanged) Then
Range("P4") = "Reset"
End If

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
'add your code here

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

"CRayF" <[email protected]> wrote in message
news:[email protected]...
> Is there a way to trigger an event if any data in modified on ONLY the
> current worksheet. If changes are made on another Worksheet, it should NOT
> test TRUE so
> "if ThisWorkbook.Saved = true then" does not seem to help... Also, I would
> not want it to test TRUE if the cursor is moved. Only when any cell is
> changed.
>
> If (ActiveWorkSheetDataChanged) Then
> Range("P4") = "Reset"
> End If

interesting , but cant get it to work .. could you add more info for a
newbie?

Matt

it says "invalid outside procedure"

which line? did you note the instructions where to store it?

--
HTH

Bob Phillips

"Matt" <[email protected]> wrote in message
news:[email protected]...
> it says "invalid outside procedure"
>

Hi Bob,

This Worksheet has several cells that when selected execute certain actions.
When the K1 is selected the this Worksheet has a set of cells that are
copied from the source Program Summary Worksheet Template to refresh it. (and
after answering Yes to a message).
As soon as this is done, I then set Range("N3").Value = "default".

The idea is that the first time “after” all this is copied over, as soon as
one cell is manually changes, then I’d like to set the cell value of K1 to
“Modified!”.

After cut and pasting the code you provided, this ran with no error messages
but "immediately" changed the cell K1 back to “Modified!” I suspect it has
something to do with the timing of the data being copied… any clues?

Here’s the complete set of code within this module and your sub-routine at
the bottom.
--------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim srcProgramDataInputWs As Worksheet
Dim srcProgramSummaryTemplateWs As Worksheet
Dim srcProgramSummaryWs As Worksheet
Dim srcBettingTemplateWs As Worksheet
Dim racePark As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer

Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary")
Set srcProgramSummaryWs = Sheets("ProgramSummary")
Set srcBettingTemplateWs = Sheets("@TempleteBetting")
Set srcProgramDataInputWs = Sheets("ProgramDataInput")

racePark = Left(srcProgramDataInputWs.Range("H3").Value, 3)

If Target.Address = "$A$1" Then
Dim exists As Boolean
Dim ExistingBettingWsName As Worksheet
Dim NewBettingWsName As Variant

Range("N3").Select

NewBettingWsName = Format(srcProgramDataInputWs. _
Range("F3").Value, "mm-dd ") & _
Left(srcProgramDataInputWs.Range("H3").Value, 3)

exists = False
For Each ExistingBettingWsName In ThisWorkbook.Sheets
If ExistingBettingWsName.Name = NewBettingWsName Then
exists = True
Exit For
End If
Next
If exists Then
MsgBox "Betting Worksheet for [ " & NewBettingWsName & _
" ] already exists. [RENAME] or [DELETE] that Worksheet and try
again."

Else
Dim NewBettingWs As Worksheet
Dim NewBettingWsTabColor As Variant
Dim src As Variant

If racePark = "PHX" Then NewBettingWsTabColor = 10
If racePark = "WHE" Then NewBettingWsTabColor = 46
If racePark = "WON" Then NewBettingWsTabColor = 41

Range("N3").Select

srcBettingTemplateWs.Copy before:=ActiveSheet
Set NewBettingWs = ActiveSheet
With NewBettingWs
.Name = NewBettingWsName
.Unprotect
.Tab.ColorIndex = NewBettingWsTabColor 'or replace with
index number

src = srcProgramDataInputWs.Range("B3").Value
i = 3
j = 0
Do Until src = ""
srcBettingTemplateWs.Rows("11:22").Copy .Cells((j * 12)
+ 11, 1)
i = i + 12
j = j + 1
src = srcProgramDataInputWs.Cells(i, 2).Value
Loop

.Protect
End With
End If
End If


If Target.Address = "$K$1" Then
If MsgBox("Are you sure you want to CLEAR this Worksheet?", _
vbYesNo) = vbYes Then
ActiveSheet.Unprotect
ActiveSheet.Range("N3:Q242").Formula = _
srcProgramSummaryTemplateWs.Range("N3:Q242").Formula
ActiveSheet.Protect
Range("N3").Value = "default"
Range("N3").Select
End If
End If


If Target.Address = "$B$1" Then
Dim SelectedTxtInputFile As Variant
SelectedTxtInputFile = Application.GetOpenFilename( _
"Race Program Input Files (*.txt),*.txt", , _
"Select which RACE Program to import", , False)


If SelectedTxtInputFile = "False" Then
Range("N3").Select
Else

srcProgramDataInputWs.Range("A3:H242").ClearContents

With srcProgramDataInputWs.QueryTables.Add(Connection:= _
"TEXT;" & SelectedTxtInputFile _
, Destination:=srcProgramDataInputWs.Range("A3:H242"))
.Name = "ImportProgramData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
Range("N3").Select
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
'add your code here
Range("K1").Value = "Modified"
ws_exit:
Application.EnableEvents = True
End Sub