G
Guest
I am grateful to any help and suggestions what to do!
Here ar 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, but never completly:
- Not allow draganddrop
- On every change of a range or cell an new fresh format template sheet
shall
be pastespecial(xlpasteFormulas) to be able to hadle merged cells in
clipboard etc.
-Not create a loop
- Keep the sheet1 protected
Please - what is wrong? i have tried to use less selection & activate code,
but i could't make it right either.
Please lay som minutes on this, and I promise to publich the solutin later -
i have seen in the newsgroups this is a big issue, 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)
' Kopierar format från mallblad, klistrar in end. formatet
' Vid inklistring tillåts bara pastespecial
'**************************************
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 ar 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, but never completly:
- Not allow draganddrop
- On every change of a range or cell an new fresh format template sheet
shall
be pastespecial(xlpasteFormulas) to be able to hadle merged cells in
clipboard etc.
-Not create a loop
- Keep the sheet1 protected
Please - what is wrong? i have tried to use less selection & activate code,
but i could't make it right either.
Please lay som minutes on this, and I promise to publich the solutin later -
i have seen in the newsgroups this is a big issue, 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)
' Kopierar format från mallblad, klistrar in end. formatet
' Vid inklistring tillåts bara pastespecial
'**************************************
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