Sheet Scroll to the Right on time change

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.

Please Login or Register  to view this content.

Originally Posted by peakymatt

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.

Originally Posted by peakymatt

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.

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

Please Login or Register  to view this content.

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?