scroll a few columns at a time

[Tendresse]

I'm making a calendar where each column represents one day and each row
represents a month. It looks something like this:

A B C D ....... I J .......
P Q
Month Mon Tue Wed ...... Mon Tue ..... Mon Tue
Jan 1 2 3 15 16 .... 22
23
Feb 12 13 ....
19 20

Row 1 and Column A are frozen, (always displayed in the screen).

I would like to design a 'fast forward' button. When clicked, this buttons
scrolls horizontally to the right to put the nearest MONDAY (on the right)
adjacent to Column A. and similarly a 'Rewind' button that scrolls
horizontally to the left to put the nearest MONDAY (on the left) adjacent to
Column A.

In other words, i want to be able to scroll horizontally one week at a time
whether it's forward or backward.

i'm using Excel 2003.

Many thanks
Tendresse

 

[Vasant Nanavati]

An example:

Sub MoveRight()
ActiveWindow.ScrollColumn = Rows(1).Find _
("Mon", Intersect(Columns(ActiveWindow.ScrollColumn), _
Rows(1))).Column
End Sub

There are probably easier ways.
_______________________________________________________________________

 

[FSt1]

hi
try this....
set column A width to 9
set row 1 height to 22.5
drop 2 command buttons on the sheet and squeeze them in to cell A1 side by
side.
right click left button>click view properties.
set the left command button's name to cb1 and caption to L.
right click right button>click view properties.
set the right command button's name to cb1 and caption to R.
freeze panes on column A, row 1.
right click left button>click view code and paste this.....
Private Sub CB1_Click()
On Error GoTo err
ActiveCell.Offset(0, -7).Select
err: If err Then
MsgBox ("Opps! Ran out of sheet going left.")
Exit Sub
End If
End Sub
right click right button>click view code and paste this.....
Private Sub cb2_Click()
On Error GoTo err
ActiveCell.Offset(0, 7).Select
err: If err Then
MsgBox ("Opps! Ran out of sheet going right.")
Exit Sub
End If
End Sub

tested. works. using xp/xl2003

regards
FSt1

 

[Tendresse]

Thank you very much. This was really helpful. Your code enables me to scroll
one week at a time towards the right. Then when i reach the last week, it
goes back to week 1. That's perfect.

But suppose i'm viewing week 2 and want to go back to week 1. I'll have to
click a few times to reach the end of the month before i can view week 1.

Therefore, i think i need to have another button that does exactly the same
but towards the left. Meaning to move to the PREVIOUS Monday. What do i need
to change in the code to achieve this?

Thanks again,

Tendresse

 

[Tendresse]

Thank you very much, FST1. Your code worked very well. There was a couple of
things though that didn't quite work with me:

1) when i keep scrolling to the right, it keeps going outside the calendar
all together. The message ("Opps! Ran out of sheet going right.") never
appears. How do i set an end to the worksheet from the right hand side?

2) the column with the MONDAY heading appears somehow in the middle in the
screen. I mean i can still see the SUNDAY column of the week before. I want
MONDAY to be always displayed straight after the MONTH column. Do you know
what i mean?

Many thanks. I appreciate your help

Tendresse

 

[Vasant Nanavati]

Try (untested):

Sub MoveLeft()
ActiveWindow.ScrollColumn = Rows(1).Find _
("Mon", Intersect(Columns(ActiveWindow.ScrollColumn), _
Rows(1)), , , xlPrevious).Column
End Sub

_________________________________________________________________________