Why execute this code EXCEL will shut down and restart

[metrostar]

Please Login or Register  to view this content.

Is it a code problem? Or is it caused by other reasons

Any help would be appreciated!

[bebo021999]

You are using Worksheet_Change event, but you did not define which cell to trigger change to activate the code
I can see your code trying to input sheetname into cell A55 (or I6). It should be placed in general module and run once only.
Try below code. Hit F5 to run once.

Option Explicit
Sub sheetnameInput()
Dim sheetName As String, ws As Worksheet
For Each ws In Sheets ' loop through each sheet
    sheetName = ws.Name
    Select Case Left(sheetName, 1)
        Case "Q"
            ws.Range("A55").Value = Replace(sheetName, "Q_", "")
        Case "S"
            ws.Range("I6").Value = Replace(sheetName, "SVR_", "")
        Case Else
            ws.Range("A55").Value = ""
            ws.Range("I6").Value = ""
    End Select
Next
End Sub 

[protonLeah]

You need to disable the change event trigger...

Please Login or Register  to view this content.

[metrostar]

@protonLeah Perfect solution, you are great

@bebo021999 , Except for the beginning of S and Q, the others remain the same, and where is the code changed? For example, the name of SHEET3 has no meaning at all

[Marc L]

Is it a code problem ?

O b v i o u s l y yes ‼
'Cause when writing to any cell within this Change event you launch an infinite loop leading to a crash !
So within this event procedure just desactivate the events - via EnableEvents - before writing to any cell and activate them before the end …

Rather than creating such worksheet event within every worksheet remove all of them
then use only this VBA workbook level event so to paste only to ThisWorkbook module :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    If Sh.Name Like "Q_*" Then Sh.[A55] = Replace(Sh.Name, "Q_", "") Else _
    If Sh.Name Like "SVR_*" Then Sh.[I6] = Replace(Sh.Name, "SVR_", "") Else Sh.[A55,I6].ClearContents
    Application.EnableEvents = True
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[metrostar]

@Marc L Perfect solution, you are great