+ Reply to Thread
Results 1 to 5 of 5

update all sheets on workbook activation

Hybrid View

Jon Henry update all sheets on workbook... 05-24-2008, 08:57 AM
broro183 hi Jon, Please edit your... 05-24-2008, 09:13 AM
Jon Henry Worked like a champ, thanks... 05-24-2008, 10:23 AM
broro183 Thanks for the feedback Jon -... 05-24-2008, 04:12 PM
davesexcel try the workbook event ... 05-24-2008, 09:45 AM
  1. #1
    Registered User
    Join Date
    05-23-2008
    Posts
    8

    update all sheets on workbook activation

    Im working on a spreadsheet with about 100-150 worksheets in it. On each sheet there are two dates(one pre-determined and one the current date) that I want to compare to determine whether or not they are <4 years nine months apart, >4 years 9 months apart or >5 years apart. Each state will make the tab a certain color, green, yellow and red respectively.

    Im using the worksheet_change event right now to update the color after Ive entered a date using this code:

    If ActiveSheet.Cells(5, 14) - ActiveSheet.Cells(5, 1) >= 1828 Then
            ActiveSheet.Tab.ColorIndex = 3
        ElseIf ActiveSheet.Cells(5, 14) - ActiveSheet.Cells(5, 1) >= 1736 Then
            ActiveSheet.Tab.ColorIndex = 6
        Else
            ActiveSheet.Tab.ColorIndex = 4
        End If
    My problem now is when I open the workbook, the colors wont update without a change to each sheet. I want to make it so that when I open the workbook, the colors update themselves. I can do this right now using the workbook_activate event and this code:

    If Sheet24.Cells(5, 14) - Sheet24.Cells(5, 1) >= 1828 Then
            Sheet24.Tab.ColorIndex = 3
        ElseIf Sheet24.Cells(5, 14) - Sheet24.Cells(5, 1) >= 1736 Then
            Sheet24.Tab.ColorIndex = 6
        Else
            Sheet24.Tab.ColorIndex = 4
        End If
    Doing this however requires I input one copy of this code for each sheet(thats a lot). Im looking for a way to do this with much less code, maybe referencing the cells a different way(ie ActiveWorkbook.something.something?

    Is it possible or do you guys have any other suggestions for a more efficient way to do it?

    Thanks
    Jon
    Last edited by Jon Henry; 05-24-2008 at 09:44 AM.

  2. #2
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi Jon,

    Please edit your initial post by including "code tags" around your lines of code. This can be done by selecting your text & then pressing the "#" icon which appears immediately above your message.

    The most efficient way I know of is to use a "for each" structure combined with a "with" statement ie:
    (note, this is untested)
    Option Explicit
    Private Sub Workbook_Open()
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Worksheets
        With sht
            Select Case .Cells(5, 14) - .Cells(5, 1)
                Case Is >= 1828
                    .Tab.ColorIndex = 3
                Case Is >= 1736
                    .Tab.ColorIndex = 6
                Case Else
                    .Tab.ColorIndex = 4
            End Select
        End With
    Next sht
    End Sub
    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  3. #3
    Registered User
    Join Date
    05-23-2008
    Posts
    8
    Worked like a champ, thanks broro.

    I was using those events Dave. The problem was using them, when I opened a workbook I had to go to each sheet to activate it and make it run the code. I needed something that ran it on all sheets on workbook open. Thanks for the help though.

  4. #4
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    Thanks for the feedback Jon - I'm pleased I could help :-)

    Rob

  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525
    try the workbook event
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    End Sub
    
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    End Sub
    http://www.contextures.com/xlvba01.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1