Renaming Sheets with Calculate

Not a code man, just trying to find some help

1. I have a worksheet named "Margin" where a user enters several names in cells B11 through B24 for different projects.
2. The following worksheets have a cell (A4:E4 Merged) that calculates its name from the appropriate cell in "Margin".
   (=Margin!B11 for example)
3. I would then like to have each worksheet name itself based on that value
4. If a relevant value is blank (ie no project name), the worksheet should hide itself.

This is my code (borrowed from several sources) but it is not functioning. Help would be appreciated.

• Possible issues... IsEmpty is not the correct command since the fomula returns "0" for a blank cell?
• The macro does not seem to be running at all...even just a name change is not renaming the tab?

Thanks for any pointers you have!

 Private Sub Worksheet_calculate()
   Dim Target As Range
   Set Target = Me.Range("A4")
    
    'If the target cell is empty (contents cleared) then do not change the sheet name and hide worksheet
   If IsEmpty(Target) Then Me.Visible = xlSheetVeryHidden
   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 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
        Me.Name = Target.Value
    Else
        MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _
        "Please enter a unique name for the system."
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub

Added code tags...sorry about that

Try checking for an empty string instead of using IsEmpty.

Also, move the Exit Sub so that the next part of the code actually runs if the cell isn't empty, as it is the code will never reach the later code.

If Target.Value = "" Then 
    Me.Visible = xlSheetVeryHidden
    Exit Sub
End If

If you mean the check for duplicate worksheet names then that should work with all worksheets, the code doesn't specify which sheets to look at.

The problem might be where the code is located.

I'm assuming you have this code in each worksheet, if you do I'm not sure that's where it should be.

Not even sure it should be in the Calculate event, I would have thought the Change event would be more appropriate.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Yes, its in each worksheet. I need the macro to automatically run hence I was told to put it in the worksheets as a calculate event. As to why, lets just say this will be used by people I am having to train how to use a mouse. It has to all be automatically done and Vb is new to me..so my options are limited to what I can learn in the next 48 hours.

Well i simplified the code and got rid of the

On Error Resume Next

statements as they are bad form. But the worksheet name check is still not checking any worksheets after the active worksheet.

Relevant code

'Verify that the proposed sheet name does not already exist in the workbook.
Dim bln As Boolean
  bln = False
  For Each Worksheet In Worksheets
    If Target.Value = Worksheet.Name Then bln = True
  Next Worksheet

'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
Me.Name = Target.Value
Else
MsgBox "There is already a sheet named " & Target.Value & "." & vbCrLf & _
"Please enter a unique name for the system."
Exit Sub
End If

I think the reason the check appears not to be (or isn't) checking the other sheets is because you are using the Calculate event and the order of calculation.