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

[Guest]

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 sheet
even when user paste etc. ( I use a sheet 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. Whorksheet Change has a "target"
defined as range witin the function, but how to define the selected range
for the other application.events ?


CODE IN SHEET:

Private Sub Worksheet_Deactivate()
'*************************************
' Turn on drag and drop and Contrl-V
' and enable events, when leaving the sheet
'**************************************
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 sheet
' 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

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.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 sheet
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 worksheet 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 sheet 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 sheet
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 sheet 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
ActiveSheet.Unprotect
Blad200.Cells.Copy

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

ActiveSheet.Protect AllowFormattingCells:=False
ActiveSheet.EnableSelection = xlUnlockedCells

Application.EnableEvents = True
End Sub

 

[Anders S]

I haven't studied your code in detail, but for starters you must *Set* RngSel as
a range is an object:

Set RngSel = Application.Selection

HTH
Anders Silven