+ Reply to Thread
Results 1 to 11 of 11

Split view crams UI elements together

  1. #1
    Registered User
    Join Date
    06-14-2018
    Location
    San Diego, California
    MS-Off Ver
    365
    Posts
    5

    Split view crams UI elements together

    At work I deal with several very large worksheets in the same workbook, which basically requires me to use split view to navigate efficiently. The only problem is what split view does to your navigation bar:
    excel worksheet bar.PNG

    As you can see, the scroll bar for the left pane of split view is jammed into the same space as the worksheet navigation. The scroll bar is now useless because it's tiny and too sensitive. The worksheet navigation is also useless because I can't see more than a few sheets at a time. (I know you can right click on the worksheet navigation arrows to get a pop-up menu, but that's nearly as slow as scrolling through the worksheet bar if I'm constantly navigating between sheets.)

    Is there any way to separate the scroll bars from the worksheet tabs so they're not jammed into the same space? I have searched on and off for a couple months now and have never found anything online about this.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Split view crams UI elements together

    Hi ewpcasey and welcome to the forum,

    I've created a short example workbook for you and included some VBA. First behind the first sheet, I test for a double click on a cell. If the cell is in column B I look in the energy sheet for the value clicked on. If double click in Column C it looks in the Wattage sheet. To see the code do an Alt-F11. I think VBA might be what you want for your problem.

    Jump to other sheet value by double click VBA.xlsm
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    06-14-2018
    Location
    San Diego, California
    MS-Off Ver
    365
    Posts
    5

    Re: Split view crams UI elements together

    Thanks for the suggestion and the for welcome, MarvinP, but unfortunately that solution doesn't really fit how these worksheets are used. The LE table basically pulls information from about half a dozen, several-hundred/thousand line item worksheets and compiles the data into a 70-column series of calculations. The results of that calculation are then pulled to multiple other worksheets that do more acute analysis on particular components of the LE table. Basically, I need to be able to quickly get to every worksheet from every other worksheet so I can edit the information or formula there, or pull results.

    I have thought about using VBA to make a set of navigation buttons, but since these tables are also shared with a lot of people (including outside the company I work for) and I don't have a security certificate, it might be a problem.

    Is there absolutely no way to move the worksheet tabs and scroll bars into separate UI components?

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Split view crams UI elements together

    Hi,

    Searching the net for a possible answer and found:
    https://www.excelforum.com/excel-gen...croll-bar.html

    I think the only way to do this might be using VBA and a Model Scroll bar that doesn't disappear. I've never seen one of these but Excel is big...

  5. #5
    Registered User
    Join Date
    06-14-2018
    Location
    San Diego, California
    MS-Off Ver
    365
    Posts
    5

    Re: Split view crams UI elements together

    I'll look into it.

    Thanks for your help.

  6. #6
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Split view crams UI elements together

    Have you considered the "New Window" option on the view tab instead? Lets you open multiple independent windows for the same file. Its handy for looking at the same sheet in multiple places or multiple sheets. Its especially nice when you have a multi-monitor setup as you can spread them around the screens.

    If you have Windows 8.1 or Windows 10 (Win 7 is only 50/50), the snap feature would let you gracefully and quickly "snap" the windows either 50/50, 25/25/50 or 25/25/25/25 on a single screen. You can use Win+Arrows (ex: Win+Right arrow) in various compinations to snap the windows or using the mouse, click and drag the title bar to an edge or a corner of the screen. Doing a single window will then put thumbnails in the remaining space letting you pick another window to fill it. Makes window management...a snap (pun intended).

    You mentioned needing to move between sheets easier. There are some built in ways many people are not aware of to make this easier with many sheets. One example is right clicking the area to the left of the sheet tabs at the bottom of the screen. Doing so opens a dialog with a list of all the sheets which you can use to activate any other sheet. Another possible way is to hyperlink to pages. On each sheet putting a hyperlink to either a "table of contents" with links to every other sheet or a link to the next logical sheet if there is a desired progression. With the TOC/hyperlink method you basically have a link someplace on each page back to the TOC and the TOC links to everything else making moving between sheets as simple as 2 clicks.

    The only other thing I can think of is using VBA, making a form that lists all the sheets and using that to move between them. Thats basically the same thing as the built in feature to move between them however.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  7. #7
    Registered User
    Join Date
    06-14-2018
    Location
    San Diego, California
    MS-Off Ver
    365
    Posts
    5

    Re: Split view crams UI elements together

    The problem I always seem to run up against isn't just that there's a lot of worksheets, it's that the worksheets themselves are quite large (vertically and horizontally) and need to be edited in multiple locations. Placing access points on the worksheets isn't quite as useful because I need to navigate to it to use it, or dedicate precious screen space to keeping it up. That's why I was looking for a way to change Excel's UI.

    However, you got me thinking, if I can arrange my desktop in such a way that I can access everything at once, this won't be an issue anymore. I might just ask for a third screen, since I'm already using two with two windows both in split. I'll see about arranging my setup better as it is now, though.

    Again, thanks for the suggestions.

  8. #8
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Split view crams UI elements together

    If the areas you need to goto on any given sheet are always at/around the same cell(s), you can use named ranges and the name box to quickly go to it.

    In case your not familiar with named ranges and the name box its as follows; you can give a cell or a set of cells a name, like "mynamedcells" as long as you follow the rules for named ranges name syntax (no spaces, no reserved names (like A1), no special characters, maybe stuff I didnt think to mention). You can set the name in many ways, the simplest is using the name box, which is the box to the left of the formula bar with the name of the currently selected cell in it. Simply select the cell(s) you want to name and then type that name in the name box and hit enter. Now regardless of what sheet you are on, you can type that name in the box, hit enter and it will take you to that sheet/cells. Keep in mind you cannot have 2 named ranges with the same name for this to work.

    To view/manage all named ranges you can open the name manager on the Formulas tab.

    I would recommend keeping the amount of named ranges to as minimal as possible. A dozen, 2 dozen, 50...maybe even 100 are likely fine. 1,000 or more is likely to cause more problems than it fixes. Maybe reserve this for areas that are static in position and that you need to go to frequently but do not want to keep on screen 24/7.

    Take as many monitors as they will give you and your computer can handle. The more screen real estate you have the easier it will get (presuming you are the type who can manage many monitors without getting overwhelmed).

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Split view crams UI elements together

    My son got me a 4k monitor about a year ago and I'm amazed how much real estate it gives me. I have a laptop and use it as monitor number 1 and the 4k is "Extended" (not duplicated) to use both monitors with different views of programs. I can also drag and snap different programs to split the screen or even have 4 different programs running by dragging them to the corners.

    https://www.howtogeek.com/181681/4-h...ndows-desktop/
    https://www.tekrevue.com/tip/windows...splay-scaling/

  10. #10
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Split view crams UI elements together

    Marvin brings up a good point as well.

    A 4k monitor is essentially = 4x 1080p monitors when you snap windows to the corners. I have seen some 4k monitors that even have a setting built in so the computer treats it as 4x 1080p monitors natively. Not sure if you can get work to spring for 4k, but it could prove helpful

  11. #11
    Registered User
    Join Date
    06-14-2018
    Location
    San Diego, California
    MS-Off Ver
    365
    Posts
    5

    Re: Split view crams UI elements together

    I'll see if they will be willing to shell out the money for a 4K monitor for me. Maybe I'll get one myself...

    Zer0, I really like the named ranges suggestion. I've used them before in formulas but didn't know you could use them for quick navigation. I will definitely check that out!

    Thanks for the suggestions all around, folks. You've both been very helpful.

+ 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. To split a string which are html pulled elements
    By gochel in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-18-2013, 08:00 AM
  2. Excel 2007 : Split sheet view twice
    By RAGUS in forum Excel General
    Replies: 1
    Last Post: 12-11-2011, 05:34 AM
  3. Copying cells in split screen view
    By Rogue7 in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 10-13-2011, 02:32 PM
  4. Replies: 1
    Last Post: 04-12-2011, 02:47 AM
  5. Reorder elements a cell using arrays, split & join
    By k8_dog in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-12-2011, 01:58 PM
  6. [SOLVED] multi split view
    By MartaH in forum Excel General
    Replies: 1
    Last Post: 05-04-2006, 05:55 PM
  7. Replies: 2
    Last Post: 07-29-2005, 08:05 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