protect layout / copy past with predifined lay-out settings

[APOC [T.I.M.]]

is there a simple way to "protect" the lay out of my worksheet in someway ???
I mean when I copy paste some info/text in my worksheet it's alway pasted in the default excel layout setting (arial 10) at least nearly all the time.

could I paste text by default with these settings?
Arrial Narrow 8 in the default (predifined) cell collor and no underline?
I guess I should use some vba code?
(and perhaps I can use this settings by default for all text in my worksheet by default?)
I did search but there are a lot and I mean a whole lot of topics but I still haven't found one which does what I want/need.

with kind regards

 

[APOC [T.I.M.]]

I'm trying something like this, but it's not quite working,
I guess somethings wrong with this line: ,,Dim MyDataObj As New DataObject'' (Excel 2002 SP3)

Sub CustomPaste()
 
Dim temp
 
'Ignore "TESTERtje" documents
If UCase(Left(ActiveDocument.AttachedTemplate.Name, 6)) <> "TESTERtje" And _
   LCase(ActiveDocument.AttachedTemplate.Name) <> "normal.dot" And _
   LCase(ActiveDocument.AttachedTemplate.Name) <> "normal.dotm" Then
        Selection.Paste
        'MsgBox "No TEST document, normal behaviour!", vbInformation
        Exit Sub
End If
 
If VariabeleExists("SkipCustomPaste") = True Then
    ActiveDocument.Variables("SkipCustomPaste").Delete
    Selection.Paste
    Exit Sub
End If
 
'check clipboad for data:
Dim MyDataObj As New DataObject
MyDataObj.GetFromClipboard
 
 
 
If MyDataObj.GetFormat(1) = True Then
    'Change layout pasted text into surrounding layout/format
    Selection.PasteAndFormat wdFormatSurroundingFormattingWithEmphasis
    MsgBox "Text pasted in default layout", vbInformation, "TESTERtje custom paste"
Else
    'Paste the rest, ie. images
    Options.PictureWrapType = wdWrapMergeInline
    temp = Selection.ParagraphFormat.LineSpacingRule
    Selection.ParagraphFormat.LineSpacingRule = wdLineSpaceSingle
    On Error GoTo fout:
    Selection.Paste
    Selection.TypeParagraph
    Selection.ParagraphFormat.LineSpacingRule = temp
    MsgBox "row distance is 1" & vbCrLf & vbCrLf & _
    "(or else you will probably see a partial picture)", vbInformation, "TESTERtje custom paste"
End If
 
Exit Sub
 
fout:
'clipboard empty
Selection.ParagraphFormat.LineSpacingRule = temp
MsgBox "Niets op klembord of fout bij plakken!", vbExclamation, "TESTERtje custom paste"
 
End Sub

 

[APOC [T.I.M.]]

can't get the above code working in VBA (excel 2002 sp3)

I'd ecpesially like this part to work:

If MyDataObj.GetFormat(1) = True Then
    'Change layout pasted text into surrounding layout/format
    Selection.PasteAndFormat wdFormatSurroundingFormattingWithEmphasis
    MsgBox "Text pasted in default layout", vbInformation, "TESTERtje custom paste"
Else
    'Paste the rest, ie. images
    Options.PictureWrapType = wdWrapMergeInline
    temp = Selection.ParagraphFormat.LineSpacingRule
    Selection.ParagraphFormat.LineSpacingRule = wdLineSpaceSingle
    On Error GoTo fout:
    Selection.Paste
    Selection.TypeParagraph
    Selection.ParagraphFormat.LineSpacingRule = temp
    MsgBox "row distance is 1" & vbCrLf & vbCrLf & _
    "(or else you will probably see a partial picture)", vbInformation, "TESTERtje custom paste"
End If
 
Exit Sub
 
fout:
'clipboard empty
Selection.ParagraphFormat.LineSpacingRule = temp
MsgBox "Niets op klembord of fout bij plakken!", vbExclamation, "TESTERtje custom paste"

in the above vba-code (previous post) the problem starts with this line:
Dim MyDataObj As New DataObject* <= isn't defined
Yeah! I was affraid of that, how should/could I get it to work, does anyone have an idea?

with kind regards, Tim

 

[Andrew Poulsom]

To use the DataObject variable type, you must set a reference to the Microsoft Forms 2.0 object library.

 

[APOC [T.I.M.]]

not quite sure what you exactly mean

 

[Andrew Poulsom]

not quite sure what you exactly mean

In the Visual Basic Editor click your workbook in the Project window and choose Tools|References from the menu. Check the box next to Microsoft Forms 2.0 object library and click OK.

 

[APOC [T.I.M.]]

done...

now it does seem to work in word but not in excel ???

 

[Andrew Poulsom]

The code you posted is for Word not Excel. What do you mean it doesn't work in Excel?

 

[APOC [T.I.M.]]

I'd like to use this code in Excel also, but until now I can't get it get to work in Excel.

 

[APOC [T.I.M.]]

capture the .copy / .past procedure

Isn't there in VBA some "simple" way to somehow capture the .copy / .past procedure?

ie. Selection.Paste and "mount" someway or another some Format/FormatSurrounding to it?
So that what's pasted automatically inherits the format of the surrounding cells (like one above and one below and if one is empty take the one that does have a format)

 

[APOC [T.I.M.]]

kinda hard