I have a time sheet I want to put directional buttons on similar to a
calendar so you can move forward and backward to view different pay
periods.
Each pay period is 2 weeks long taking up 14 columns + 1 column for
NOTES + 1 column for a border, so every pay period is 16 columns wide.
Using the Macro Recorder in Excel 2007 it used this;
ActiveWindow.ScrollColumn = 21 or 37 or 53 etc. Could I count what
column the user is looking at and substitute the number after
ActiveWindow.ScrollColumn = with n+ or – 16 ?
Is this the best method for moving the focus for a user or can someone
suggest something better?

(E-Mail Removed) brought next idea :
> I have a time sheet I want to put directional buttons on similar to a
> calendar so you can move forward and backward to view different pay
> periods.
> Each pay period is 2 weeks long taking up 14 columns + 1 column for
> NOTES + 1 column for a border, so every pay period is 16 columns wide.
> Using the Macro Recorder in Excel 2007 it used this;
> ActiveWindow.ScrollColumn = 21 or 37 or 53 etc. Could I count what
> column the user is looking at and substitute the number after
> ActiveWindow.ScrollColumn = with n+ or – 16 ?
> Is this the best method for moving the focus for a user or can someone
> suggest something better?

I can see where this isn't going to serve your purpose too well if
users use the tab key or arrow keys to navigate. Why can't they just
hold down the 'Alt' key while pressing PageUP (scroll left) or PageDn
(scroll right)?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Sep 7, 5:48*pm, GS <g...@somewhere.net> wrote:
> lain...@canada.com brought next idea :
>
> > I have a time sheet I want to put directional buttons on similar to a
> > calendar so you can move forward and backward to view different pay
> > periods.
> > Each pay period is 2 weeks long taking up 14 columns + 1 column for
> > NOTES + 1 column for a border, so every pay period is 16 columns wide.
> > Using the Macro Recorder in Excel 2007 it used this;
> > ActiveWindow.ScrollColumn = 21 or 37 or 53 etc. Could I count what
> > column the user is looking at and substitute the number after
> > ActiveWindow.ScrollColumn = with n+ or – 16 ?
> > Is this the best method for moving the focus for a user or can someone
> > suggest something better?
>
> I can see where this isn't going to serve your purpose too well if
> users use the tab key or arrow keys to navigate. Why can't they just
> hold down the 'Alt' key while pressing PageUP (scroll left) or PageDn
> (scroll right)?
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc

Different users have different size screens so using Alt PageUP might
work on some screens but not for others. Any thoughts on vb coding
which might work?

Sub goright()
On Error Resume Next
ActiveCell.Offset(0, 16).Select
End Sub

Sub goleft()
On Error Resume Next
ActiveCell.Offset(0, -16).Select
End Sub


Gord Dibben Microsoft Excel MVP


On Thu, 8 Sep 2011 08:23:49 -0700 (PDT), (E-Mail Removed) wrote:

>On Sep 7, 5:48*pm, GS <g...@somewhere.net> wrote:
>> lain...@canada.com brought next idea :
>>
>> > I have a time sheet I want to put directional buttons on similar to a
>> > calendar so you can move forward and backward to view different pay
>> > periods.
>> > Each pay period is 2 weeks long taking up 14 columns + 1 column for
>> > NOTES + 1 column for a border, so every pay period is 16 columns wide.
>> > Using the Macro Recorder in Excel 2007 it used this;
>> > ActiveWindow.ScrollColumn = 21 or 37 or 53 etc. Could I count what
>> > column the user is looking at and substitute the number after
>> > ActiveWindow.ScrollColumn = with n+ or – 16 ?
>> > Is this the best method for moving the focus for a user or can someone
>> > suggest something better?
>>
>> I can see where this isn't going to serve your purpose too well if
>> users use the tab key or arrow keys to navigate. Why can't they just
>> hold down the 'Alt' key while pressing PageUP (scroll left) or PageDn
>> (scroll right)?
>>
>> --
>> Garry
>>
>> Free usenet access athttp://www.eternal-september.org
>> ClassicVB Users Regroup! comp.lang.basic.visual.misc
>
>Different users have different size screens so using Alt PageUP might
>work on some screens but not for others. Any thoughts on vb coding
>which might work?

(E-Mail Removed) used his keyboard to write :
> On Sep 7, 5:48Â*pm, GS <g...@somewhere.net> wrote:
>> lain...@canada.com brought next idea :
>>
>>> I have a time sheet I want to put directional buttons on similar to a
>>> calendar so you can move forward and backward to view different pay
>>> periods.
>>> Each pay period is 2 weeks long taking up 14 columns + 1 column for
>>> NOTES + 1 column for a border, so every pay period is 16 columns wide.
>>> Using the Macro Recorder in Excel 2007 it used this;
>>> ActiveWindow.ScrollColumn = 21 or 37 or 53 etc. Could I count what
>>> column the user is looking at and substitute the number after
>>> ActiveWindow.ScrollColumn = with n+ or – 16 ?
>>> Is this the best method for moving the focus for a user or can someone
>>> suggest something better?
>>
>> I can see where this isn't going to serve your purpose too well if
>> users use the tab key or arrow keys to navigate. Why can't they just
>> hold down the 'Alt' key while pressing PageUP (scroll left) or PageDn
>> (scroll right)?
>>
>> --
>> Garry
>>
>> Free usenet access athttp://www.eternal-september.org
>> ClassicVB Users Regroup! comp.lang.basic.visual.misc
>
> Different users have different size screens so using Alt PageUP might
> work on some screens but not for others. Any thoughts on vb coding
> which might work?

Well, Alt+[PageUp/PageDn] WILL work on all screens but the scroll would
not necessarily be 16 columns at a time because it will be fixed to
however many columns fit the screen.

Gord offers a VBA solution and I won't give you such a simplistic way
to handle it. I have a project that scrolls 'modules' into view one
module at a time using a combobox. The modules may have any number of
columns each, not necessarily the same number in each. That said, the
code to manage this is fairly complex because it compensates for
wherever the user has navigated by any other means (tab, keyboard).
Each module is assigned a defined name when inserted in the wks, making
it much easier to manage/manipulate views as some modules can be
grouped for display while other modules are hidden (and vice versa).

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc