How to preserve current selection after appl.events carry out an copy-pastespecial action ?

Nobody_at_all.se
Date: 05/11/04 

Date: Tue, 11 May 2004 15:38:34 GMT

Hi, I have used advices from the group but still don't get it right. I'm
sure a lot of us need this code when it is o.k. and promiose to post it, if
I get advice to fix it! . I need to keep format and conditions in a ***
even when user paste etc. ( I use a *** template to get it right again
with PasteSpecial xlPasteFormats)

How should I preserver the current selected area when worksheet_change and
calculate take place? They activate an select - copy - PasteSpecial(Format)
action that redefine the selected area?

"RngSel "
The cause of the problem seem to be that the debugger claims my "RngSel "
is not a defined object or something. Whork*** Change has a "target"
defined as range witin the function, but how to define the selected range
for the other application.events ?

CODE IN ***:

Private Sub Worksheet_Deactivate()
'*************************************
' Turn on drag and drop and Contrl-V
' and enable events, when leaving the ***
'**************************************
    Application.CellDragAndDrop = True
    Application.OnKey "^v"
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Activate()
'*************************************
' Disable draganddrop, turn protection off and on
' Call FormatTemp Sub to ensure right format in ***
' Call FormulaPaste Sub to make Ctrl-V to only make Pastespecial Formulas
' Deliver selection as "target" as Range to callsubs (seem to be the
problem..??)
'**************************************
Dim RngSel As Range
RngSel = Application.Selection
    Application.CellDragAndDrop = False

Application.EnableEvents = False
Application.OnKey "^v", "FormulaPaste"

    Call FormatTemp

RngSel.Activate

    Active***.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
    Active***.EnableSelection = xlUnlockedCells

Application.EnableEvents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

'*************************************
' Recieve a range from event to restore its format
' When ranges and cells are changed it copy paste formats from ***
template
' Causes Loop with itself if appl.event not disabled (?)
' This event doesn't occur when cells change during a recalculation.
' Occurs when cells on the work*** are changed by the user or by an
external link.
' Target = The changed range. Can be more than one cell.
' Use the Calculate event to trap a *** recalculation.
'**************************************
Application.EnableEvents = False
 Dim RngSel As Range
RngSel = Target
 Call FormatTemp
RngSel.Activate
    Application.EnableEvents = True
 End Sub

Private Sub Worksheet_Calculate()
'*************************************
' When ranges and cells are changed it copy paste formats from ***
template
' Causes Loop with itself if appl.event not disabled (?)
'**************************************
Application.EnableEvents = False
 Dim RngSel As Range
RngSel = Application.Selection
 Call FormatTemp
RngSel.Activate
    Application.EnableEvents = True
 End Sub

CODE IN MODULE1:

Sub FormulaPaste()
'*************************************
' Demand a selection
' Causes Loop with ActiveSheet_Change if appl.event not disabled
' Make PasteSpecial - Formulas
'*************************************
Application.EnableEvents = False

     With Selection
       .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
       SkipBlanks:=False, Transpose:=False
     End With

     Call FormatTemp ' THE SUB RIGHT BELOW THIS

Application.EnableEvents = True

End Sub

Sub FormatTemp()
'*************************************
' Demand a original selection defined as target
'Copy paste formats from *** template "Blad200" with
' correct formats and format conditions
'emand a selection as target
' Causes Loop with ActiveSheet_Change if appl.event not disabled
'**************************************
Dim RngSel As Range
Application.EnableEvents = False
Active***.Unprotect
        Blad200.Cells.Copy

    Active***.Cells.Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

   Active***.Protect AllowFormattingCells:=False
   Active***.EnableSelection = xlUnlockedCells

    Application.EnableEvents = True
End Sub