G
Guest
I am grateful to any help and suggestions what to do!
Here are the subs I expect to make the users able to copy and paste between
and within sheets. It doesn't work properly. Sometimes when I change som
parts, it works partly of cource, but never completly. I have tried to quit
the sloppy activate - selection habit, but
couldn't make that code work. I'm sorry.
However right now, it doesn't activate the critical copy - pasteformat
action
It should:
- Not allow draganddrop
- On every change of a range or cell an new fresh format template sheet
shall be PasteSpecial Paste:=xlPasteFormulas (xlPasteFormulas ensure to
handel merged cells from
clipboard etc).
-Not create a loop from Sub WorkSheet_change
- Keep the sheet1 protected
Please - what is wrong?
Please lay som minutes on this, and I promise to publish the solution
later -
iI have seen in the newsgroups this nut has been a big issue for years, this
kan make it right
once and for all...
/Regards
(e-mail address removed)
'SUBS ON SHEET1 ("Blad1" in the code in swedish)
Private Sub Worksheet_Deactivate()
'*************************************
' Turn on Draganddrop and CtrlV when leaving sheet
'**************************************
Application.CellDragAndDrop = True
Application.OnKey "^v"
End Sub
Private Sub Worksheet_Activate()
'*************************************
' App Enableevents tru / false to toggle because the eventsubs not shall
start a loop between and within each other
'Catch CtlV, send it to an other sub which should make all paste to
Pastespecial(xlpasteformulas)
'**************************************
Application.EnableEvents = True
Application.OnKey "^v", "FormPaste"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'*************************************
'App Enableevents tru / false to toggle because the eventsubs not shall
start a loop between and within each other
'When anything changes in sheet104 (Blad104 in swedish) it will copy formats
from a template sheet and
'paste it in the 104 sheet to keep format conditions, formats etc.
'**************************************
Application.EnableEvents = False
Blad1.Activate
Blad1.Cells.Select
Application.CutCopyMode = False
Selection.Copy
Blad104.Select
Target.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.EnableEvents = True
End Sub
IN MODULE1
'*************************************
'Make paste special Formulas only to keep format
' and NOT allow merged cells
'**************************************
Sub FormPaste()
Blad104.Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Else
Beep
MsgBox " Wrong selection or code in worksheet_change!"
End If
End Sub
Here are the subs I expect to make the users able to copy and paste between
and within sheets. It doesn't work properly. Sometimes when I change som
parts, it works partly of cource, but never completly. I have tried to quit
the sloppy activate - selection habit, but
couldn't make that code work. I'm sorry.
However right now, it doesn't activate the critical copy - pasteformat
action
It should:
- Not allow draganddrop
- On every change of a range or cell an new fresh format template sheet
shall be PasteSpecial Paste:=xlPasteFormulas (xlPasteFormulas ensure to
handel merged cells from
clipboard etc).
-Not create a loop from Sub WorkSheet_change
- Keep the sheet1 protected
Please - what is wrong?
Please lay som minutes on this, and I promise to publish the solution
later -
iI have seen in the newsgroups this nut has been a big issue for years, this
kan make it right
once and for all...
/Regards
(e-mail address removed)
'SUBS ON SHEET1 ("Blad1" in the code in swedish)
Private Sub Worksheet_Deactivate()
'*************************************
' Turn on Draganddrop and CtrlV when leaving sheet
'**************************************
Application.CellDragAndDrop = True
Application.OnKey "^v"
End Sub
Private Sub Worksheet_Activate()
'*************************************
' App Enableevents tru / false to toggle because the eventsubs not shall
start a loop between and within each other
'Catch CtlV, send it to an other sub which should make all paste to
Pastespecial(xlpasteformulas)
'**************************************
Application.EnableEvents = True
Application.OnKey "^v", "FormPaste"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'*************************************
'App Enableevents tru / false to toggle because the eventsubs not shall
start a loop between and within each other
'When anything changes in sheet104 (Blad104 in swedish) it will copy formats
from a template sheet and
'paste it in the 104 sheet to keep format conditions, formats etc.
'**************************************
Application.EnableEvents = False
Blad1.Activate
Blad1.Cells.Select
Application.CutCopyMode = False
Selection.Copy
Blad104.Select
Target.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.EnableEvents = True
End Sub
IN MODULE1
'*************************************
'Make paste special Formulas only to keep format
' and NOT allow merged cells
'**************************************
Sub FormPaste()
Blad104.Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Else
Beep
MsgBox " Wrong selection or code in worksheet_change!"
End If
End Sub