Write Contents of an Array Derived from a spreadsheet macro into a Fixed Display Window, ( User Form ??? )
. Hi,
. I need help in somehow "fixing" a Display in the screen as I scroll up and down a large Sheet.
. The following may be asking a bit much. Possibly with VBA it is impossible. But I am amazed in the meantime what some experts have got VBA to do here....
.
. Thanks to lurking and participating in ExcelForums I have manage to create a fairly large file which I need to help my wife in her necessary diets.. ( I shall certainly “make it public” here when finished if it could be of use to anyone. )
. It is slightly over the 1MB attachment limit so I enclose it here
https://app.box.com/s/fqmlb0e1q194einwubiifqwj20jb97iq
. (Initially on opening, (after the normal macro activation warning) , it asks if it should be initialised, that is to say all entries cleared. It is OK to do that! )
.
. Here, a small extract from Sheet1 after initialization:
Using Excel 2007
- A B C D E F G 873254. Mirabellen 54. Mirabellen 8733 8734Mispeln Kcal 15,42 300 8735 Fett 0,026 0,01 873655. Nashi-Birne Eiweiß 1,044 200 8737 Koh 0,663 10 8738Nekterine Zucker 0,663 0,1 8739 Ballastoffe 3,835 100 874056. Oliven Natrium+ 0,07 0,55 8741 874257. Orange 8743 874458. Papaya 8745 874659. Passionsfrucht .
Sheet1
. Briefly, to use, one types in column C , some amount ( Per 100g ) for the various foods. I have then written a “Worksheet_change type” program which kicks in and then works out a very extensive analysis of the Nutrition ( abbrev. Nuts ) Values.
. Here, a small extract from Sheet1 after Adding 200g Orange in Column C ( The uploaded File comes up in this view and asks if it should be initialised )
Using Excel 2007
- A B C D E F G 873254. Mirabellen 54. Mirabellen 8733 8734Mispeln Kcal 107,42 300 8735 Fett 0,426 0,01 873655. Nashi-Birne Eiweiß 3,044 200 8737 Koh 17,538 10 8738Nekterine Zucker 17,403 0,1 8739 Ballastoffe 8,235 100 874056. Oliven Wasser 173,36 21 8741 kalium 0,331 2 874257. Orange 2 Natrium+ 0,072 0,55 8743 Kalzium 0,08 1.......................
Sheet1
. In both screenshots you see what I call the output “Box”. ( For now pasted arbitrarily to columns F-G, staring at row 8734, and extending down. ). This shows the final information for all taken Foods. It is updated by the Worksheets_Change program every time a new entry is placed in column C.
. My basic problem is that when I scroll up and down the very large Sheet1, I lose sight of this “Box”.
. I have no idea how to “Fix” this in some “Display Window”
. With mind to further developments in the code, I have tried to write the program for now as flexible as possible. If you have time to Look through the Workseets_change Code ( for Sheet 1 ) you will see that I extensively build up Various Arrays containing all the information I may need. (Often things are seen to be done 3 times corresponding to 3 Ranges for: General Foods; Vegetables; and Fruit )
. Relevant for this Thread is just the Array right at the end of the program:
ArrDisplaySP()
. This is built up right at the end of the program. This is used for the “Box” shown in the Screenshots above. This is, as mentioned, for now pasted into the Spreadsheet
. I expect there may be a way to Put this Array into a Display that remains visible always. This might be using some “User Form”. Unfortunately this is a Lion’s Leep way above my abilities.
. Can any one give me a solution, or at least point me in the direction. I have no Knowledge whatsoever of User Forms, or the like.
. A Pipe dream would be to further have a “Button” in this display which would run the program, or part thereof, rather than the Worksheets_change Method. This is because on my computers the update takes several seconds. With a “Button” I could choose to run the program, or at least part of it, after several entries. A pipe Dream of a Pipe Dream would be a second Button to select whether I want instant updating through worksheet_change or updating through clicking of the first Button.
. Can anyone Help. I am asking a lot here, so there is certainly no rush.
. Many Thanks.
Alan Elston
EDIT: A reduced size File I have managed just to attach.
Bookmarks