VBA Tab question

[robert morris]

The following code allows me to type a Name in B2 of a new worksheet in two
other woekbooks, but not in a new (third) wookbook. Simply put, when a name
is typed in B2 the Tab is named the same as B2.

Now it does not work in a third Wookbook, new Worksheet

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$B$2" Then Sh.Name = Target.Value
End Sub

Anyone see the problem?

Bob

 

[Dave Peterson]

Did you put this code into the ThisWorkbook module for every workbook that
should have this behavior.

The workbook_Sheetchange event will only fire for workbooks that have this code
in them.

 

[robert morris]

Dave, thanks for the quick reply.

Answer to your first question is No

I use Sheet 2 in each Workbook as the Master (formatted but no data) but has
all the VBA and Macros (Sheet3.MakeLinks) and copy it to a new blank w/s,
then type new NAME) in B2 which copies to the Tab.

It works with my other two Workbooks.

Bob

 

[Dave Peterson]

If you're using a single worksheet as the master, then instead of using the
Workbook_SheetChange event (which has to be under ThisWorkbook to run), you
could use the Worksheet_Change event.

This worksheet_change event goes under the worksheet that should have the
behavior.

Maybe you have both events in the Sheet module. If you do, the
workbook_sheetchange event is never fired.