scroll a few columns at a time
 

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

scroll a few columns at a time
 

An example:

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

There are probably easier ways.
__________________________________________________ _____________________

"Tendresse" wrote in message
...
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

scroll a few columns at a time
 

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 buttonclick view properties.
set the left command button's name to cb1 and caption to L.
right click right buttonclick 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 buttonclick 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 buttonclick 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" wrote:

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

scroll a few columns at a time
 

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

"Vasant Nanavati" wrote:

An example:

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

There are probably easier ways.
__________________________________________________ _____________________

"Tendresse" wrote in message
...
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

scroll a few columns at a time
 

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
"FSt1" wrote:

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 buttonclick view properties.
set the left command button's name to cb1 and caption to L.
right click right buttonclick 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 buttonclick 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 buttonclick 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" wrote:

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

scroll a few columns at a time
 

Try (untested):

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

__________________________________________________ _______________________


"Tendresse" wrote in message
...
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

"Vasant Nanavati" wrote:

An example:

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

There are probably easier ways.
__________________________________________________ _____________________

"Tendresse" wrote in message
...
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