Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jul 2011
    Posts
    57
    Thanks
    5
    Thanked 1 Time in 1 Post

    Chart updates from rolling columns on Excel 2010

    I have a spreadsheet that I update regularly. On one sheet, I have about 16 rows and columns for each week of the year.
    There is a chart associated with it that shows all of the rows and then just 4 of the week columns. Each week, I hide the current first column and then unhide the new weeks column.
    This was all working fine until recently and now when I change the visible week columns, the chart does not remove the removed week and add the added week.
    I inherited this spreadsheet, so I do not know how it was set up to do this.
    My attempts to fix it were not successful and it only shows the specific weeks I selected in the data choices. When I make the change, it loses the now hidden previous week and does not include the new unhidden week.
    How do I set this up to work again?
    Thanks,
    Jeff

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Can you attach a cleaned up version with no proprietary info in it so we can examine it?

    Steve

  3. #3
    Star Lounger
    Join Date
    Jul 2011
    Posts
    57
    Thanks
    5
    Thanked 1 Time in 1 Post
    Steve,
    Here is the page I am having issues with. I have left the sheet with only 4 weeks visible. The rest of the preceding and following week columns are hidden, so you can see what I do with this.
    So for this coming week, I would hide the first visible column, then unhide the rest of the year, then hide all of them except the column for 3/17/14.
    Jeff
    Attached Files Attached Files

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    JWThau,

    Seems like you are going through a lot of trouble updating this workbook weekly. Here is some code that will let you scroll through a 1 month period (by weekly increments) using spin buttons while your graph updates automatically.

    HTH,
    Maud

    Stats.png

    Code:
    Public Sub UpdateNext()
    Application.ScreenUpdating = True
    For I = 2 To 49
    If Cells(2, I).EntireColumn.Hidden = False Then
        Cells(2, I).EntireColumn.Hidden = True
        Cells(2, I + 4).EntireColumn.Hidden = False
        Exit For
    End If
    Next I
    Application.ScreenUpdating = False
    End Sub
    
    Public Sub UpdatePrevious()
    Application.ScreenUpdating = True
    For I = 3 To 53
    If Cells(2, I).EntireColumn.Hidden = False Then
        Cells(2, I - 1).EntireColumn.Hidden = False
        Cells(2, I + 3).EntireColumn.Hidden = True
        Exit For
    End If
    Next I
    Application.ScreenUpdating = False
    End Sub
    Attached Files Attached Files

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    JWThau (2014-03-18)

  6. #5
    Star Lounger
    Join Date
    Jul 2011
    Posts
    57
    Thanks
    5
    Thanked 1 Time in 1 Post
    Maud,
    That is very cool and thank you so much. I thought about taking credit for this at work , but that wouldn't be fair to the great little function you gave me.
    I am still curious about why this used to work and then stopped. Learning those kinds of things makes me more functional and I enjoy figuring it out.
    Jeff

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Jeff,

    Reverse the True/False in the 4 lines Application.ScreenUpdating= and the transition will run very smoothly. Had a "duh" moment there. Take credit for it, this one is on me!

    Maud

  8. #7
    Star Lounger
    Join Date
    Jul 2011
    Posts
    57
    Thanks
    5
    Thanked 1 Time in 1 Post
    Okay, did that, but I have a question here.
    I added the code to my macros for that workbook, but I don't get the buttons.
    How do I copy over the spinbuttons code?

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Go to Developer tab> insert> More Controls> Scroll down to SpinButton> OK> drag cross hair to draw the control. Note the following event procedures for the control in the sheet's module that call the routines that do the work.

    spin.png

    Code:
    Private Sub SpinButton1_SpinDown()
    UpdatePrevious
    End Sub
    
    Private Sub SpinButton1_SpinUp()
    UpdateNext
    End Sub
    If you do not find it, you could PM me I will give you my email address so I could add it for you.

    Maud

  10. #9
    Star Lounger
    Join Date
    Jul 2011
    Posts
    57
    Thanks
    5
    Thanked 1 Time in 1 Post
    Maud,
    Thanks, I figured it out. Works perfectly.
    Thanks so much for the help.
    Jeff

Posting Permissions

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