VBA TIPS
Filling only the values, without filling the formats of cells.

Normally, AutoFill function would fill in cells with common series. Of course the format of the cells are included.



What if you'd like to fill only the values, without filling the formats of cells?



Here is a sample code with using Undo Method. This program would fill only the values, without filling the formats of cells.

When you run ChangeEventOn, a public variable named ChangeEventFlg has a value TRUE then this custom events works, when you run ChangeEventOff a public variable named ChangeEventFlg has a value FALSE then it makes the AutoFill function default again.

Example

Place the following in a standard module.

Option Explicit

Public ChangeEventFlg As Boolean


Sub ChangeEventOn()
    ChangeEventFlg = True
End Sub


Sub ChangeEventOff()
    ChangeEventFlg = False
End Sub


Place the following in the ThisWorkbook module.

Option Explicit


Private Sub Workbook_SheetChange(ByVal Sh As ObjectByVal Target As Range)
    On Error GoTo ErrLine
    If ChangeEventFlg = False Then Exit Sub
    Dim Formerdata As Variant, ChangedData As Variant
    With Application
        .EnableEvents = False
        .Undo
        Formerdata = Target.Formula
        .Undo
        ChangedData = Target.Formula
        .Undo
        Target.Formula = ChangedData
ErrLine:
        .EnableEvents = True
    End With
End Sub

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo