2 Bugs: PasteSpecial syntax & EnableEvent in ws_deactivate

[Guest]

Can somebody please look at this? Look at the subs remarks and you find
two bugs mentioned in it.
Bug 1 = In sub FormulaPaste , Bug 2 = In sub Worksheet_Deactivate

Bug #1: When I move out from the sheet and in again, I always get an error
on the row ".PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone" Why?
It works fine afterwards if I resume the debbuger mode and copy - paste from
and to cells within the same sheet !!!

When I hold the marker over the code at " With selection" I see the text of
the content into the selected/activated cell. Should it not idication the
range?
If I mark a range I thought it should show the string of the active cell in
the selected range, but I see nothing. Indication something wrong?


SUBS IN MODULE1:
Sub FormulaPaste()
'*************************************
' Trigger from Application.OnKey "^v", "FormulaPaste"
' ---> BUG # 1 " : PasteSpecial Paste:=xlPasteFormulas,
Operation:=xlNone" Why?
' BUG Don't appear if I resume debugger and copy - paste witin the
sheet,
' but if I leave and activate the sheet again, the BUG is back.
' Demand a selection
' Causes Loop with ActiveSheet_Change if appl.event not disabled
' SHOULD paste the formulas from clipboard
'*************************************
Application.EnableEvents = False

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

Call FormatTemp

Application.EnableEvents = True

End Sub


Sub FormatTemp()
'*************************************
' Demand a original selection defined as target
'Copy paste formats from sheet template with
'right formats and format conditions
' 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


SUBS IN SHEET:
Bug 2#: The Worksheet_Deactivate seem not to be activated when I leave
the sheet. If I paste something in an other sheet, the OnKey "V" macro
starts. It should'nt because I have Application.OnKey "^v" in this sub...

Private Sub Worksheet_Deactivate()
'*************************************
' BUG # 2 - seem not to be activated when leaving the sheet.
' Turn DragandDrop on & Off leaving the sheets
' Restore Ctrl V funktion
' Ensure enableEvents to be active
'**************************************
Application.CellDragAndDrop = True
Application.OnKey "^v"
Application.EnableEvents = True
End Sub


Private Sub Worksheet_Activate()

'*************************************
' Disable draganddrop, turn protection off and on
' Call Sub FormatTemp to ensure right format in sheet
' Call Sub FormulaPaste when Ctrl-V - only Paste Formulas allowed
' Deliver selection as "target" as Range to Sub PasteFormula
'**************************************
Dim RngSel As Range
Set 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
Set RngSel = Target
Call FormatTemp
RngSel.Activate
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Calculate()
'*************************************
' Identical to ws_change
'**************************************
Application.EnableEvents = False
Dim RngSel As Range
Set RngSel = Application.Selection
Call FormatTemp
RngSel.Activate
Application.EnableEvents = True
End Sub

 

[Tom Ogilvy]

With all the activities you have going on, for Bug 1, I suspect you have
cleared the clipboard and therefore have nothing to paste.

For Bug 2, again, with all the activities going on it would be difficult to
diagnose what the source of your problem is. I might be a matter of which
event fires first - perhaps your event is getting reversed by an event
firing after it. You could put in some message boxes in your code and then
check out the order of the events.

 

[Guest]

Thank you. Yes it is a bit messy to cover all activities. God point with
msgbox. Could you or any other advice me what actions do clear the
clipboard?
Maybe:

Application. EnableEvent ?
Application.CellDragAndDrop ?
Application.OnKey "^v", "FormulaPaste" ?

I run all three of them in sub worksheet_activate...mayby this is why I
can't keep clipboard when changing sheets...? Any more commands to be
careful with?

By the way, I now use Chip Pearsons "Sub SaveLocation" (Great!, thank you )
to retrieve selections after relocations and I
work on saving the selection.copy from antoher sheet temporary somewere,
because it seems like the clipboard
become empty when the Sub worksheet_activation run.

/Regards