+ Reply to Thread
Results 1 to 19 of 19

Scrolling left or right depending on position within active sheet

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Scrolling left or right depending on position within active sheet

    Hi all,

    I'm not sure this is feasible with VBA (hopefully), but I want to "scroll" with the keystroke CTRL + LEFT or RIGHT arrow to a certain position depending on where I am currently on the active sheet.

    e.g.

    If the scroll position is currently between "Japan Clusters" and "Korea Clusters" the keystroke CTRL + LEFT ARROW should bring me to the "Japan Cluster", meaning the blue column should be the left most visible column. If I press CTRL + RIGHT ARROW it should scroll to the 'Korea Cluster", if I then press CTRL + RIGHT ARROW again, it should move on to the next cluster ("HMT Cluster").

    I have attached a sample file to make things more clear. The keystroke CTRL + RIGHT ARROW currently gets you only to the "Japan Cluster" no matter which part of the sheet you're currently looking at!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Scrolling left or right depending on position within active sheet

    Please Login or Register  to view this content.
    Kind regards
    Leo

  3. #3
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Scrolling left or right depending on position within active sheet

    This is incredible!! I knew there is a way

    The only concern I have after a bit of testing, my actual file has 1500+ rows, so if my clients scroll down to e.g. row number 1000 and then want to move to the next "cluster" it moves them up to row number 2 again! Can the code somehow be adjusted to take into account the rows as well?

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Scrolling left or right depending on position within active sheet

    Quote Originally Posted by esbencito View Post
    I'm not sure this is feasible with VBA (hopefully), but I want to "scroll" with the keystroke CTRL + LEFT or RIGHT arrow to a certain position depending on where I am currently on the active sheet.

    e.g.

    If the scroll position is currently between "Japan Clusters" and "Korea Clusters" the keystroke CTRL + LEFT ARROW should bring me to the "Japan Cluster", meaning the blue column should be the left most visible column. If I press CTRL + RIGHT ARROW it should scroll to the 'Korea Cluster", if I then press CTRL + RIGHT ARROW again, it should move on to the next cluster ("HMT Cluster").
    1) To ThisWorkbook Code Module.
    Please Login or Register  to view this content.
    To a Standard Code Module.
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Scrolling left or right depending on position within active sheet

    Great!! Now it selects the row as well.

    Unfortunately, (should have anticipated that) it scrolls the cell to the top left corner. If my clients have e.g. cell DO45 selected, but scrolled to have it centered, the macro still moves the screen down, rather than just right or left. Also, if my clients just scroll rather than select cells, it wouldn't take that into account.

    Now the question, in order to make this even better, would be, can the VBA tell where the screen is currently scrolled to and take that as a starting point rather than just ActiveCell.Column?

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Scrolling left or right depending on position within active sheet

    Change test sub to
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Scrolling left or right depending on position within active sheet

    Hmm... I think it has to be either both parts of the code using "Cell.Select" and "ActiveCell" or both using current window scroll position. A combination of the two doesn't seem to work. In the case above, it doesn't move further to the right or left unless you select a cell again every single time

  8. #8
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Scrolling left or right depending on position within active sheet

    Try

    Please Login or Register  to view this content.
    Kind regards
    Leo

  9. #9
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Scrolling left or right depending on position within active sheet

    almost!!

    It works, but only if the ActiveCell isn't between clusters or at the very left of the sheet. Then it either doesn't move at all or I get a "application defined" error...

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Scrolling left or right depending on position within active sheet

    Quote Originally Posted by esbencito View Post
    Hmm... I think it has to be either both parts of the code using "Cell.Select" and "ActiveCell" or both using current window scroll position. A combination of the two doesn't seem to work. In the case above, it doesn't move further to the right or left unless you select a cell again every single time
    I don't think I understand what you are trying to do, so I leave this to the other.

  11. #11
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Scrolling left or right depending on position within active sheet

    Can i know how many columns you see on your screen ?

    Kind regards
    Leo

  12. #12
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Scrolling left or right depending on position within active sheet

    Not sure I understand your question correctly, but if I'm at zoom 100% looking at one of the cluster section, I can see 36 columns without having to scroll...

  13. #13
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Scrolling left or right depending on position within active sheet

    First test this 1, only for previous, if this works i make the next also

    Please Login or Register  to view this content.
    Kind regards
    Leo

  14. #14
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Scrolling left or right depending on position within active sheet

    Thanks Leo!

    That does indeed work better than the previous code. Though, not 100% reliable as e.g. it doesn't scroll to the last cluster properly when I am on the very left of the sheet. Also, even though it does select the right cell, it still moves the screen up or down slightly to center (!) the active cell (minor but still a little annoying when comparing data by cluster).

    If there isn't a fix for that with this "search for: Cluster" approach. Could below approach work which I used to scroll sheets simultaneously? It gets the current row and column scroll position and applies it to the other sheet when activated. Maybe something similar could work here as well? Or a combination of both? not sure...

    https://www.excelforum.com/excel-pro...taneously.html

  15. #15
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Scrolling left or right depending on position within active sheet

    Think this is it then

    Please Login or Register  to view this content.
    Kind regards
    Leo

  16. #16
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Scrolling left or right depending on position within active sheet

    Great!!

    Only problem here (sorry for not pointing that out earlier), my clusters actually have names, they aren't called "Store 1", but rather "TOP","A","B" and are constantly changing

    Capture1.PNG

  17. #17
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Scrolling left or right depending on position within active sheet

    Next try

    Please Login or Register  to view this content.
    Cheers
    Leo

  18. #18
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Scrolling left or right depending on position within active sheet

    Awesome!! Thanks! That finally works

    I like your solution with activating the cell in Row (2) first, then finding the string "Cluster". Makes it quite easy to apply it to other cases by merging cells and finding common strings in each section of the sheet.

  19. #19
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Scrolling left or right depending on position within active sheet

    Btw, if someone else has a different solution than the above, I would still be interested in seeing them to learn more about VBA in particular regarding scrolling without selecting or deselecting cells

    e.g.

    https://www.excelforum.com/excel-pro...taneously.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Retrieving the sheet name to the left of the active sheet
    By JPaniagua in forum Excel General
    Replies: 2
    Last Post: 03-10-2014, 12:42 PM
  2. Active cell moving but sheet is not scrolling
    By AmStaffMom in forum Excel General
    Replies: 3
    Last Post: 05-30-2012, 02:08 PM
  3. relative position of active cell over multiple sheet
    By endoskeleton in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2011, 09:28 AM
  4. How do I know which sheets are to the RIGHT and LEFT to the Active Sheet?
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 04-04-2011, 11:07 AM
  5. using a value from the sheet to the left of the active sheet
    By vn900 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2011, 05:24 AM
  6. Position cell in top left position
    By Hein in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-07-2009, 05:48 AM
  7. keep active sheet view and cursor position
    By killa47 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-08-2005, 05:20 AM
  8. Freezes chart position with scrolling
    By PauloEd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-11-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1