+ Reply to Thread
Results 1 to 9 of 9

Contents of an Array Derived from a macro into a Fixed Display Window, ( User Form ??? )

  1. #1
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,426

    Contents of an Array Derived from a macro into a Fixed Display Window, ( User Form ??? )

    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
    8732
    54. Mirabellen
    54. Mirabellen
    8733
    8734
    Mispeln
    Kcal
    15,42 300
    8735
    Fett
    0,026 0,01
    8736
    55. Nashi-Birne
    Eiweiß
    1,044 200
    8737
    Koh
    0,663 10
    8738
    Nekterine
    Zucker
    0,663 0,1
    8739
    Ballastoffe
    3,835 100
    8740
    56. Oliven
    Natrium+
    0,07 0,55
    8741
    8742
    57. Orange
    8743
    8744
    58. Papaya
    8745
    8746
    59. 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
    8732
    54. Mirabellen
    54. Mirabellen
    8733
    8734
    Mispeln
    Kcal
    107,42 300
    8735
    Fett
    0,426 0,01
    8736
    55. Nashi-Birne
    Eiweiß
    3,044 200
    8737
    Koh
    17,538 10
    8738
    Nekterine
    Zucker
    17,403 0,1
    8739
    Ballastoffe
    8,235 100
    8740
    56. Oliven
    Wasser
    173,36 21
    8741
    kalium
    0,331 2
    8742
    57. 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.
    Attached Files Attached Files
    Last edited by Doc.AElstein; 06-06-2015 at 03:51 PM.

  2. #2
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,426

    Re: Contents of an Array Derived from a macro into a Fixed Display Window, ( User Form ???

    Bump
    . Hi,
    . I found a limited solution to my problem in the meantime by use of the status Bar..
    . But I am still looking for a full solution. Can anyone help get me started?

    . To summarize: A Worksheet_Change Code updates the contents of an Array (based on entries in column C of Sheet1 of this file
    https://app.box.com/s/fqmlb0e1q194einwubiifqwj20jb97iq )
    . I need to see the contents ( or at least the first few “rows” ) from this Array constantly.
    . Currently I write therefore the contents of this array to an arbitrary part of the spreadsheet. This is done at the end of the Worksheet_Change Code once various calculations have updated the values of this Array.
    . The file is very large and is likely to become larger. I Therefore lose sight of the Outputted Array when I scroll up and down the file. I prefer not to output it to multiple parts of the Spreadsheet.
    . I am looking therefore for some way to keep the display of the contents of this Array fixed as I scroll up and down. My original idea, as discussed in post #1 was a User Form, but I have no idea how to get started on that or any other way of Writing the Contents of an Array Derived from a spreadsheet macro into a Window which remains fixed as I scroll up and down the spreadsheet. (Possibly even a Visual Basic solution could be appropriate. But again, I have no idea how to get started on that. ( I do have VB 2008 and VB 2010 installed on my computer ) )
    . Can anyone help?

    . Thanks again
    Alan Elston.

    P.s. here is part of the Worksheet_Change Code. The important line shown in orange at the end is the code line I would like to replace with some way of writing to a Window “Fixed” somehow in the screen. The full code is in the Sheet1 Module of the uploaded file
    https://app.box.com/s/fqmlb0e1q194einwubiifqwj20jb97iq



    Please Login or Register  to view this content.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2013 Win7
    Posts
    6,265

    Re: Contents of an Array Derived from a macro into a Fixed Display Window, ( User Form ???

    I think you just want to put the array into a listbox on a modeless userform.

    P.S that's a lot of code for a change event handler - doesn't it slow your workbook down?

  4. #4
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,426

    Re: Contents of an Array Derived from a macro into a Fixed Display Window, ( User Form ???

    Hi Kyle,
    . Thanks for the reply, appreciate it
    Quote Originally Posted by Kyle123 View Post
    I think you just want to put the array into a listbox on a modeless userform…..?
    . I guess that sounds like that would look finally like what I was thinking of. Hence my mention of a User Form in my original request. Unfortunately I have no idea how to go about that, and so was hoping someone with Userform knowledge could help get me started on that. - I am a real amateur working on this for private use and any profi help is always very gratefully received. ( A couple of years ago as I first started on this project with no idea what so ever about computers etc., I started with a simple spreadsheet looking similar to the File I uploaded. I had no programming experience. As I tried to speed things up with programming I thought of using Visual Basic along with big text files for my project and did a short VB evening course. ( But then I went over / back to VBA. ) So I have some vague memory of a Userform, as this I remember was one of the main “things” that VB was about ).

    . If anyone could “do a “modeless “ userform” for me on my file I would be very grateful. I guess that was what I was asking for. Maybe your hint in the direction of a “modeless“ could help someone to see what I am looking for.
    ….

    Quote Originally Posted by Kyle123 View Post

    P.S that's a lot of code for a change event handler - doesn't it slow your workbook down?
    . Any suggestions for improvement would be great, - but I thought I had the code fairly efficient and flexible:

    . a) I allow selection of how many Nutrient columns are selected ( variable lc ) – In the practice that could go to over 3500, but often under a 100 is adequate.

    .b) Based on all my learning’s in the forums, I extensively capture, and paste out to the spreadsheet in .value “one liners”, and do all my workings in VBA Arrays to avoid “slamming in the brakes” as you say with spreadsheet interaction.

    .. It does take 1 second or two to update on the old XP and Vista Notebooks I am still hoping to use, - (it is noticeably better in my new 64 bit machine…) - hence in my original request I mentioned that a “Button” on the user form to enable / disable the events parts ( Parts 2) – 5) in the code ) which recalculate / update the Array would be ideal. Again this is way above my abilities.
    .
    . Thanks again for replying.
    Alan.

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,301

    Re: Contents of an Array Derived from a macro into a Fixed Display Window, ( User Form ???

    Here's a simple demo: http://1drv.ms/1KNC8cm

    It has a userform called ufResults which just contains one listbox called lstResults.
    I then added a new routine:
    Please Login or Register  to view this content.
    and altered the lines in your worksheet_change code to just use:
    Please Login or Register  to view this content.
    instead of outputting to a range and the status bar.
    Remember what the dormouse said
    Feed your head

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,426

    Re: Contents of an Array Derived from a macro into a Fixed Display Window, ( User Form ???

    Thanks Rory! !!
    . I think this is the sort of ”Resultat” I was looking for. Brilliant. Very grateful. Thanks for taking the time to look at what is a bit of a “Holy Cow” File and code.!!


    . 1) Very minor point: I was / am bit puzzled that I could not find the code Sub UpdateResultsDisplay(arrData) listed initially in the usual way.. . I noticed that by changing it to this UpdateResultsDisplay() it was then listed. Similarly the code works with a Fuction instead, Function UpdateResultsDisplay(arrData) ( which also is not listed). I guess this is telling me that with something in the () a Sub is like a Function, only difference that it does not return a result, rather as in this case it just does something. So this then makes it a Private Sub rather than a Pubic one,
    .- is that all sort of correct ?

    . 2) The “thing” I see in the VB Development Window is empty, - is that what Kyle meant by a “modeless “ userform” – in English does that mean an empty Userform just stays there after Userform.show, ( which is exactly what I was looking for. )

    . 3) Now I see how this “Fixed” window idea implemented with a Userform works, I guess my idea of a “Button” on the form to turn on or off the code parts 2) to 5) ( which calculate/ refresh arrData is not really possible? ) – It would involve some Flag Boolean variable that would be “switched” from the Userform to True or False, and then the code would be modified thus: ( Pseudo Code )

    If Flag = True Then
    ‘Do Parts 2) to 5)
    Else
    EndIF


    . The problem I guess is that Flag is reset once the code stops. (I guess the only approach possible would be to remove those code sections and place them in a Sub which ran from a button, ( Possibly in” that Resultat / ufResults Userform ) . This Button would then be hit every time a refresh / update was necessary. )
    . ( I guess a “bodge “ solution would be that hitting the button assigns the .Value of some spare cell in the sheet to “True” or “False”. This would then be looked at in the Psuedo code bit thus:

    If cell ( y, x).Value =”True” Then
    ‘Do Parts 2) to 5)
    Else
    EndIF


    - Or is it possible to have a variable given a type and value by a Button on the Userform, which then remains after the code stops?.
    . ……..


    . Thanks very much again. I will be going through that code in some detail. This has been a great help in getting me started in an “unknown area” of VBA for me.

    Alan.
    Last edited by Doc.AElstein; 08-04-2015 at 09:52 AM.

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,301

    Re: Contents of an Array Derived from a macro into a Fixed Display Window, ( User Form ???

    Quote Originally Posted by Doc.AElstein View Post
    . 1) Very minor point: I was / am bit puzzled that I could not find the code Sub UpdateResultsDisplay(arrData) listed initially in the usual way.. . I noticed that by changing it to this UpdateResultsDisplay() it was then listed.
    Routines that take arguments don't appear in the macros dialog, if that's where you were looking.

    Similarly the code works with a Fuction instead, Function UpdateResultsDisplay(arrData) ( which also is not listed). I guess this is telling me that with something in the () a Sub is like a Function, only difference that it does not return a result, rather as in this case it just does something.
    Basically yes. A function doesn't actually have to return a result, but there's no point using a function if it doesn't. Equally, you don't have to use the result of the function - you can simply call it like a sub.

    So this then makes it a Private Sub rather than a Pubic one,
    .- is that all sort of correct ?
    No, that is a completely different thing. Public vs Private refers to the scope of the routine - i.e. from where you can call it. Private routines can only be called from within the same module, whereas Public ones can be called from anywhere in the project (or indeed other projects, if you have a reference set). I emphasise called here because you can actually run private routines using Application.Run from anywhere you like, but you can only call them directly using Call, or simply the name of the procedure, according to the rules of scope.

    2) The “thing” I see in the VB Development Window is empty, - is that what Kyle meant by a “modeless “ userform” – in English does that mean an empty Userform just stays there after Userform.show, ( which is exactly what I was looking for. )
    What thing? The userform contains a listbox, it's not empty. When it is shown, it appears on screen.

    3) Now I see how this “Fixed” window idea implemented with a Userform works, I guess my idea of a “Button” on the form to turn on or off the code parts 2) to 5) ( which calculate/ refresh arrData is not really possible? ) – It would involve some Flag Boolean variable that would be “switched” from the Userform to True or False, and then the code would be modified thus: ( Pseudo Code )
    If you simply want to turn off the Change event code, you can use Application.EnableEvents. Set it to False to turn events off and back to True to re-enable them. This setting will persist until code changes it, or the application is restarted, when it reverts back to its default True value.

  8. #8
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,426

    Re: Contents of an Array Derived from a macro into a Fixed Display Window, ( User Form ???

    Hi Rory,
    . 1) Public Privates Fuction stuff: thanks for all that extra info, it clears that up nicely in my head.

    . 2) I was talking about the “Userform” I see in the development window, which looks “empty”. I see no list box. – Maybe I do see something but am too ignorant to realize / recognize it - I guess I need to read up more on Userforms. – I guess it is your code bit that puts stuff in it, which is then wot I see in the spreadsheet. In the VB Development Window I guess it is just the basic format / size / position etc. that one sees. So that is really down to me to do my homework

    . 3) My first thought was also disabling using something like Application.EnableEvents = False, but then part 1) of the code would also be inactive ( Part 1) calculates each row, which should always be done based on a Worksheet change in column C. ( Parts 2) – 5) then make Arrays of totals based on all the rows filled by part 1). ).
    . So ideally I was looking to put a ( Radio ? ) button on that permanently displayed Userform which would allow to select if just part 1) of the Worksheets_Change code worked, or all Parts. The reason for this is that I would only do the Array calculations to update continuously the Arrays if I really needed to and that would help speed up the code. If I can get any help in that last bit as described in .3) post #6 that would really crown it off, especially if someone could put that “Button” in the form for me, But maybe .3) request is a minor point – if I can get my computers to keep running reasonably fast something like that becomes redundant / unnecessary, !! ( ... But It would be very a very useful learning exercise to see if a “Flag” variable set by a button could be kept after the code stops to use in an IF Flag = ___ Then bit the next time the code runs ....- )

    . Thanks Rory very much again for coming back with all that useful info.

    Alan
    Last edited by Doc.AElstein; 08-04-2015 at 02:52 PM.

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,426

    Re: Contents of an Array Derived from a macro into a Fixed Display Window, ( User Form ???

    ... Hi, Feeding back to this Thread....

    Quote Originally Posted by Doc.AElstein View Post
    ........
    . 2) The “thing” I see in the VB Development Window is empty, ????.
    .. all just a question of clicking somewhere in the big “thing” (UserForm) in the main Development Window, and then seeing what pops up at the left in the in a smaller window which shows lots of Properties for whatever you clicked in the big “thing” (UserForm) . – clicking in the “empty box” revealed that it was the list box , lstResults.
    ... further..
    .. A tool box is to be seen, ( or you can get it clicking on a Tool icon , or view >> Toolbox.....)
    .. you just drag things from it into the big “thing” (UserForm) in the main Development Window
    .. the 5th tool in the first row of the ToolBox was the List Box, put in by Rory.
    ..
    ............................


    .. As For my request .3) from post #6 and #8,
    Quote Originally Posted by Doc.AElstein View Post
    .......
    . 3) Now I see how this “Fixed” window idea implemented with a Userform works, I guess my idea of a “Button” on the form to turn on or off the code parts 2) to 5) ( which calculate/ refresh arrData is not really possible? ) – It would involve some Flag Boolean variable that would be “switched” from the Userform to True or False, and then the code would be modified thus: ( Pseudo Code )

    If Flag = True Then
    ‘Do Parts 2) to 5)
    Else
    EndIF


    . The problem I guess is that Flag is reset once the code stops. (I guess the only approach possible would be to remove those code sections and place them in a Sub which ran from a button, ( Possibly in” that Resultat / ufResults Userform ) . This Button would then be hit every time a refresh / update was necessary. )
    . ( I guess a “bodge “ solution would be that hitting the button assigns the .Value of some spare cell in the sheet to “True” or “False”...........
    .......... let me ‘ave a go now...

    .. Drag a couple of buttons across.. ( I will play around with the Properties shown when I click once on them later )
    .. Double click on one and I get the code “behind it” – that is to say I guess what happens when I click it....
    .. And modify those codes thus:

    Please Login or Register  to view this content.

    ( Rather than set a “Flag” as an arbitrary “True” or “False” value in a spare spreadsheet cell, use the Status bar as a “Flag”, - just a novel idea..! )

    .. Make some minor modifications to my Workshhets_Change code, including the If Then Else End If bit ( Psuedo Code ) :

    If Application.StatusBar = False Then
    'I have a radio ( option ) buttons on the UserForm and can makee status bar False, ( That is to say in it's normal state )
    Else 'Do parts 2) to 5) to calculate, update, display totals Arrays
    ........
    .........
    .......
    End If 'End of recalculating or not recalculating the Totals Arrays

    .... Results
    . a) Option Button1, type in 1 Orange, result : row updated , but not totals in UserForm

    http://i1065.photobucket.com/albums/...ps0hwnlik1.jpg

    .b) Option Button2, re type in 1 Orange, result : row updated and totals in UserForm

    http://i1065.photobucket.com/albums/...psaylfqc5v.jpg

    ....................................................................

    .. So that is a “bodge” solution to my request .3)
    .. I will mark the Thread as solved later, if no one adds a contribution... -
    .. But, If anyone can do a better, more professional solution, I would be grateful and it would make a nice contribution to the Thread. Here the current File:
    https://app.box.com/s/7dalx2amn8mm44jfzj11s8lc1gg8jvk8

    .. Otherwise, Thanks again for the help
    . Alan.


    EDIT: ???? Why is my IMG Code off ????
    Last edited by Doc.AElstein; 08-05-2015 at 09:11 AM. Reason: Images not working????

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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