Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 33

Thread: Make me look like an Excel/VBA Rockstar....

  1. #1
    VBAX Regular
    Joined
    Jun 2008
    Posts
    28
    Location

    Make me look like an Excel/VBA Rockstar....

    Hello all, This being my first post here I will start off first by saying thank-you to all of you who have learned how to utilize a product in a much more expert way than I......

    I am a regular user of excel, and am somewhat skilled in making it do what I want, but know ZERO (0) about the vba side of things. A little introduction first..... I am an education administrator for a school of medicine. I am trying to find a way to solve a grade reporting issue, in that grades are too often not filtered into the system in as timely a fashion as necessary. I need to see the grades of our students at the same time as they are entered into the profs spreadsheets, so that I can identify the students who are in trouble.

    What I have done thus far is as follows:

    1) created an "original spreadsheet, with conditional formatting so that if an entry is less than 70 the text is colorred red.
    2) created a duplicate to this sheet, so that as a grade is entered into one cell, it will reflect on the other seperate sheet in a seperate book.
    3) created automatic graphs so as a number is reflected in a cell, an automatic point is assigned on the graph in the corresponding position.

    What I really need to have happen is as follows:

    1) It would be great that if a cell has an entry that is 75 or less an autmatic email is generated at a point in time later...say 120 mins later, that alerts me to this fact.

    2) It would perhaps be even better if the cut n paste method was not the method employed in order to create the spreadsheet so that I can see it.... recall that the original sheet is the profs...(will reside on a shared drive) and the other book hereunto refered as the LAW book... is mine and sits on my computer.....

    I am going to attach the two spreadsheets and see if there is anything y'all can suggest to help me in this matter. In advance I appreciate your help....if this is a time consuming issue.....there could be beer money in it for you...... I see also that I cannot add two files... so I will immediately post the second below this....if it will fit, it is larger I guess beciase it is a paste version?
    Last edited by gregj; 06-02-2008 at 08:09 AM.

  2. #2
    VBAX Regular
    Joined
    Jun 2008
    Posts
    28
    Location
    I was not able to upload this other file......it is 1.5MB....
    If someone has a suggestion?

  3. #3
    VBAX Regular
    Joined
    Jun 2008
    Posts
    28
    Location
    ooops let me reattach after removing some info.....my bad...

  4. #4
    VBAX Regular
    Joined
    Jun 2008
    Posts
    28
    Location

    Help Please?

    Anyone who can help out there?

  5. #5
    VBAX Mentor
    Joined
    Oct 2007
    Posts
    372
    Location
    zip your files together

  6. #6
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    My first impression is that you have an extremly bloated workbook. Why not use just one chart and select your student of interest from a drop down list? Or just apply a pivot table and or a pivot chart?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    VBAX Regular
    Joined
    Jun 2008
    Posts
    28
    Location
    Quote Originally Posted by Aussiebear
    My first impression is that you have an extremly bloated workbook. Why not use just one chart and select your student of interest from a drop down list? Or just apply a pivot table and or a pivot chart?
    I agree it is full, but I am not sure how to do what you are saying with a pivot table.... ?

  8. #8
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Here's something that although built in 2007, but available in 2003 format, may give you some ideas as to what is possible. I'm not an expert in charting, and in fact its more than likely that someone will easily improve the chart type and layout.

    For the purpose of adding test scores I've used the Randbetween function. In this case Randbetween(60,100). For trialing, simply select the cell and drag across and down to fill the other data cells required.

    The chart is located on Sheet5, only because the other sheets were used to experiment with and failed. You can select the student by using the Student Name selector. The name of the Student in cell A3 will change accordingly as will the data in the chart. I've also added a trend line in the chart, which while you only have single test results doesn't do much, its when multiple test results are available that it has an effect.

    Somewhere I once read something about having a series of lines in a chart which are not linked, as in 1st four tests (Biology) are represented by one line, 2nd four tests Genetics) by another etc. It would be nice to see someone have a go at that as a possible result.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    VBAX Regular
    Joined
    Jun 2008
    Posts
    28
    Location

    unable to see the attachment aussiebear

    nope... i got it ... but it won't let me open it... is read only or the server document is stored on is not responding.... I'll try later thanks for looking into this though....

  10. #10
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    This workbook has developed some issues which I am currently investigating. I wil repost the entire workbook as soon as it is resolved
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  11. #11
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Right, the xls version is working.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  12. #12
    VBAX Regular
    Joined
    Jun 2008
    Posts
    28
    Location
    That friggin rocks.... Sure would like to know how to do that. Will it be compatible with other versions of excel. BTW thanks......VERY MUCH.

  13. #13
    VBAX Regular
    Joined
    Jun 2008
    Posts
    28
    Location
    K, now that I have had a few mins to mess with this I am certainly intrigued by it. I a curious if I can do what I had originally planned which is have a seperate version of the sheets and the slider graph page for professors, and one for me such that as they update their grades my grade sheet and slider bar updates automatically or as I open up the workbook. Additionally I need to protect the sheet so that all a prof can do is enter grades. If they need to use formulas to calculate them, then they can do that on their own sheets, but can only paste values into the (shared drive sheet) - which updates my version as mentioned.

    Is there somewhere you can point me to figure this sheet out? This is soooo cool.

  14. #14
    Site Admin VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,008
    Location
    Gregj, i have removed the solved from this post, when you have completed your solution in this thread please mark it solved again!
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  15. #15
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    There's nothing really special about the workbook. Do a Google search for dynamic charting. The data in sheet1 has been arrived at by using the Rand between function so those cells will need to be cleared and unlocked for use by who ever enters the data. On sheet2 be careful of not deleting the link cell I1. (Its font is White)
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  16. #16
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Quote Originally Posted by gregj
    I a curious if I can do what I had originally planned which is have a seperate version of the sheets and the slider graph page for professors, and one for me such that as they update their grades my grade sheet and slider bar updates automatically or as I open up the workbook. Additionally I need to protect the sheet so that all a prof can do is enter grades. If they need to use formulas to calculate them, then they can do that on their own sheets, but can only paste values into the (shared drive sheet) - which updates my version as mentioned.
    I don't know why you can't do this.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  17. #17
    VBAX Regular
    Joined
    Jun 2008
    Posts
    28
    Location
    Aussie,
    Thanks again for your help. I have been messing around with this and sort of "reverse-engineering" it. I can now make my own scrolly etc. What I can not get to work is this: In the worksheet, where all the students names and grades are, if I clear out data, and replace it with my name and add a system of tracking data such as a bunch of really low scores, the data reflects in the chart, but remains there across all names scrolled for. Does this make sense?

    If you look at the attached sheet, you will see that I have messed with John Banahan's data....but this is not reflected in the positioning of the scroll bar. What I am hoping to achieve is that for every time a name is selected using the scroll, the chart indicates that individual students progressive grades across all subject areas.

    Do I have to go in and make indiviidual series, attach the proper cell ranges to data points etc....?

    BTW, I managed to get the data to automatically "link" from one sheet to another....which is what I had originally started with if you recall.... then I had 118 individual charts... <---retarded ] this way is much better. I just hope the community here is still willing to help with this as I am learning a crapload here which is always gratefully appreciated.

  18. #18
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Basic error here. The slider change is triggering a change in the random numbers, not the showing the data relating to the student name. Copy/PasteSpecial Values to see what I mean.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  19. #19
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Add this to a standard module and assign it to the scrollbar

    Sub ChartUpdate()
    Dim Rng As String, Rw As Long
    Dim cel As Range
    Set cel = Selection
    Rw = Range("M1").Value + 2
    'Modify data range
    Rng = "$C$" & Rw & ":$AL$" & Rw
     
    ActiveSheet.ChartObjects("Chart 2").Select
    ActiveChart.SeriesCollection(1).Formula = "=SERIES(,,Sheet1!" & Rng & ",1)"
    'Return activation to sheet
    cel.Activate
    End Sub

    BTW There is a question on a similar topic here
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  20. #20
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Malcolm, I get an error at the Set cel = Selection line. I take it that I stil need to select the data by using the wizard or does the code do this?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

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