Tab color change on specific value
 

In Excel 2007, I have a workbook with several sheets. On some of the sheets,
there are timer cells i.e. columns of cells with start, duration, end (start
+ duration), and time_until_done (end - now).

The time_until_done is conditionally formatted to highlight any that are
completed, but this presumes that I have to go to each sheet and do a manual
recalculation (if no automatic recalculations have been done).

So I thought it would be nice if the tabs could be highlighted if they need
attention. So I put the following in the code for each sheet about which I
want to be notified:

Private Sub Worksheet_Change(ByVal Target As Range)

If Application.WorksheetFunction.Min(Range("K3:K27")) = 0 Then
Me.Tab.Color = vbYellow
Else
Me.Tab.Color = False
End If

End Sub

So as you can probably guess, this only works if I go to the worksheet and
trigger the change event which kind of defeats the old purpose here.

My question is this: is there another event that I can use or some other
method that might work better?

Also, in Access, these things are easier to figure out for me. If I open the
property sheet for an object in Access there's a nice descriptive list of
events that pertain to that object and when I click on the ellipses next to
it, I am taken directly to the code for that form or report and the
subroutine bookend statements are even generated. The rest is gravy.

Is there any such way to find what events are associated with any particular
object in Excel?

I apologize if my use of terminology of terms such as "object" is fast and
loose. I know they have specific meanings in VBA and I'm not that sure what
they truly mean or how an object differs from a container for example.

I really need to buy a tome on this and get my head wrapped around it. You
guys have been so helpful but I would really like to be more self-sufficient.

TIA (again!)...Geoff

Tab color change on specific value
 

Will the worksheet Calculate Event work for you? If anything gets calculated
in the worksheet the event will fire. Hope this helps! If so, let me know,
click 'YES" below.

Private Sub Worksheet_Calculate()

If Application.WorksheetFunction.Min(Range("K3:K27")) = 0 Then
Me.Tab.Color = vbYellow
Else
Me.Tab.Color = False
End If

End Sub

At the tope the VBE there are two DropDown boxes. The left is for the
"object" and the right is the event that pretain to the object.

Hope this helps! If so, let me know, click "YES" below.
--
Cheers,
Ryan


"Geoff" wrote:

In Excel 2007, I have a workbook with several sheets. On some of the sheets,
there are timer cells i.e. columns of cells with start, duration, end (start
+ duration), and time_until_done (end - now).

The time_until_done is conditionally formatted to highlight any that are
completed, but this presumes that I have to go to each sheet and do a manual
recalculation (if no automatic recalculations have been done).

So I thought it would be nice if the tabs could be highlighted if they need
attention. So I put the following in the code for each sheet about which I
want to be notified:

Private Sub Worksheet_Change(ByVal Target As Range)

If Application.WorksheetFunction.Min(Range("K3:K27")) = 0 Then
Me.Tab.Color = vbYellow
Else
Me.Tab.Color = False
End If

End Sub

So as you can probably guess, this only works if I go to the worksheet and
trigger the change event which kind of defeats the old purpose here.

My question is this: is there another event that I can use or some other
method that might work better?

Also, in Access, these things are easier to figure out for me. If I open the
property sheet for an object in Access there's a nice descriptive list of
events that pertain to that object and when I click on the ellipses next to
it, I am taken directly to the code for that form or report and the
subroutine bookend statements are even generated. The rest is gravy.

Is there any such way to find what events are associated with any particular
object in Excel?

I apologize if my use of terminology of terms such as "object" is fast and
loose. I know they have specific meanings in VBA and I'm not that sure what
they truly mean or how an object differs from a container for example.

I really need to buy a tome on this and get my head wrapped around it. You
guys have been so helpful but I would really like to be more self-sufficient.

TIA (again!)...Geoff

Tab color change on specific value
 

I'll check it out tonight. Looks promising. Thanks for all the help!

Tab color change on specific value
 

Thank you thank you a thousand thank yous.

"Ryan H" wrote:



Hope this helps! If so, let me know, click "YES" below.
--
Cheers,
Ryan