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
> .
>
|
Similar Threads
