0

I have this file that when I use it at home with my Excel 2019 everything works fine as expected, when I use it at work with Excel 365 it crashes after a few seconds. It seems to me that the problem is in the code below (in the sheet "GIACENZA MONETA"), so I'd really appreciate your help to fix it.

This code does 2 things:

  1. when the user changes a cell, it updates the date and time in cell F25
  2. when the user writes something in cell F3, it makes it upper case and adds the word "cassa " on the left (if the word "cassa" has not been written by the user).

I think there must be a conflict of some sort that makes Excel 365 crash (it just closes without any warnings this file I'm using and also other Excel files that could be open).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim stringa As String


If Target.Cells.Count > 1 Then Exit Sub

    On Error Resume Next


'this is actually the sheet and in F25 I write date and time of the last modification by the user
ThisWorkbook.Worksheets("GIACENZA MONETE").Range("F25").Value = "Aggiornamento giacenza: " & Format(Now(), "dd/mm/yyyy - hh:mm:ss")


'if the user writes something in F3, it becomes upper case and I write on the left the word "cassa " if it isn't already written by the user
    If Not Intersect(Target, Range("F3:F3")) Is Nothing Then

        Application.EnableEvents = False

        Target = UCase(Target)

        stringa = ThisWorkbook.Worksheets("GIACENZA MONETE").Range("F3").Value
        
        If InStr(1, stringa, "cassa", vbTextCompare) = 0 Then
            stringa = "CASSA " & stringa
            ThisWorkbook.Worksheets("GIACENZA MONETE").Range("F3").Value = stringa
         End If
        

        Application.EnableEvents = True

    End If

    On Error GoTo 0


End Sub

Dolphin975
  • 157
  • 2
  • 10
  • 3
    `On Error Resume Next` is not proper error handling. Remove and see if an error is being thrown. – BigBen Feb 03 '21 at 17:13
  • 2
    `If Target.Cells.Count > 1 Then Exit Sub` should be `If Target.Cells.CountLarge > 1 Then Exit Sub` – BigBen Feb 03 '21 at 17:14
  • 2
    You should disable events before you change the value in F25. – Rory Feb 03 '21 at 17:20
  • 3
    Excel 365 is not a version. It might be Excel 2019 under the hood, or it could be any version. The new 365 masks the true version. The true version can be seen under `File > Account` in the *About Excel* section. The current version could be 2008 or 2101 depending on your update channel. See here: https://learn.microsoft.com/en-us/officeupdates/current-channel – HackSlash Feb 03 '21 at 17:43

1 Answers1

1

Folding in some of the comments:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const RNG_TS As String = "F25"
    Const RNG As String = "F3"
    Dim stringa As String

    If Target.Cells.CountLarge > 1 Then Exit Sub
    'in a sheet module you can use `Me` to refer to the sheet...
    If Target.Address = Me.Range(RNG_TS).Address Then Exit Sub 'prevent re-entry
    
    Me.Range(RNG_TS).Value = "Aggiornamento giacenza: " & _
                             Format(Now(), "dd/mm/yyyy - hh:mm:ss")
    
    If Target.Address = Me.Range(RNG).Address Then
        stringa = UCase(Trim(Target.Value)) 'always worth adding Trim() for user-entered values
        If InStr(1, stringa, "cassa", vbTextCompare) = 0 Then stringa = "CASSA " & stringa
        On Error GoTo haveError
        Application.EnableEvents = False
        Target.Value = stringa
        Application.EnableEvents = True
    End If
    
    Exit Sub
    
haveError:
    'make sure this is re-enabled
    Application.EnableEvents = True

End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • The code works perfectly here at home, tomorrow I'll try at work but I'm confident it'll work. Thanks to all for the awesome inputs. What's the difference between 'If Target.Cells.Count > 1' and 'If Target.Cells.CountLarge > 1' ? – Dolphin975 Feb 03 '21 at 19:01
  • 1
    @Dolphin975 - see the "Remarks" in the [docs](https://learn.microsoft.com/en-us/office/vba/api/excel.range.countlarge). `CountLarge` is safer here, though I'm guessing you probably don't plan to change more than 2,147,483,647 cells at once... but if you did, `.Count` would throw an error. – BigBen Feb 03 '21 at 19:13
  • I confirm the code works 100% even at work! Thanks again!!! – Dolphin975 Feb 04 '21 at 16:42