Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    New Lounger
    Join Date
    Jun 2014
    Posts
    20
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Userform chart from data sheet

    Hello,

    I have recieved a ton of help from RG and Maub on this project through another thread on this site. This has been a terrific learning expierence for me as I have learned alot, but......I am back with my hand out asking for more direction and help.
    PM ROUTE..xlsm
    1. Can you look through this program and give me any suggestions as far as funtionality (or anything really)
    2. As you can see there is a button on the start sheet that is labeled "Trends". Ideally, I would like this to call the UFTrends userform and when the user clicks one of the buttons at the bottom, (this is where it gets difficult) it looks through the asset DATA sheet and the Air Compressor DATA sheet for any records of this asset and creats a line chart with a previous data. As you will see, the buttons on the bottom of the UFTrends userform match some of the column headers on the sheets. I have started working on it but I cant get anything to populate in the chart area. I am sure I am way off course.

    P.S. the password to run "Application.visible=true" is bulldogs (click ADMIN button at top left)

    Any direction you can give me would be wonderful!!!!

    Jay

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Something wrong with the file.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Scubajay,

    Wow! You have come a long way with your project. Very impressive! To get your workbook to open, I had to disable some problematic code. It is a nice touch to have your form visible with the excel window hidden but you can run into some unexpected results if you open a second spreadsheet. Just something to think about.

    When opening the UFTends form, in the initialize routine you are loading the asset textbox with the value of a listbox2 from UserForm1. I would add some type of verification that SAP1, 2, or 3 has been chosen and an asset from that grouping selected.

    If I understand you correctly, you would like to find to find records that match the asset listed in the asset textbox with the same assets on your 2 sheets. At that point it appears that you looking to select the data criteria by using the buttons at the bottom. Essentially, each button represents a column. Ami I understanding it correctly?

    Maud

  4. #4
    New Lounger
    Join Date
    Jun 2014
    Posts
    20
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Maud,

    If I am only expecting them to ever have this speadsheet open, do you forsee a problem using the visibile.false code? They are going to be using this on tablets and only to do thier routes.

    You are understanding exactly what I am trying to do. Basically trend the data on the 2 sheets filtering it by the buttons on the bottom of UFTrends and the asset selected.

    I tried to take a stab at it but i am running into all kinds of problems.

    Thanks again for all your help Maud

    Jay

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Scubajay,

    A couple of things so far:

    1. You have referenced the "Microsoft Office Web components" which is creating havoc on some of your code: "DateTextBox = Format(DateTextBox.Value, "mmm-dd-yyyy hh:mm")" as well as Password = InputBox("Enter Password.", "Log-In"). With it disabled, the code works fine. It might be working on your system if you have the library present but there is no guarantee that others will. Unless you have a reason to have it, try de-referencing it.

    PM route.png


    2. When the Userform1 opens, it should have a default of SAP1 assets selected and populating Listbox2. This will also serve as a prompt to select an asset from the list. Changes to do the would include:
    Code:
    Private Sub UserForm_Initialize()
    'xl_hwnd = FindWindow(vbNullString, Me.Caption
    'If xl_hwnd <> 0 Then
    'lStyle = GetWindowLong(xl_hwnd, GWL_STYLE)
    'lStyle = SetWindowLong(xl_hwnd, GWL_STYLE, lStyle And Not WS_SYSMENU)
    'DrawMenuBar xl_hwnd
    'End If
    DateTextBox.Value = Now
    Me.ListBox2.RowSource = "SAP1_"
    OptionButton1.Value = True
    DateTextBox = Format(DateTextBox.Value, "mmm-dd-yyyy hh:mm")
    End Sub
    Note: I have commented out some code that does not appear to be necessary and that was preventing the workbook from opening. I suspect that this was the same issue that dguillett@gmail.com was having

    3. Adding the following code to your trends button will force a selection in listbox2. Without a selection made your code will fail and the program closes.
    Code:
    Private Sub CommandButton8_Click()
    If ListBox2.ListIndex = -1 Then Exit Sub
    UFTrends.Show
    End Sub
    With the 3 adjustments above, your workbook opens and transitions smoothly for the parts that I tested.

    4. Where do you want to draw your graphs? On the form or on one of your sheets?

    HTH,
    Maud
    Last edited by Maudibe; 2014-09-22 at 23:24.

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    scubajay30 (2014-09-23)

  7. #6
    New Lounger
    Join Date
    Jun 2014
    Posts
    20
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Maud,

    Thanks again for your suggestions. I have completed all three. It is wierd that I was not having any trouble with that reference on any computer I tried it on but if I do not need it referenced than I got rid of it.

    To answer your question to #4, I would like the ability to eventually print these graphs so where ever you think it is best to draw them is fine with me. If we use a sheet, can it be hidden and still work? What are the draw backs from drawing it on the form (I suspect it will slow the form down).

    Thanks again for your help!!
    Jay

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Scubajay,

    I have the setup laid out.

    1. I just need to know how you want set up your x and y series. I am guessing that your y axis will be the columns that the buttons indicate, where the x axis is column A (Date)

    2. Could you name the rest of your buttons and indicate to which column of data they are referring to? In particular, those in the Pressure group

    buttons.png

    3. As far as the graphs, you hit it right on the nail. Create them on a hidden sheet, send them to the form as an image, and print them from the sheet. Do you want to put a print button on the UFTrends form?

    4. by clicking on the buttons, do you want the capability to have more than one series visible (see image)? If so, would coloring the buttons to match the series color be your choice? Although not shown, the chart would be labeled Vibration, Temperature, or Pressure.

    chart100.png

    5. Is there any reason why you have calculations set to manual? This would prevent your charts from auto-updating as the data changes.

    Answer these questions and I can start to build.

    Maud

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    scubajay30 (2014-09-24)

  10. #8
    New Lounger
    Join Date
    Jun 2014
    Posts
    20
    Thanks
    9
    Thanked 0 Times in 0 Posts
    1. I think the way you have would be the best-Y axis is the column data and the x axis is the date the record was taken. This would be for all charts.

    2.The buttons are going to be the same for the tempurature as they are for the vibration. The pressures however will be different and not all assets will have data to graph. I will need more time to figure out what pressures are worth trending so I can just adjust the code when I have that figured out. for now, lets just call one Oil Pressure and one Dpressure. (will need to calculate the delta later).

    3. YES!! I would love the ability to print each graph. And maybe even the ability to print all graphs from a single button....

    4. I like the way you have it pictured. Wasnt thinking about putting more than one series on a chart but it could provide benefit to see the whole system trend. Again what ever way is easier for you then I can adjust from there. (I dont want to take up to much more of your time)

    5. Honestly, there is no reason at all that I know of.

  11. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Scubajay,

    Thanks for answering the questions. I have the filtering by buttons completed. Can you post a sample Asset Data sheet with a fair amount of data that I can use for testing the graph production?

    Maud

  12. The Following User Says Thank You to Maudibe For This Useful Post:

    scubajay30 (2014-09-25)

  13. #10
    New Lounger
    Join Date
    Jun 2014
    Posts
    20
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Here you go

    Asset DATA Example.xlsxHere you go. This data set comes from SAP3.

  14. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Scubajay,

    Thanks for the sample data. I copied it over to my working copy (from post#1) and nothing worked any more. I then realized that the Asset column on the Asset Data sheet was in a different format from the original which did not have the asset # appended. Because there are no matches (ListBox2 and Asset column), there was nothing to filter. I also noticed that the sample data is a totally different data set. Do you have a matching Start sheet for this new sample? If you plan to keep the appended asset number, then I will need to figure a new way to cross-reference.

    Original data with no asset #
    Asset1.png

    New data with appended asset #
    asset2.png

  15. The Following User Says Thank You to Maudibe For This Useful Post:

    scubajay30 (2014-09-29)

  16. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Scubajay,

    Although limited to SAP1, I added additional sample data to get a better view of how things worked. I used your scheme of a hidden sheet (they are all hidden actually) and called it "Data" to supply the graph. When the user opens the workbook and the UseForm1 appears, the user selects an asset from the listbox. Behind the scenes the Asset Data sheet is filtered to meet the asset selection made. The filtered data is then transferred to the Data sheet and the graph is updated. When the user clicks on the UFTrends button on UserForm1, the UFTrends sheet opens with a blank graph of the asset.

    pmr1.png

    Clicking on the parameter buttons adds the series to the graph for that asset. You can have any combinations of parameters at a time. The buttons act as toggle switches which illuminate when selected.

    pmr2.png

    A Save button takes a snapshot of the graph and saves it to the same directory as a .gif file for later use
    A Print button will print the graph
    A close button will close the UFTrends Form

    The illumination animation are actually small .gif files that are swapped out depending on the button's state. They must all be kept in the same folder as the file. Unzip the folder anywhere on your computer which contains all the supporting files along with the main file.

    PMRoute1.png

    Let me know if you need help integrating it into your file.

    Maud
    Attached Files Attached Files

  17. The Following User Says Thank You to Maudibe For This Useful Post:

    scubajay30 (2014-09-29)

  18. #13
    New Lounger
    Join Date
    Jun 2014
    Posts
    20
    Thanks
    9
    Thanked 0 Times in 0 Posts
    WOW! Maud you are the best. I honestly thought you were going to come back and tell me it could not be done. I do not think I could tell you thanks enough!

    I will play around with it for the next day or so and let you know if I have any issues. I would love for you to explain exactly what is happening so that I may learn but I understand that would be very difficult to do.

    Again, Thank you!

  19. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Scubajay,

    I commented the code fairly well and from what I have seen you do with this program, I think you will easily follow. Making it work with your alternately formatted data may take some tweaking. Feel free to PM or email me if you need assistance.

    Maud

  20. #15
    New Lounger
    Join Date
    Jun 2014
    Posts
    20
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I think you have way to much confidence in me.....LOL

    I have found a bit of an issue. For the graph, the typical reading for vibration will be between 1 g and 20 g where a typical reading for a temperature would be between 150F and 250F. If you insert two records, one with a vibration reading of 1 and one with a temperature reading of 250 the graph basically becomes useless as the range is to great. Is there a way to over come this or should I put some administrative controls in place that will not allow them to have that far of a spread?

    I have thought about having two seperate graphs, one for vibe and one for temp but I like the way you have set it up with both on the same graph. This allows me to see the comparison and influence between the two.

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •