Can I stop code in a Worksheet_Change event with a Worbook_Deactivate event code?

[Ronaldo]

Hi,
(A new attempt - this is a new thread from the one yesterday, thank you Bob
and Frank for your suggestions)

If I got code running at Worksheet_Change(ByVal Target As Range) event and
if I drag- and drop to another worksheet, the Target is in the
wrong sheet and workbook. How can I recognize this type of event and stop
the Worksheet_Change code from executing and in these particular cases run
an other procedure instead? I know Workbook_Deactivate event, but can't find
out how it help me in this.

For you who have the interest, I enclose my desciption of the case below, as
I described it in a earlier thread yesterady.

- Regards -

----------------------------------

Hi, thank you both for the reply. Here is a background reasons. Please note
that I still need the help as asked in "Possible Solution" here below.

REASON
With every change event in some sheets, I do by code a number of
modifications both in the
active sheet (trigger = worksheet_change) and in a couple of other sheets.
When I do a DRAG AND DROP from the sheets to an other workbook, the
activesheet is the target sheet in the new workbook.

If I use some events like worksheet_deactivate in ThisWorkbook module, I
can't see how I will be able to use my current procedures that is built on
Worksheet_Change(ByVal Target As Range) .

There is also two more specific applications: one use ActiveCell and I
havn't created it myself (contributor is Jim Recht) and feel a bit afraid
of starting to mess with it. This I had to change to work with a Range/Cells
instead of ActiveCell as now. It's a procedure that correct the row height
when the Target is Merged cells: SEE "AutoFitMergedCellRowHeight" below.

The other is a commonly used procedure I use to be able to restore selected
range and activated cell after I run a procedure that has to copy paste
(can't do that another way, Excel is the limit) cell formats. In the
beginning of the trigger event "Worksheet_Change" I send the locations to
the static variables by just Call "SetSaveLoc" and pick them up in the end
of the procedure with Call "GetSaveLoc". SEE the three subs SetSaveLoc,
GetSaveLoc & SaveLocation. When pasting outside the Workbook, it Set the
locations of the new Workbook instead.

There might be workarounds, but in total I thought that it might be easier
to define the worksheets and put a little trigger in the worksheet_Change
event to act if all of a sudden the Target is in wrong woorksheet.

POSSIBLE SOLUTION
Actually, maybe I should put the trigger in the "SaveLocation" procedure to
check if it is the right workbook ("Wb") - but I still need to compare with
a list of "allowed worksheets" in my woorkbook, doesn't I? It is 10
worksheets - isn't looping through arrays the best thing to do? I like other
ways of course - I have lived my life this far avoiding arrays...


- Regards -


REFERENCE SUBS:
Public Sub AutoFitMergedCellRowHeight()
'Used in WS_Change in Sheet 1 to 8
'*********************************
Debug.Print "Grund AutoFitMergedCellRowHeight"
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single

If ActiveCell.MergeCells Then
Application.ActiveCell.EntireRow.AutoFit

With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + _
MergedCellRgWidth
Next

.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.Cells.Locked = False
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

Public Sub SetSaveLoc()
'*************************
SaveLocation (False)
End Sub

Public Sub GetSaveLoc()
'*************************
SaveLocation (True)
End Sub

Public Sub SaveLocation(ReturnToLoc As Boolean)
' When (True) it "Set in memory" WB, WS, R
' When (False)it Activate workbook, worksheet, range
'**************************************
Static Wb As Workbook
Static ws As Worksheet
Static R As Range

If ReturnToLoc = False Then
Set Wb = ActiveWorkbook
Set ws = ActiveSheet
Set R = Selection
Else
Wb.Activate
ws.Activate
R.Select
End If
Exit Sub

End Sub