missing macro, dumb question

Smithgall

Board Regular
Joined
May 24, 2006
Messages
68
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,054
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top