Reputation: 666
I'm working on a COM add in, but I would be happy to have this solved in VB or VBA. I have user reports that are generated based off selections from a database, then the sheet is added into an existing sheet. I've been told I need to keep the same basic format and leave it in Excel. The problem is that these lists within the form could be of a much longer length than there is space for.
Here is what the current form looks like:
My thought on a solution was to drop the total to the theoretical max items (the total number of options from the DB plus say 50 or so), and then have the middle scroll with a scroll bar.
Is there a way to have 3 separate scroll bars that only scrolls each table (again there is stuff above these that wouldn't scroll)? I've only seen scroll bars that scroll the whole sheet.
Is there a better approach?
I've thought of trying to use the Index function, but I would want the end user to be able and edit the information as they scroll (and an index would be written over in this case).
Thoughts?
Upvotes: 2
Views: 1952
Reputation: 3678
have the data stored in another sheet or below these tables.
reference to this data with an OFFSET
function based on the value in a specific cell for each table for the rows direction.
put a scroll bar (ActiveX or Forms) next to each table that links to the specific cell from mthe step before.
hide the specific cells. hide the sheet/rows with the data.
With the forms scrollbar you can easily link its value to a cell, but not its min and max value, and those you will want to link to the number of records in your set (minus the number of records shown in your table)... With the ActiveX control it looks like the same story. You will have to set the min and max properties through some code in which case I always prefer the ActiveX control as it interacts with VBA much easier (imho).
Upvotes: 2