Worksheet Change Event-what I expect it to do

**davey11372** · 05-29-2010, 07:50 AM

I have the following code:

Sub TabName()
    ActiveSheet.Name = ActiveSheet.Range("A5")
End Sub

The above code works fine, though I have to run the macro each time manually.

I am trying to make it dynamic using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Address = "$A$5" Then

        ActiveSheet.Name = ActiveSheet.Range("A5")

   End If

End Sub

This code does not do what I expect it to do, which is rename the sheet if i change the text in cell A5.

Any help would be appreciated.

**teylyn** · 05-29-2010, 08:07 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Code tags added this time only!

Where did you put the Worksheet_Change macro? It belongs in the Sheet module, where it will do what you describe. I've run it without any problems.

**Marcol** · 05-29-2010, 08:14 AM

Ooops to late. Sorry Teylyn

Please wrap your code in code tags, before the moderators get you...

Forum rules

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

Then try

Private Sub Worksheet_Change(ByVal Target As Range)
    Target = Trim(Target)
    If Target = "" Then Exit Sub
    If Target.Address = "$A$5" Then
        Sheet1.Name = Target
    End If
End Sub

Remember because the SheetName changes you have to refer to Sheet1, Sheet2, etc
Also this will not allow the sheet name to be "", or a string of spaces.

Hope this helps