Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Tab question
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Tab question
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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Tab question
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" wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Tab question
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. robert morris wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question | New Users to Excel | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions | |||
Last question! | Excel Discussion (Misc queries) | |||
Another Name question | Excel Discussion (Misc queries) |