Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    VBA Scroll into View (XL 97)

    I seem to have lost my brains today. Isn't there some easy way in VBA to scroll a window so that a particular range is in view? I know that I've used it, but I can't find it. Maybe it is only in XL2K.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA Scroll into View (XL 97)

    Hi, Sam. Do you mean like Andrew's <post#=104926>post 104926</post#>, or something different?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA Scroll into View (XL 97)

    I'm not sure what you mean exactly. Are you thinking of something like:

    Range("a50:e60").activate

    Steve

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Scroll into View (XL 97)

    Use the ScrollRow and ScrollColumn properties of the active window,
    e.g the following should put cell AA:100 of the active sheet in the top left of the current window :
    <pre>With ActiveWindow
    .ScrollRow = 100
    .ScrollColumn = 27
    End With</pre>

    Andrew

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Scroll into View (XL 97)

    John, Steve and Andrew, no & dosn't work (2x).

    I was trying to fix the attachment of <post#=177356>post 177356</post#> so that it opens with today's date showing. It works when opened with excel, but not with my browser (Win/ME with IE 6.0). I thought that when I was working with embedded charts, I used a ScrollIntoView method, but it must have been in a dream. Notice that the open event is firing: if you press the right-arrow key, you go to tomorrow, but it dosn't scroll until you press the right-arrow. I added ScrollColumn, but it still dosn't work. Wonder if it is a "feature" of FreezePanes?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Scroll into View (XL 97)

    Well, even at my old age, the memory isn't gone. XL2K and above have a ScrollIntoView method for a Window object, but it's just not in XL97. Still like to know why the attachment dosn't work. TIA --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Scroll into View (XL 97)

    Sammy,

    When using Find with dates, I think the format has more relevance than the actual numeric value, so rather than look for serial value of the date you should search for a string representation of the in format used. See if <pre>Private Sub Workbook_Open()
    Worksheets("Sheet1").Activate
    ActiveWindow.FreezePanes = False
    Range("B2").Select
    ActiveWindow.FreezePanes = True
    Rows(1).NumberFormat = "d-mmm-yy"
    Rows(1).Cells.Find(what:=Format(Date, "d-mmm-yy"), LookIn:=xlValues).Select
    End Sub</pre>

    helps at all.

    Andrew

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Scroll into View (XL 97)

    Yes, that is much much better. Obviously, I've never searched for dates before. But, how to get the result to scroll into view?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Scroll into View (XL 97)

    Sammy, it does scroll into view for me, with the selection being in the center of the window.

    Perhaps

    ........
    Rows(1).Cells.Find(what:=Format(Date, "d-mmm-yy"), LookIn:=xlValues).Select
    ActiveWindow.ScrollColumn = Selection.Column

    gives a better result.

    Andrew

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Scroll into View (XL 97)

    Tried that: dosn't work. Must be a "Feature" of IE with XL97. What browser and Office are you using? <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23> --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Scroll into View (XL 97)

    Well it seems to be a "feature" of XL2002 as well, using IE 6.

    I had not tested it in IE, but having done so can confirm that the scrolling does not happen for me.

    Maybe when excel is running in IE, it gets a bit confused about windows - is an IE Window or an Excel window ?

    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •