Switch from Worksheet_Change to Worksheet_Calculate

[Mike C]

Any idea how I could switch the following code from a worksheet_change event to worksheet_calculate?

Private Sub Worksheet_Change(ByVal Target As Range)
    'Specify the target cell whose entry shall be the sheet tab name.
    If Target.Address <> "$A$1" Then Exit Sub
        'If the target cell is empty (contents cleared) then do not change the shet name
    If IsEmpty(Target) Then Exit Sub
 
    'If the length of the target cell's entry is greater than 31 characters, disallow the entry.
    If Len(Target.Value) > 31 Then
        MsgBox "Worksheet tab names cannot be greater than 31 characters in length." & vbCrLf & _
        "You entered " & Target.Value & ", which has " & Len(Target.Value) & " characters.", , "Keep it under 31 characters"
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If
 
    'Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
    'Verify that none of these characters are present in the cell's entry.
    Dim IllegalCharacter(1 To 7) As String, i As Integer
    IllegalCharacter(1) = "/"
    IllegalCharacter(2) = "\"
    IllegalCharacter(3) = "["
    IllegalCharacter(4) = "]"
    IllegalCharacter(5) = "*"
    IllegalCharacter(6) = "?"
    IllegalCharacter(7) = ":"
    For i = 1 To 7
        If InStr(Target.Value, (IllegalCharacter(i))) > 0 Then
            MsgBox "You used a character that violates sheet naming rules." & vbCrLf & vbCrLf & _
            "Please re-enter a sheet name without the ''" & IllegalCharacter(i) & "'' character.", 48, "Not a possible sheet name !!"
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
            Exit Sub
        End If
    Next i
 
    'Verify that the proposed sheet name does not already exist in the workbook.
    Dim strSheetName As String, wks As Worksheet, bln As Boolean
    strSheetName = Trim(Target.Value)
    On Error Resume Next
    Set wks = ActiveWorkbook.Worksheets(strSheetName)
    On Error Resume Next
    If Not wks Is Nothing Then
        bln = True
    Else
        bln = False
        Err.Clear
    End If
 
    'If the worksheet name does not already exist, name the active sheet as the target cell value.
    'Otherwise, advise the user that duplicate sheet names are not allowed.
    If bln = False Then
        ActiveSheet.Name = strSheetName
    Else
        MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _
        "Please enter a unique name for this sheet."
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
    End If
 
End Sub

 

[Luke M]

At the beginning of the Sub, I'd add these lines:
Dim Target as Range
Set Target = Me.Range("A1")

Then, you won't have to change anything else. You could remove the line at beginning checking to make sure Target is the correct cell, since that is no longer needed.

 

[NARAYANK991]

Hi Mike ,

Can you clarify as to why you would want to do that ?

The Worksheet_Change event procedure is triggered by any change by a user in a worksheet cell anywhere in the worksheet ; the Worksheet_Change event procedure checks to see whether the change has taken place in a cell of interest , executing the procedure if this is so , exiting otherwise.

The Worksheet_Calculate event procedure does not have any parameters ; it is triggered by any change anywhere in the workbook by which Excel decides a recalculation is necessary.

If your actions leading to the procedure are going to remain the same ( data entry in cell A1 ) , or the code within the procedure is going to remain substantially the same , then I would not advise a change from Worksheet_Change to Worksheet_Calculate.

Narayan

 

[Mike C]

Thanks Narayan. The reason for the needed switch was to allow for the code to pickup on changes to a cell that came by the way of a calculation. The target address is a locked cell in a protected workbook and I needed the code to fire when the result from the formula in the cell changed. FYI, I ended up abandoning this approach and went with a button to accomplish the code as I believe this code was causing an overstack error.

Mike