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

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

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

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