CODEWITHSUNDEEP
excelevent-handlingvba
Tom Ruiz
Tom Ruiz

Reputation: 307

Is this code wrong or I'm not using it correctly?

I was reviewing this piece of code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Column = 1 And Target.Offset(1, 0) = "" Then
        Target.Offset(-1, 0).Copy
        Target.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
    End If
End Sub

Basically what this is supposed to do is to copy the format of the cell above the one you are typing/pasting data so if you paste several cells it will start copying the format down to all the cells.

I've tested this code but most of the times I get an error '13 runtime the other thing is when it does work it only works for Column A, but for some reason the code is not stable its really flaky and take about 1.3 seconds for it to work. and it does not work when I paste the info into the cell just if I type it in which the actual purpose of the code is exactly that to force any type of data which is pasted to have the format of the cell above it

How can we make this code to copy/paste the format of the cell above the one I'm inputting data but to be applicable to all Columns not just Column A

Furthermore, is there a way to make the code run faster? If I delete anything I also get the Error '13. Also if I paste the code into an excel sheet which I already worked on it won't work at all.

Upvotes: 0

Views: 112

Answers (1)

David Zemens
David Zemens

Reputation: 53623

I mentioned the Event loop in the comment above, it's important to be mindful of that when using event handlers.

This line limits your code to Column A only, specifically the bit before the "And":

If Target.Column = 1 And Target.Offset(1, 0) = "" Then

I'm going to make those changes here, but also limit it to exclude Row 1 (you'll get an error on Target.Offset(-1,0) if you you do that in row 1).

I am also going to limit it to only work on a target of a single cell.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    If Not Target.Cells.Count = 1 Or Target.Row = 1 Then Exit Sub
    If Target.Offset(1, 0) = "" Then
        Target.Offset(-1, 0).Copy
        Target.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
    End If
    Application.EnableEvents = True
End Sub

Of course it is possible to use the event handler on a range of cells, but then we would get an error on Target.Offset(1,0) (which is really asking for the .Value of that cell, which will fail if it is a range of multiple cells.). Like I said, there's ways around it, but without actually knowing what you need, it's hard to make precise suggestion.

This might work; still limited to a single row but I think will work for multiple cells.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    Dim nextRow as Variant

    If Not Target.Rows.Count = 1 Or Target.Row = 1 Then Exit Sub

    'store the next row in an array:
    nextRow = Application.Transpose(Application.Transpose(Target.Offset(1, 0).Value)

    If Join(nextRow, "") = "" Then
        Target.Offset(-1, 0).Copy
        Target.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
    End If
    Application.EnableEvents = True
End Sub

Upvotes: 3

Related Questions