Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-01-2022, 07:05 PM
14spar15 14spar15 is offline Scroll list to this point Windows 7 64bit Scroll list to this point Office 2010 64bit
Advanced Beginner
Scroll list to this point
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default Scroll list to this point

Hello, In this sample database I want to be able to scroll the list (On right) upward so the selected letter (on left) is the first row showing on the right list. For example if the red “P” (C4) is selected with a mouse click then the list on the right would scroll to where “Pink” would be on top. If the blue “D” (A14) is selected with a mouse click the list would scroll to where “Deer” would be on top. I’m certain that it had been done and I’m not finding any examples especially with two unique list. In my actual database there will be hundreds of colors listed as well as hundreds of animals listed. Thanks
Attached Files
File Type: xlsm Book1.xlsm (12.5 KB, 5 views)
Reply With Quote
  #2  
Old 08-02-2022, 12:51 PM
p45cal's Avatar
p45cal p45cal is offline Scroll list to this point Windows 10 Scroll list to this point Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Click on one of the letters on the left in the attached and if an entry exists beginning with that letter the whole sheet will scroll so that the top row contains the first entry with that first letter.
There are comments 'hardcoded in the code which means you need to change those lines to suit your actual circumstances.
The code is to be found in Sheet1's code-module.
Attached Files
File Type: xlsm msofficeforums49448Book1.xlsm (21.9 KB, 5 views)
Reply With Quote
  #3  
Old 08-03-2022, 09:56 AM
14spar15 14spar15 is offline Scroll list to this point Windows 7 64bit Scroll list to this point Office 2010 64bit
Advanced Beginner
Scroll list to this point
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Great, this works well. I tried for days to do something ActiveX Controls with no luck. The list seldom changes so changing the parameters in the code-module is absolutely no problem. My actual worksheet is so large that I have actually separated it into this red and blue group. I put my most looked up names in the red list and the others in the blue because I still have 40 to 50 names in the list starting with say an M.
A couple of other things here. Freezing the first five columns in the traditional method does not seem to work. I get the dark grid line between columns E and F but the columns are not frozen. Could this be fixed? Also one other thing that would be nice if possible. It would be helpful if in addition to selecting one of the red search letters, a letter could be entered into cell B1 and this would be the same as selecting that red letter. This letter would be entered from an outside source as the worksheet gets focus. Hopefully, one of the click letters could then still be used if I am not getting the results I was expecting. Perhaps on this added click, B1 would have to be cleared. Probably just need to do this for the red section. Thanks again for the help here.
Reply With Quote
  #4  
Old 08-03-2022, 02:01 PM
p45cal's Avatar
p45cal p45cal is offline Scroll list to this point Windows 10 Scroll list to this point Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

With Split or Freeze panes you won't be able to get asynchronous scrolling in both directions.
If you have a vertical (left-right) split/freeze, their rows will always synchronise.
If you have a horizontal(above-below) split/freeze, their columns will always synchronise.
If you have 4 panes, the ones diagonally opposite to each other are going to be independent of each other.

To get asynchronous scrolling you can open a new window on the same workbook, arrange them on the screen either manually or by clicking some buttons, make sure they're on asynchronous scrolling:
2022-08-03_213507.png

then we can arrange for the code only to scroll the other window.
To do that a tweak to the code; there are 2 lines:
ActiveWindow.ScrollRow = FirstCell.Row
change them both to:
ThisWorkbook.Windows(ThisWorkbook.Windows.Count).S crollRow = FirstCell.Row


That's been done in the attached as well as changes to cell B1 treated as a red letter.
Attached Files
File Type: xlsm msofficeforums49448Book1_02.xlsm (24.5 KB, 10 views)
Reply With Quote
  #5  
Old 08-10-2022, 11:11 PM
14spar15 14spar15 is offline Scroll list to this point Windows 7 64bit Scroll list to this point Office 2010 64bit
Advanced Beginner
Scroll list to this point
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Ok, I think I got this. Thanks for all the help.
Reply With Quote
  #6  
Old 08-10-2022, 11:18 PM
14spar15 14spar15 is offline Scroll list to this point Windows 7 64bit Scroll list to this point Office 2010 64bit
Advanced Beginner
Scroll list to this point
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default Problems combining selected cells

Hello, I want to build a paragraph from the several of the sentences in cells C2:C10 on these different sheets. If I click on say C4 and hit the copy button that sentence is added to C15. Now, I would like to select say C7, then hit the copy button again and it would go back to C15, go to the end of the string that is already there, add one space and then paste C7. I have found that if I go to C15 and push F2 it will put the cursor at the end of the string like I need but unfortunately C7 is then no longer on the clipboard. Is there a fix for this or if there is a better way to do this such as double clicking sentences to add them that’s fine to. >> ALSO<< As I enter any sheet in this workbook I want to run the Clear C15 macro. I am using 60-70 sheets and am adding sheets daily so I hope I don’t have to do something for each sheet to achieve this. Each sheet has the same basic outline so regardless of the sheet, C15 needs to be cleared. I don’t need to store anything in any of the C15 cells on any of the sheets after pasting it in to another application or leaving that sheet. Thanks
Attached Files
File Type: xlsm Book4.xlsm (34.7 KB, 5 views)
Reply With Quote
  #7  
Old 08-11-2022, 04:06 AM
p45cal's Avatar
p45cal p45cal is offline Scroll list to this point Windows 10 Scroll list to this point Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

This is quite a different question; I suggest creating a new thread.
Reply With Quote
  #8  
Old 08-11-2022, 07:47 AM
14spar15 14spar15 is offline Scroll list to this point Windows 7 64bit Scroll list to this point Office 2010 64bit
Advanced Beginner
Scroll list to this point
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Thanks for the reply. I'm not sure how but my intention was to start a new thread but somehow I added it to the end of my last. Thanks for bringing this to my attention.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to scroll Excel list and select random name GreySquirrel PowerPoint 1 07-12-2019 12:06 PM
Is there a way to display quickparts as a panel rather than a scroll list? Very Terry Word 2 04-26-2017 07:08 AM
Scroll list to this point Cannot scroll past specific point in doc sunriver Word 3 06-24-2015 07:00 PM
Page does not actively scroll with click, hold and drag on scroll-bar jbax Word 1 05-09-2013 07:16 PM
Power Point copy-paste number list to RTF editor himanshu PowerPoint 0 02-22-2010 12:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:30 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft