G
Guest
Can somebody please look at this? I give the subs with remarks and you find
two bugs in it.
Bug 1 = sub FormulaPaste , Bug 2 = 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
two bugs in it.
Bug 1 = sub FormulaPaste , Bug 2 = 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