Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jan 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB Code for Self scoring test in Excel (VB (Excel))

    Hi there, I am a microsoft office user trainer. I am writing an assessment to test the skills of new starters at our organisation in Excel.
    I am attempting to make this automated and to this end I have created a spreadsheets with step buttons for the user to press (macros), Each step gives them instructions and add stuff to the spreadsheet until they have completed all the task, saved the spreadsheet and sent it to me.
    On a hidden sheet I have written some formulas to test whether they have accomplished each task correctly and to give me a score. Unfortunate some of these I do not know how to do with an Excel formula and suspect I can write some VB coding to do it if I knew how.
    I could like to gather information about sheet 1 which they should have renamed to "Income".

    I need to know
    a) whether they changed the sheet name,
    [img]/forums/images/smilies/cool.gif[/img] the row height of a particular row;
    d) whether they have entered the sum functions correctly into a series of cells;
    d)If they have created a chart and set specific options such the type of chart and if they added the title
    f) the name of the file
    I then want to send them an e-mail (to the address they entered into the spreadsheet), giving their scoring and what training they should do.
    I suspect this is really easy if you know how. I can write if statements to work out what training they should do based on the answer given. My problem is getting the answer into my score sheet without doing it manually.
    Resolving these problems will saved me so much time doing a boring repetitive task every week and enable me to do some really good stuff instead.
    Thanks in advance.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code for Self scoring test in Excel (VB (Excel))

    a)

    In VBA, the following formula will return the tab name of the first worksheet in the workbook:

    <code>
    Sheets(1).Name
    </code>

    The following will return the tab name of the object with the object name Sheet1:

    <code>
    Sheet1.Name
    </code>

    [img]/forums/images/smilies/cool.gif[/img] The following code will return the height of the row containing cell A1:

    <code>
    Range("A1").EntireRow.Height
    </code>

    c) The following code will return the formula in cell A1:

    <code>
    Range("A1").Formula
    </code>

    d) Would have to know a lot more about what you want. Is the chart on a ChartSheet or a chart on a worksheet? If there is more than one chart, which chart are you interested in. What option do you want to know about?
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Jan 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code for Self scoring test in Excel (VB (Excel))

    Dear Legare Coleman

    Thanks for you quick reply.

    My spreadhseets
    has four sheets
    In the first sheet I ask the user to carry out a variety of tasks by providing step buttons, When they click each button new data is displayed as appropriate and instructions are given to the user what to do next. I have recorded / edited macros for this.
    Sheet four is called the Score sheet and will be hidden from the user.
    I am using this sheet to store the score for each questions 1-12.
    1. Name Entered? This is easy to score with a fromula!
    2. Text Formatted? (separate text from name - don't know how to do this with a forrmula)
    3. Column Width changed (there is a formula that does this but it does not apper to update automatically)
    4. Have they copied the formulas with Autofil (Don't know a way to text this one)
    5. Copied Values (don't know how to test this one either) I can see if they have entered the values in the cell
    6. Row Height changed? Your code would do this. I need the result to be entered into my score sheet/cell.
    7. Used Sum Function? Range("CR").Formula would do this but once again I need to know how to get the answer 1 in a cell if they used a sum function.
    8. Copied Formula?
    9. Formatted numbers to currency 0 decimals?
    10. sorted the list? I have written a nested If statement to test this. ( seems to work after the filter has taken place)
    11. Filtered the list?
    12 Created a 3--d Pie chart with a title of "Expenses" on sheet 1 = 1 for creating the 3-d pie and two for getting a title on it.
    13 Renamed Sheet to "Income Analysis" a 1 for renaming the sheet correctly.

    By writing formulas to test the answer I get a 1 to appear in my spreadsheet if they got the answer right and a zero if they got it wrong. I can then total up their correct answer for eqach area and recommend appropriate training.

    I can then paste the results into an email and send it to them.
    As part of the assessment the user should rename sheet one to Income

    I can send a copy of my spreadsheet with macros so far if that helps.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code for Self scoring test in Excel (VB (Excel))

    Yes, I think you will have to upload a copy of your workbook for us to be much more help. You will also need more detailed explainations of what you want. For example, what do you mean by "Text Formatted?" Text is always formatted, so what are you asking. What score do you want assigned for what formatting condition? Another example, "Column Width changed." There is no way to know if the user changed the column width other than to compare the current column width to some value. You can save the column width before telling the user to change it, and then compare to the width after the user is supposed to have changed it. Is that what you want to do? If so, what score do you want to assign if the user did and did not change it?

    In addition, many of these changes do not trigger any kind of event or recalculation of a formula. For example, changing the column width does not trigger any event. Therefore something else will have to trigger the check to see if the column width has changed. You need to figure out what that event is, and we would also know where to store the score when that event happens.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Jan 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code for Self scoring test in Excel (VB (Excel))

    Hi Legare
    I will send you the file. It will show how the spreadsheet should look after the assessment is complete.
    When you click the step buttons on the sheet 1 (Income Analysis) sheet you will see the instructions I am giving the student.
    The score sheet shows a column where I want to display the results. (column 2).
    With the column width I am checking to see if they have increased the width beyond the standard width wide enough for the labels to display.
    The event that triggers the scores could be me click on a button on the scoring sheet.
    Attached Files Attached Files

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code for Self scoring test in Excel (VB (Excel))

    I glanced at your workbook, and I think I understand most of what you want. However, from the little information in the score column, it looks like you are scoring each step either 0 or 1, not 1-12 as you indicated in your first post. Is this correct?

    I have to leave for a couple of hours, and I will work on this when I return if noone else has done it before then.
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code for Self scoring test in Excel (VB (Excel))

    The attached workbook does what I think you want for the first seven steps. I put the code in the worksheet activate event routine for the score sheet so that the scores are automatically updated every time that worksheet is activated. I stopped here for a couple of reasons. First, I wanted to be sure this is what you want before spending a lot more time on this. Second, The next few steps are features that I am not extremely familiar with, so it would take me more time to do these than I have available at the moment. You can see the code for what I have done by right clicking on the sheet tab for the score sheet and selecting "View Code". If this is what you are looking for, then I hope that one of the people around here that are more familiar with auto filter and charts than I am might jump in and finish the code. If not, then I will try again the next time I have some time available.
    Attached Files Attached Files
    Legare Coleman

  8. #8
    New Lounger
    Join Date
    Jan 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code for Self scoring test in Excel (VB (Excel))

    Wow, Thanks so much for what you have done so far Lagare. This is precisely what I wanted to achieve.
    By entering a score of 1 for everything they got right I can then easily produce feedback and recommend training. Everything you have done so far is what I want except that I would like to test the two formatting tasks separately from autofill or copy of labels. I am not sure if you entered a score for row height and the last items seem to be displaying the score in the cell above, but since my knowledge of VB is extremely basic I could be wrong.

    If anyone can help me with scoring the Chart and AutoFilter task that would be great. Your work would go to a great cause since I work for a large charity whose aim is to eradicate poverty and campaign against climate change. Thanks you again for you work.

    If it makes it easier I could ask them to the chart in a chart sheet rather than embedding it.

  9. #9
    New Lounger
    Join Date
    Jan 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code for Self scoring test in Excel (VB (Excel))

    Very long piece of code moved to attachment by HansV

    Legare, Thanks for the work you did on the Excel macros for me. I have now managed to get the rest of the coding done and thought you might like to see it.

    Now I am working on a macro that I can click to send the delegate feedgack automatically.
    Attached Files Attached Files

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code for Self scoring test in Excel (VB (Excel))

    Thanks for sharing that with us. Sorry I didn't get back to you with more help. I have been extemely busy the last couple of weeks, and didn't have the time that it would take to finish what I started. Glad you solved it yourself.
    Legare Coleman

Posting Permissions

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