Sheet Scroll to the Right on time change

[peakymatt]

Hi All

What I am trying to achieve is this.

I have a sheet with times across the top in 15 minute intervals. As the time becomes current conditional formatting kicks in and highlights the cell. To the left hand side of the sheet, there is a frozen pane.

Now what I would like is as the time changes for the sheet to scroll to the right so that the current time block is up against the frozen pane.

I have attached a dummy sheet so that you are able to see what I mean.

Thanks in advance

Matt

[foxguy]

Hi Matt;
Here you go:

Go into Scroll_Col() and adjust the addresses in the section marked.

You will notice that there is a Static variable "siCol" in Scroll_Col().
This prevents the macro from scrolling the sheet until the next time period (so the user can scroll around the spreadsheet without it automatically scrolling them back).

There is also an optional bForce parameter in Scroll_Col(). This is so you can force the sheet to scroll back to the correct column when you want, but you will need to decide when those times are.

I haven't tested it for times before 9:00 AM. I left that for you.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Scroll_Col
End Sub

Sub Scroll_Col(Optional bForce As Boolean)
    Dim rStart As Range
    Dim rEnd As Range
    Dim rTimes As Range
    Dim rNow As Range
    Dim rSelection As Range
    Dim rGoto As Range
    
    Dim sh As Worksheet
    Dim iCol As Integer
    
    Static siCol As Integer

    Set sh = Selection.Worksheet
    With sh
        '************************************
        'Adjust these addresses as necessary
        Set rStart = .Range("C2")
        Set rEnd = .Range("CW2")
        Set rNow = .Range("A2")
        '************************************

        Set rTimes = Range(rStart, rEnd)

        On Error Resume Next
        iCol = Application.WorksheetFunction.Match(CDbl(rNow.Value), rTimes, 1)
        iCol = rStart.Column + iCol - 2

        If bForce Or (iCol <> siCol) Then
            siCol = iCol
            Set rGoto = .Cells(1, iCol)
            
            Application.EnableEvents = False
            Set rSelection = Selection
            Application.Goto rGoto, True
            rSelection.Select
            Application.EnableEvents = True

            'This would be a better solution
            '    because it doesn't change the selection
            '    but it will scroll the Left Pane
            '    not the right pane
            'ActiveWindow.ScrollColumn = iCol
        End If
    End With
End Sub

[peakymatt]

Hey foxguy
thanks for that. Will give it a whirl and let you know how I go.

Matt

[peakymatt]

Hey foxguy

It works if I run the Sub Scroll_Col, but then it doesn't work on the Worksheet_selectionChange.

Any reason why this would be so.

[foxguy]

It works if I run the Sub Scroll_Col, but then it doesn't work on the Worksheet_selectionChange.
Any reason why this would be so.

The most likely reason is that you put the Worksheet_selectionChange in the wrong module. I went back and looked at the file you uploaded and there were no macros in it, which makes me wonder if you are unfamiliar with how events works. If I'm wrong, I apologize.

The Worksheet_selectionChange will only execute if it is in the sheet's module. Right mouse click on the sheet's tab and select "View Code". Make sure it's in the window that opens.

The second most likely is that sub's name is misspelled. Excel looks for a sub in the correct module that has the EXACT spelling, parameters, etc.

The last thing I can think of is that EnableEvents is turned off. Go to the immediate window and type "? Application.EnableEvents" (with the "?") and hit enter. If it prints False then type "Application.EnableEvents = True" and hit enter.

If none of these is the problem, I would need to see the workbook, so upload a copy and I'll look at it.

[peakymatt]

Hey FoxGuy

Checked all that. I have noticed that it will Scroll once, but then if you happen to scroll around the page after that, it wont scroll again to the current time period.

Is there any other way of doing it than by SelectionChange?

Thanks again for your help, I really appreciate it.

[foxguy]

Checked all that. I have noticed that it will Scroll once, but then if you happen to scroll around the page after that, it wont scroll again to the current time period.
Is there any other way of doing it than by SelectionChange?

You will have to decide how you want to trigger it. A button on the frozen pane, a menu button, a double click on column heading (my favorite), a click inside the frozen pane, just about anything that you can put into words.
I'm on the way out the door again, but I'll be back in 3-4 hours. Let me know what you want and I'll help you get it set up.

[peakymatt]

What I actually wanted was for it happen automatically, so that the page will scroll with the time changing, so that it will alway be up to date.

On my version here I have set it up at the moment with a button on the left hand side, but its not really what we were wanting. Is there anyway to automate it.

Is it possible to say link it to the conditional format of the cell, and as the colour of the cell changes, then the page scrolls????, just an idea.

[foxguy]

What I actually wanted was for it happen automatically, so that the page will scroll with the time changing, so that it will alway be up to date.

Is it possible to say link it to the conditional format of the cell, and as the colour of the cell changes, then the page scrolls????, just an idea.

First, just to be accurate, the CFs (conditional formatting) don't change the cell's color when the time changes, they change the cell's color when A2 updates. A2 doesn't update if someone just scrolls around the sheet without changing anything. You can verify this by letting the computer sit idle until the next 15 minute segment, then just scroll around the sheet changing selection, but don't change any cell. You will see that A2 doesn't update and the CFs don't change the cell's color. Be sure and scroll the screen so that the correct column is not up against the frozen pane. Then change any cell, and you will see A2 update, the CF change the cell's color, and the screen will scroll to the correct column.
Obviously this difference is moot as long as the user is actually typing.

I can improve the macro so that A2, the CFs, and the scrolling all update when the user changes selection (they won't have to change anything). It just needs one line of code added to Scroll_Col. Find this section of code and add one line

        '************************************
        'Adjust these addresses as necessary
        Set rStart = .Range("C2")
        Set rEnd = .Range("CW2")
        Set rNow = .Range("A2")
        '************************************

        Set rTimes = Range(rStart, rEnd)

        rNow.Calculate            'added line
        
        On Error Resume Next

But if you want the screen to scroll even if no one is sitting at the computer, it can be put on a timer. I don't like timers because they can be confusing to people and they can be unpredictable on slow or overloaded computers. Also, if your computer goes to sleep to save energy, the timers quit running also. Obviously if the computer goes to sleep no one would know that the timer quit, but as soon as someone sits down at the computer, all the timers that went dormant execute, and that can cause trouble. I don't think that your timer would cause trouble, but since I don't use them much I can't guarantee that it won't.

I think that adding that one line into Scroll_Col will satisfy you. If you want it put it on a timer, I can set it up for you.

There's just one situation (that I can think of) that you probably have.
When the user is at the beginning of a 15 minute segment (and the screen has been scrolled to the correct column) and is moving around the screen and then wants to have the screen scroll back to the correct column, how can they do it? They don't want to wait 15 minutes to have the screen automatically scroll back, they want to be able to trigger it themselves. That's the decision you need to decide. How do you want to allow the users to trigger the scrolling themselves? A button on the screen, a menu item, a right mouse click item, a double click on a column heading, or what?