Below is the code where I am successfully changing the worksheet tab color
under a certain conditon. However, after the tab is changed, I cannot
navigate away from that spreadsheet to another one. It is as if it is
locked. I watched the code as it ran and it ran thru "end sub". I am using
VBA 6.5 and excel 2003. Any ideas?

Private Sub Worksheet_Deactivate()
Debug.Print "order deactivate"
Dim orderWS As Worksheet
Dim tabRg As Range
Set orderWS = ThisWorkbook.Worksheets(activesheet)
Set tabRg = orderWS.Range("F40")

' check if carrier has been assigned and then change color tab if it has

Debug.Print tabRg.Value
'If tabRg.Value " " Then
'Sheets(activesheet).Select
' ActiveWorkbook.Sheets(activesheet).Tab.ColorIndex = 35
'End If


ErrorProcess:
If Err.Number < 0 Then
MsgBox Err.Description & " In worksheet-deactivate", vbCritical, "Error
# " & Err.Number
End If


End Sub

So you just want to use the color of the tab as a flag--green is ok (and maybe
red for bad)?

If that's ok, then maybe...

Option Explicit
Private Sub Worksheet_Deactivate()

Dim tabRg As Range

'Me is the object owning the code--in this case, the Order Worksheet
Set tabRg = Me.Range("F40")

If Trim(tabRg.Value) = "" Then
'it's empty, leave the tab color alone???
'or change it to red (for me)?
Me.Tab.ColorIndex = 3
Else
Me.Tab.ColorIndex = 35
End If

End Sub

Phyllis wrote:

Below is the code where I am successfully changing the worksheet tab color
under a certain conditon. However, after the tab is changed, I cannot
navigate away from that spreadsheet to another one. It is as if it is
locked. I watched the code as it ran and it ran thru "end sub". I am using
VBA 6.5 and excel 2003. Any ideas?

Private Sub Worksheet_Deactivate()
Debug.Print "order deactivate"
Dim orderWS As Worksheet
Dim tabRg As Range
Set orderWS = ThisWorkbook.Worksheets(activesheet)
Set tabRg = orderWS.Range("F40")

' check if carrier has been assigned and then change color tab if it has

Debug.Print tabRg.Value
'If tabRg.Value " " Then
'Sheets(activesheet).Select
' ActiveWorkbook.Sheets(activesheet).Tab.ColorIndex = 35
'End If

ErrorProcess:
If Err.Number < 0 Then
MsgBox Err.Description & " In worksheet-deactivate", vbCritical, "Error
# " & Err.Number
End If


End Sub

--

Dave Peterson

Hi Dave,
Thanks for your response. I am able to successfully change the tab color
with the code I provided. The problem is that after the code changes the
tab, I am not able to select another worksheet. I am locked onto the
worksheet that I changed the tab color on. Why???

"Dave Peterson" wrote:

So you just want to use the color of the tab as a flag--green is ok (and maybe
red for bad)?

If that's ok, then maybe...

Option Explicit
Private Sub Worksheet_Deactivate()

Dim tabRg As Range

'Me is the object owning the code--in this case, the Order Worksheet
Set tabRg = Me.Range("F40")

If Trim(tabRg.Value) = "" Then
'it's empty, leave the tab color alone???
'or change it to red (for me)?
Me.Tab.ColorIndex = 3
Else
Me.Tab.ColorIndex = 35
End If

End Sub

Phyllis wrote:

Below is the code where I am successfully changing the worksheet tab color
under a certain conditon. However, after the tab is changed, I cannot
navigate away from that spreadsheet to another one. It is as if it is
locked. I watched the code as it ran and it ran thru "end sub". I am using
VBA 6.5 and excel 2003. Any ideas?

Private Sub Worksheet_Deactivate()
Debug.Print "order deactivate"
Dim orderWS As Worksheet
Dim tabRg As Range
Set orderWS = ThisWorkbook.Worksheets(activesheet)
Set tabRg = orderWS.Range("F40")

' check if carrier has been assigned and then change color tab if it has

Debug.Print tabRg.Value
'If tabRg.Value " " Then
'Sheets(activesheet).Select
' ActiveWorkbook.Sheets(activesheet).Tab.ColorIndex = 35
'End If

ErrorProcess:
If Err.Number < 0 Then
MsgBox Err.Description & " In worksheet-deactivate", vbCritical, "Error
# " & Err.Number
End If


End Sub

--

Dave Peterson
.

I don't know.

The code you posted blew up on me at this line:

Set orderWS = ThisWorkbook.Worksheets(ActiveSheet)

So I wasn't able to test it.

Phyllis wrote:

Hi Dave,
Thanks for your response. I am able to successfully change the tab color
with the code I provided. The problem is that after the code changes the
tab, I am not able to select another worksheet. I am locked onto the
worksheet that I changed the tab color on. Why???

"Dave Peterson" wrote:

So you just want to use the color of the tab as a flag--green is ok (and maybe
red for bad)?

If that's ok, then maybe...

Option Explicit
Private Sub Worksheet_Deactivate()

Dim tabRg As Range

'Me is the object owning the code--in this case, the Order Worksheet
Set tabRg = Me.Range("F40")

If Trim(tabRg.Value) = "" Then
'it's empty, leave the tab color alone???
'or change it to red (for me)?
Me.Tab.ColorIndex = 3
Else
Me.Tab.ColorIndex = 35
End If

End Sub

Phyllis wrote:

Below is the code where I am successfully changing the worksheet tab color
under a certain conditon. However, after the tab is changed, I cannot
navigate away from that spreadsheet to another one. It is as if it is
locked. I watched the code as it ran and it ran thru "end sub". I am using
VBA 6.5 and excel 2003. Any ideas?

Private Sub Worksheet_Deactivate()
Debug.Print "order deactivate"
Dim orderWS As Worksheet
Dim tabRg As Range
Set orderWS = ThisWorkbook.Worksheets(activesheet)
Set tabRg = orderWS.Range("F40")

' check if carrier has been assigned and then change color tab if it has

Debug.Print tabRg.Value
'If tabRg.Value " " Then
'Sheets(activesheet).Select
' ActiveWorkbook.Sheets(activesheet).Tab.ColorIndex = 35
'End If

ErrorProcess:
If Err.Number < 0 Then
MsgBox Err.Description & " In worksheet-deactivate", vbCritical, "Error
# " & Err.Number
End If


End Sub

--

Dave Peterson
.


--

Dave Peterson