CODEWITHSUNDEEP
vbaexcel
Ivan
Ivan

Reputation: 31

Detect whether a cell has been changed by user or macro

I have a spreadsheet where users paste numbers, due to the length of these numbers (and the fact that we don't need to spreadsheet to carry out any computations with them) we want them to be formatted as text, otherwise they appear in scientific format i.e. 1.12345E+13 instead of 12345678912345

It is not possible to adjust/modify the data source the numbers are being copied from.

I'm using Private Sub Workbook_SheetChange do detect if a cell in the relevant range has been changed, and I then format the range to text with 

ThisWorkbook.Sheets("Sheet1").Columns("B").NumberFormat = "@"

Unfortunately on Excel 2007 whether you do this manually in Excel or via a marco, the number still appears as 1.12345E+13 unless you click into the cell and press enter.

I can get round this by applying: 

With rng
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
End With

but when I do this I end up with an infinite loop, as the Private Sub Workbook_SheetChange detects the cell has been changed and goes round the loop again.

If I could somehow work out whether the cell has been changed manually by the user or by the macro, this would be easily fixed. The macro is in ThisWorkbook. I've tried using Application.Activesheet instead of ThisWorkbook.Sheets but it didn't make any difference.

If alternatively there's an easier/better way to fix numbers being displayed as 1.12345E+13 even after I've re-formatted the cell I'd love to know about it.

Thank you.

Upvotes: 3

Views: 1190

Answers (2)

Tim Williams
Tim Williams

Reputation: 166306

Here's a full example, including handling Target ranges of >1 cell:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim c As Range

    Application.EnableEvents = False

    On Error GoTo haveError
    For Each c In Application.Intersect(Target, Sh.UsedRange).Cells
        With c
            If IsNumeric(.Value) Then
                .NumberFormat = "@"
                .Value = CStr(.Value)
            End If
        End With
    Next c

haveError:
    'Make sure to re-enable events!
    Application.EnableEvents = True
End Sub

Upvotes: 5

SierraOscar
SierraOscar

Reputation: 17637

but when I do this I end up with an infinite loop, as the Private Sub Workbook_SheetChange detects the cell has been changed and goes round the loop again.

That's because you need to disable application events from automatically firing.

Private Sub Workbook_SheetChange(ByVal Sh As Worksheet, ByVal Target As Range)

    Application.EnableEvents = False  '// Stop events automatically firing

        With rng
            .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
        End With

    Application.EnableEvents = True   '// Re-enable events for next time

End Sub

Because you've disabled the events, it can't trigger itself again when you change the value of the cell. Once the code has completed you can re-enable the events to ensure that it fires the next time it is required.

For what it's worth, don't beat yourself up about it - this is an extremely common pitfall when people start working with event procedures in .

Upvotes: 5

Related Questions