Is there any way that 'filling' can be restricted to values only and not formatting, perhaps through VBA?

[Rnkhch]

Hello,

I know that it is possible to right-click and drag and then select 'fill without formatting', but is there any formatting changes can be disabled for dragging/filling, perhaps through a VBA code?

Thanks for any input!

 

[My Aswer Is This]

Give me a example of what you might want to do.

Like maybe Select Range("A1") and fill down 20 cells.

 

[Rnkhch]

Yes, that would be a perfect example.

I have made a template for my students that is fairly complex and it involves a table of 40 samples with alternating colors at each row, but also I have applied conditional formatting to highlight rows based on either errors or certain values entered. But they get lazy and instead of typing sample names, they do a lot of copy/pasting or dragging/filling and mess up with the formatting. I was able to "fix" the copy/pasting problem through a VBA code I found online at this link below (comment #27154 at the comments section at the bottom), but so far I haven't been able to prevent the dragging/filling issue, so I'm hoping you have a nice trick for that

How to restrict to paste values only (prevent formatting) in Excel?

www.extendoffice.com

 

[My Aswer Is This]

Keeping users from doing things you do not want them to do is always a big problem and probable impossible to stop.
My first computer Job was rewriting formulas users had deleted.

My only real answer was to keep backups of my files off Site.
And then at least not have to rewrite 40 formulas they had written over.

 

[Rnkhch]

Yeah, that's what I've been doing too

 

[johnnyL]

Hello,

I know that it is possible to right-click and drag and then select 'fill without formatting', but is there any formatting changes can be disabled for dragging/filling, perhaps through a VBA code?

Thanks for any input!

Here is an example:

    Range("I12").AutoFill Destination:=Range("I12:I15"), Type:=xlFillValues

 

[Rnkhch]

I just tested it like this by putting the code in my Sheet1: Range("E3").AutoFill Destination:=Range("E3:E42"), Type:=xlFillValues

And I get an error just by even clicking anywhere in the sheet: "Compile error: Invalid outside procedure", and the range "E3:E42" is highlighted in the editor.

Any idea what could be causing this?

 

[johnnyL]

Can you post your code so I can try and duplicate your problem?

 

[Rnkhch]

I only have the following two codes:

in Sheet1:

Range("E3").AutoFill Destination:=Range("E3:E42"), Type:=xlFillValues

in ThisWorkbook:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Not Application.CutCopyMode = False Then
Target.PasteSpecial xlPasteValues
Application.CutCopyMode = True
End If
End Sub

 

[My Aswer Is This]

This script works for me:
Range("E3").AutoFill Destination:=Range("E3:E42"), Type:=xlFillValues

But your other script in the sheet:

Is Probable causing the problem.
What is it supposed to be doing?