Scrolling Through a Range on A Worksheet

[Ark68]

I suspect this may be a difficult question to explain, and for that reason not only is the topic title a bit off, my I fear my explanation may be a bit convoluted. For that I apologize. But if I can share specific information that may help someone better figure it out, and hopefully provide a solution, please ask before declining an opportunity to make a suggestion.

I have one worksheet in my Excel VBA application that I use as the primary user interface. Pretty much everything I need the user to do to manipulate data happens on this one workshett (ws_gui1). Since there is user interaction on the top rows and right columns of the screen's display, I am trying to avoid any worksheet scrolling.

On this worksheet, I transfer a capped range of data from a 2nd data source workbook and worksheets (ws_cd1). My user interface (ws_gui) can accomodate only 35 rows of this transferred data per "page" (the range of pasted data is limited to B6:AM40) if I wish to prevent scrolling. When my source database exceeds 40 rows, I have the user flip through additional pages (ie page 1 displays the first 35 rows of data in the defined range, page 2 replaces page 1's data with source data rows 36 - 66, page 3 replaces previous data with source data rows 67-102 and so on)

Since the data presented to the user in the display range (B6:AM35) is editable by the user, it is very difficult work with the data when they have to rely on flipping pages, not to mention the amount of code needed to make changes to the whole database (among all the pages) when they do make a change affecting other data.

So my question is this: Is there a means in which I can simple scroll data just through the display range of B6:AM35. Instead of having pages, give the ability for all the data to be available for display in the viewing range? Similar to scrolling a worksheet, but limited to just the range. I think an option might be to introduce a new workbook to replace the display range and just format it to appear to be like part of the primary user interface, but unsure if you can embed a workbook into a worksheet, or superimpose a worksheet from another workbook over an existing worksheet. Some challenges that I foresee with doing that (if possible) is defining, maintaining the size of the window, preventing the user from resizing it; placing the window at the exact spot over the user interface window and preventing the user from moving it, eliminating the window ribbons, menus, formula bars, row/column headers and everything else that defines an Excel Window (including all the features in the bar at the top of the window that hosts the quick access toolbar).

Thoughts? Suggestions? I particularly like the first option if it's doable, but I will certainly take the feedback from people who have experienced and overcame similar challenges.

 

[juddaaaa]

Here's a video I found that might help

 

[Yongle]

My suggestion

Assuming that rows 1-5 are the "rows at the top" and that column N is the rightmost column that the user must interract with

Allow rows 1-5 to always be in view
Place cursor in cell A6 , followed by and View tab\ Split

Force the user to stay within confines of columns A:N
Place code below in the SHEET module
right click sheet tab of ws_gui1 \ click View Code \ paste code into that window

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const C = 14
    Dim T As Range: Set T = Target.Cells(1, 1)
    If T.Column > C Then Cells(Target.Row, C).Select
End Sub

Pages of 35 rows NOT required

 

[Yongle]

My suggestion2

Assuming that rows 1-5 are the "rows at the top" and that column N is the rightmost column that the user must interract with

Allow rows 1-5 to always be in view
Place cursor in cell A6 , followed by and View tab\ Split

Force the user to stay within confines of columns A:N
Unprotect all cells in Columns A:N
Protect the worksheet
Change ONE setting - do not allow the user to Select Locked Cells

 

[Ark68]

Hi juddaaaa and Yongle. Thank you for sharing your expertise. You have proven once again that there is more than one way to do do a task in Excel!
I feel what juddaaaa provided was more flexible for my needs, but will need to figure out how to use what the presenter provider in a VBA environment.

What I noticed in Yongle's suggestions, is with the split, it appears as tough the user now has access to 4 interfaces? Using option 1, I was able to prevent scrolling in the data portion beyond the data, but on the other three, the user could click in those areas and scroll through the entire workbook with no barriers. Although not mentioned in my OP, I failed to mention that 5 of the rows below the data rage (41:46) are also static areas where there is accessible cells by the user. I suppose some creative locking of cells might prevent much of what I'm seeing as potential problems. I'll see how successful I am with the scroll bar approach.

 

[Yongle]

What I noticed in Yongle's suggestions, is with the split, it appears as tough the user now has access to 4 interfaces? Using option 1, I was able to prevent scrolling in the data portion beyond the data, but on the other three, the user could click in those areas and scroll through the entire workbook with no barriers.

"Using option 1... but on the other three ..."
I think you misunderstood - Yongle provided 2 options (not 4)
option1 = post#3 YOU MUST DO BOTH THINGS
option2 = post#4 YOU MUST DO BOTH THINGS

I suppose some creative locking of cells might prevent much of what I'm seeing as potential problems

You are correct - but it may only be part of the solution

Although not mentioned in my OP, I failed to mention that 5 of the rows below the data rage (41:46)

That was a significant omission - I would not have given you suggestion in post#3 if I had known that

Q Does user need to change the values in rows 41:46 or simply be able to see the values?
Q Is there anything else that we need to know ?

 

[Ark68]

Hi Yongle, thanks for the followup ...

Yes, I realize you gave two option, but with the splitting, the initial worksheet is split into 4 separate repeats for that same worksheet. ie the user can see the exact same contents in all four split section when they click within and start scrolling. (thats where I got into the comment about having to be creative in cell locking ... it becomes necessary to prevent the user from being able to select cells in those areas and therefore be abvle to cursor aroiund the split. But all areas outyside the data range of B5:AM40 (increased my data window since my OP) have cells for which the user can edit, so they still need access to those cells (the few remaining unlocked).

In rows 41 - 46 there are cells of calculated cells that must remain visible while the user works in the data range. There are also buttons in that area.
Whether or not there is more to tell you depends on what you need to know. I was lookinbg only for a solution to scrolling an area of B5:M40 so I didn't think knowing the contents of the surronding area was relevant. I didn't realize that splitting would have been suggested as an option, or else perhaps I would have shared that limitation.

I have been following the tutorial that juddaaaa recommended and am adapting it to VBA. I am struggling with applying the the index formula to the data range. Since my testing data doesn't have more than 35 rows (has 31) there is no necessity for scrolling. All the records can be placed in the static data range of B5:AM40,. So for experimentation, I am reducing my visible data range to 12 so that their will be a need to scroll.

I created a scrollbar, named it datascroll.

cnt_rows = Application.WorksheetFunction.Count(ws_cd1.Columns(1)) 'the number of rows in my source data
mxds = cnt_rows - (12 - 1) 'max property for scrollbar calculated as per tutorial recommendation
if cnr_rows > 12 then
    With datascroll
                .visble = True
                .Value = 0
                .Min = 1
                .Max =mxds
                .SmallChange = 1
                .LargeChange = 12 '35
                .LinkedCell = "Sandbox!$D$1"
                .Display3DShading = True
    End With
Else
    With datascroll
               .visible = false
    End With
End if

This remains untested.

My issue is now with coding the index formula to my visible data range. I can do it manually, but I'm not having much luck applying that formula to the cells in the range B5 to B16 (12 rows of data)

=INDEX('[CD_Thu 16-Jul-20.xlsx]CORE_DATA'!A2:$A$31,Sandbox!D1)

, where column B of the visible data range is filled with values from column A in the source data.

Cell B5 populates with the correct value. However, when I copy that formula down 11 more rows to

 

[Ark68]

Disregard the last sentence. Not meant to be there.

 

[Ark68]

Thank you all once again for offering up your expertise. I found an option that appears to be working well so far for me. I am finding these new concepts challenging yet rewarding.