| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Code not working on Shared Workbook
I have the following code in a workbook. It works fine when the worksheets
inside the workbook are protected but not when the workbook is shared. Is there any way to make this work on the shared workbook? Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range Dim Cross1 As Range Dim Cross2 As Range Set Cross1 = Intersect(Target, Range(Cells(2, 6), Cells(30, 6))) Set Cross2 = Intersect(Target, Range(Cells(2, 6), Cells(30, 6))) If Not Cross1 Is Nothing Or Not Cross2 Is Nothing Then For Each Target In Sh.Range(Cells(2, 6), Cells(30, 6)) If ((Cross1.Value = "D") And (Cross2.Value2 < "C")) Then Sh.Tab.ColorIndex = 3 ElseIf Cross2.Value2 = "C" Then Sh.Tab.ColorIndex = 5 Else Sh.Tab.ColorIndex = xlColorIndexNone End If Next End If |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA Code not working on Shared Workbook
There are lots of features that are disabled in shared workbooks.
When I share a workbook and then try to change the sheet tab color, I see that it's disabled. Maybe you can use a different indicator--something (prefix/suffix) in the sheet name??? KC_Cheer_Coach wrote: I have the following code in a workbook. It works fine when the worksheets inside the workbook are protected but not when the workbook is shared. Is there any way to make this work on the shared workbook? Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range Dim Cross1 As Range Dim Cross2 As Range Set Cross1 = Intersect(Target, Range(Cells(2, 6), Cells(30, 6))) Set Cross2 = Intersect(Target, Range(Cells(2, 6), Cells(30, 6))) If Not Cross1 Is Nothing Or Not Cross2 Is Nothing Then For Each Target In Sh.Range(Cells(2, 6), Cells(30, 6)) If ((Cross1.Value = "D") And (Cross2.Value2 < "C")) Then Sh.Tab.ColorIndex = 3 ElseIf Cross2.Value2 = "C" Then Sh.Tab.ColorIndex = 5 Else Sh.Tab.ColorIndex = xlColorIndexNone End If Next End If -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA Code not working on Shared Workbook
Well, I guess if it can't be done I will settle for that. But...
Is there a way to somehow say: If ((Cross1.Value = "D") And (Cross2.Value2 < "C")) Then REMOVE SHARE CHANGE THE COLOR OF THE TAB SHARE WORKBOOK ElseIf Cross2.Value2 = "C" Then REMOVE SHARE CHANGE THE COLOR OF THE TAB SHARE WORKBOOK Else DO NOTHING Can this be done?? "Dave Peterson" wrote: There are lots of features that are disabled in shared workbooks. When I share a workbook and then try to change the sheet tab color, I see that it's disabled. Maybe you can use a different indicator--something (prefix/suffix) in the sheet name??? KC_Cheer_Coach wrote: I have the following code in a workbook. It works fine when the worksheets inside the workbook are protected but not when the workbook is shared. Is there any way to make this work on the shared workbook? Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range Dim Cross1 As Range Dim Cross2 As Range Set Cross1 = Intersect(Target, Range(Cells(2, 6), Cells(30, 6))) Set Cross2 = Intersect(Target, Range(Cells(2, 6), Cells(30, 6))) If Not Cross1 Is Nothing Or Not Cross2 Is Nothing Then For Each Target In Sh.Range(Cells(2, 6), Cells(30, 6)) If ((Cross1.Value = "D") And (Cross2.Value2 < "C")) Then Sh.Tab.ColorIndex = 3 ElseIf Cross2.Value2 = "C" Then Sh.Tab.ColorIndex = 5 Else Sh.Tab.ColorIndex = xlColorIndexNone End If Next End If -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA Code not working on Shared Workbook
Try recording a macro when you do it manually and you'll have the code.
KC_Cheer_Coach wrote: Well, I guess if it can't be done I will settle for that. But... Is there a way to somehow say: If ((Cross1.Value = "D") And (Cross2.Value2 < "C")) Then REMOVE SHARE CHANGE THE COLOR OF THE TAB SHARE WORKBOOK ElseIf Cross2.Value2 = "C" Then REMOVE SHARE CHANGE THE COLOR OF THE TAB SHARE WORKBOOK Else DO NOTHING Can this be done?? "Dave Peterson" wrote: There are lots of features that are disabled in shared workbooks. When I share a workbook and then try to change the sheet tab color, I see that it's disabled. Maybe you can use a different indicator--something (prefix/suffix) in the sheet name??? KC_Cheer_Coach wrote: I have the following code in a workbook. It works fine when the worksheets inside the workbook are protected but not when the workbook is shared. Is there any way to make this work on the shared workbook? Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range Dim Cross1 As Range Dim Cross2 As Range Set Cross1 = Intersect(Target, Range(Cells(2, 6), Cells(30, 6))) Set Cross2 = Intersect(Target, Range(Cells(2, 6), Cells(30, 6))) If Not Cross1 Is Nothing Or Not Cross2 Is Nothing Then For Each Target In Sh.Range(Cells(2, 6), Cells(30, 6)) If ((Cross1.Value = "D") And (Cross2.Value2 < "C")) Then Sh.Tab.ColorIndex = 3 ElseIf Cross2.Value2 = "C" Then Sh.Tab.ColorIndex = 5 Else Sh.Tab.ColorIndex = xlColorIndexNone End If Next End If -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Shared Workbook not working | Excel Discussion (Misc queries) | |||
| Printing viewing a shared workbook on a shared drive | Excel Discussion (Misc queries) | |||
| update pivot in shared shared workbook | Excel Discussion (Misc queries) | |||
| activesheet.tab.colorindex not working when workbook shared | Excel Discussion (Misc queries) | |||
| Often-Used Code not working in a new Workbook | Excel Discussion (Misc queries) | |||
Linear Mode
