missing macro, dumb question

[Smithgall]

Ok i have avoided asking this because im sure it will reveal my ignorance but i have a problem.

I have asked on this board how to name a tab using a value in a cell. i got several great responses. My problem is that everytime i copy the code into my macro the macro them does not appear in my list.

Quite frankly i do not know how to open the vba editor and create code that will then appear in the macro dialogue box. I typically go to tools, record a macro, stop recording and then edit that macro by copying over that code with whatever i need. i know that seems silly but it works. I then go to the form toolbar creat a button and then assign the button to the macro. typically this works fine. however if i use the code below and then go to assign the button the macros is missing and cannot be found. What gives? i dont expect anyone to give me a full class on whats going on but maybe point me in teh right direction of what i dont seem to understand.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
ActiveSheet.Name = Mid(Target.Text, 1, 31)
End If
End Sub

 

[Smitty]

Private subs (by the nature of being Private) won't show up in the Macro list.

Neither will Sheet level events (which a Sheet Change event is) or workbook level events.

That particular code needs to go in the worksheet specific module. Just open the VBE (ALT+F11) then double-click the module in question to open it and paste the code in.

That code runs automatically whenever Cell A1 is changed, not by calling it as with a standard macro.

HTH,

Smitty

 

[Cbrine]

Smithgall,
The above code is referred to as event code. It's not meant to be run off of a button, but to execute when a certian action occurs. In this case when something is changed on the worksheet. Here's how to implement.

1. Press Alt-F11
2. Select Project Explorer from the VIEW menu option.
3. Double click on the worksheet you want the code to run on.
4. Paste the code to the code window that appears.
5. Save
6. Goto you excel workbook and make sure you exit design mode.

Now try typing in cell A1. It should change the worksheet tab to that value.

HTH
Cal

PS- The code you have does little error checking so it will fail if invalid characters are used.

Here's some code with more robust error checking.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
If Not (Intersect(Target, Range("A1")) Is Nothing) Then

    If Range("A1").Value = "" Then Exit Sub
    ActiveSheet.Name = Range("A1").Value
End If

Exit Sub

ErrHandler:
Select Case Err.Number

Case 1004
    MsgBox "Please Enter a Valid Name"
    Range("A1") = ""

Case Else
    MsgBox Err.Number & " " & Err.Description
    
End Select
End Sub