+ Reply to Thread
Results 1 to 17 of 17

GUI School & Work Project for Analysis

  1. #1
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    GUI School & Work Project for Analysis

    Hi all,

    As another term nears its end at the university, I will be only 1 more year from earning my degree. At this time, I have been working on a project for a GUI class and also for the department I work in at our local utility. Luckily, I've been able to do both in one (and get paid to do it). However, as part of the school project and the report on this project, I need some evaluation. I have attached the workbook with the charting tool and also a word document explaining what to do with it. If anyone here could set aside a couple of minutes to follow along with the document and the charting tool and then fill out the short survey upon Excel close, I would very much appreciate it. If I could buy you drinks I would for this!

    Best Regards:

    Michael
    Attached Files Attached Files
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  2. #2
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: GUI School & Work Project for Analysis

    Hi M - will do it tonight and let you know.
    Blake 7

    If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

    If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.

    http://failblog.org/

  3. #3
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: GUI School & Work Project for Analysis

    Hi Blake, I just realized that there is no way for me to know the results of the survey within the book unless you re-upload a saved version of it. In saying that, no worries (unless you want to).

  4. #4
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: GUI School & Work Project for Analysis

    Hi M - am doing it now. Have a look at instructions 4 and 8

    Note: Be sure to enable Macros when prompted
    2. If the chart sheet is not already visible, click on Sheet3
    3. Click on the Charting Tool button
    Notice that there are 4 list boxes that are categorized by:
    -Primary Products
    -Processed Goods
    -Industrial Manufactured
    -Other Including Fuels/Oils
    4. From Primary Products, find Primary Steel Products
    5. From Processed Goods, find Iron & Steel Scrap
    6. From Industrial Manufactured, find Power & Distribution Transformers
    7. From Other Including Fuels/Oils, find Valves
    8. From Primary Products, find Aluminum

    I used the first one!

    Wow - that's great, fantastic. Really really impressive.

    to answer the Q's

    1. 7
    2. 6 would be 7 if the scroll bar didnt flash - it put me off
    3. 5 (93-2007 colours?)
    4. I'd imagine alot as its an intuative quick ref guide, thumbs up!

    Any other comments - My only feedback would be that it felt a little crammed in, in particular the dates, look at May. Not sure how you would overcome this if you wanted the graphs next to each other. Mind you, ive only got a little one.......... screen.
    Last edited by Blake 7; 03-23-2011 at 04:33 PM.

  5. #5
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: GUI School & Work Project for Analysis

    Quote Originally Posted by Blake 7 View Post
    to answer the Q's

    1. 7
    2. 6 would be 7 if the scroll bar didnt flash - it put me off
    3. 5 (93-2007 colours?)
    4. I'd imagine alot as its an intuative quick ref guide, thumbs up!

    Any other comments - My only feedback would be that it felt a little crammed in, in particular the dates, look at May. Not sure how you would overcome this if you wanted the graphs next to each other. Mind you, ive only got a little one.......... screen.
    Thanks so much for your time. The flashing bar is somewhat of a bother but I don't know how to deselect it. In the properties window there is no option for Take Focus on Click.
    The colours are from working between 03 and 10. The colours were actually my greatest concern but I need 5 that sort of "stand out" for the user.
    As for the crammed in aspect, that was and is an issue but I had to come up with something that fit screens from 15" to around 21 or 22" (which are standard ranges of sizes at work).

    I'm sorry you have a little one
    I'm going to enter your responses into my workbook and again, thanks so much for your time regarding this. If the scales were useful here I would tip ya up.

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: GUI School & Work Project for Analysis

    Please plunge into loops:

    Please Login or Register  to view this content.
    If you restructure the names of the objects in the userform and the cells to which the results have to be written you could reduce this code to:

    Please Login or Register  to view this content.
    'designing precedes coding'



  7. #7
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: GUI School & Work Project for Analysis

    Thanks snb for the tip and I will definitely restructure my code for the survey but the charting tool itself is where I could really use feedback, using the survey questions.

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: GUI School & Work Project for Analysis

    To illustrate what I mean, cfr the attachment.

    I also had a look at your macromodule.
    The same on loops applies there; you can reduce the code drastically:
    I'm not sure whether the hyphenated lines are necessary, because these values apply to all charts and chartseries (I see no variation).
    Please Login or Register  to view this content.
    In sheet3:

    Do not use a button in a sheet to populate a userform
    Use the appropriate userform event to populate the listboxes.

    Never use additem to populate listboxes/comboboxes but use the appropriate method .List.

    So the code in sheet3 will be:
    Please Login or Register  to view this content.
    Ans the Userform_initialize event of userform3 will be:
    Please Login or Register  to view this content.
    You'd better control the user's behaviour in userform3 'on the fly':

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by snb; 03-24-2011 at 08:45 AM.

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: GUI School & Work Project for Analysis

    For the 'melting down' of the sheets3's and userform3's code cfr. the attachment.
    Attached Files Attached Files

  10. #10
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: GUI School & Work Project for Analysis

    Hi snb, wow, thanks for all of the code advice. There is a lot to dissect there. Being a novice I have to ask about:
    Please Login or Register  to view this content.
    what does Step 5 mean as this is the first time I have seen that?
    Next, when you say:
    Do not use a button in a sheet to populate a userform
    Use the appropriate userform event to populate the listboxes.
    I don't know what an appropriate userform event implies. I am going to work your suggestions into the live workbook and see how it goes but for this particular project I have to let it be as I am presenting it on Monday. Also, I've spent hours (without coming on this forum) trying to control the user's selections and I cannot wait to do as you suggested in that regard. Thanks so much!

  11. #11
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: GUI School & Work Project for Analysis

    Hi snb, when checking out your revised Book1, I get a "Wrong number of arguments error" here:
    Please Login or Register  to view this content.
    which is located in the SubmitButton_Click sub. Left is highlighted.

  12. #12
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: GUI School & Work Project for Analysis

    Same here - I was being nosey!

    I do like his revised buttons though, the ? and the colours!

  13. #13
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: GUI School & Work Project for Analysis

    Please note: I don't give an errorless alternative; it's a suggestion how you can improve your code.

    You can check out this easily yourself: instead of
    Please Login or Register  to view this content.
    use
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Check The VBEditors Help (F1) or your standard VBA-book.(e.g. Guy Hart Davies)

  14. #14
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: GUI School & Work Project for Analysis

    Step 5 means to increment by values of 5, the default is, of course, step 1.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  15. #15
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: GUI School & Work Project for Analysis

    I don't know what an appropriate userform event implies
    To initialize means: when loading the userform ( using load userform3 or userform3.Show for the first time) set all it's properties to a default value if they haven't been set already in the properties you gave them during designing. That's the userform_Initialize event. It will be triggered once (loading the form)

    The Userform_activate event will be triggered every time the userform is being shown after it has been loaded already: userform3.show.

    See the VBEditor's help for all events of userforms.

  16. #16
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: GUI School & Work Project for Analysis

    @Mordred

    I couldn't resist playing around with your project.
    Although I aimed at keeping the functionality most of the code has evaporated.
    Because of the use of VBA I refrained from using named ranges, helping cells, linked cells etc.
    In this case, if you see the code: what you see is what you get.

    cfr the attachment.

    NB. I disabled the code in the Workbook_BeforeClose event. But you can enable that easily.
    Attached Files Attached Files
    Last edited by snb; 03-25-2011 at 06:49 PM.

  17. #17
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: GUI School & Work Project for Analysis

    @ snb

    Thanks for your time on this and sorry I haven't responded a lot early regarding this but I have been out of my mind busy. I checked out your version and have issues with it regarding improving my code. For instance, yours will not load all selections for some reason. I can choose up to 5 (or more)selections in the form but only two will show in the charts. I've looked at your code and am not really sure about why this problem. Plus, the version that I have created accommodates changes on the fly because of the named ranges, in regards to switching from monthly to quarterly and annual.

+ 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