I made a quiz in excel for work and I wanted to add a progress bar for each tab so users could see how far they have progressed....I've tried a couple ways and couldn't get it to work, and I can't find much on google to make one....Any one have any idea's? I could really use your brain
Depends on how you measure progress, and how you want it displayed. Here is one that I built, which displays in a UserForm. You would have to have code that updates it every time the user makes more progress.
Another approach would be some sort of non-VBA progress bar, but I would have to see your file to see how it works.
Jazzer tyvm with the quick response. I'm sorry I didn't add my spreadsheet, I should have. I will give your's a look over, here is what I made. It's a 90 question test, with 5 Q on each page. Each page gets hidden as you progress, so just unhide it all
The easiest thing to do is to use a cell (or a series of cells in a row that are merged) that you format with red fill, and use Conditional Formatting Data Bars, set for green fill, with a Minimum of 0 and a Maximum of 100. You can use a custom number format of
0.0"% Done"
and use a formula that returns the number percentage (=#Done/#ToDo * 100) or use VBA to put that value into the cell.
Bernie Deitrick
Excel MVP 2000-2010
You really only need two macros for navigation, as long as your sheets are in the correct order, and you can create a progress bar sheet that you keep in a separate window: see the attached. I deleted all the other macros that you don't need, and have assigned the GoOn and GoBack to your Textbox buttons - I think I got them all....
Please Login or Register to view this content.
Last edited by Bernie Deitrick; 04-04-2016 at 05:05 PM.
"I wanted to add a progress bar for each tab"
I assume you want a progress bar on each sheet that shows the overall progress. Please see attached. This may not be what you want esthetically but it's straightforward, requires no VBA. Let me know what you think. This is what Bernie suggested in Post 4, but in every sheet.
I have one other Question. I want to make a page at the end of the quiz, that goes over their answers they got wrong, but I don't know how to do this. I've tried a couple things, but when I change an answer it doesn't change on the sheet. I would like it to say something about going over and reviewing what they got wrong. List all the questions they answered incorrectly and also show the correct answer as well as what they answered. Is this possible?
Of course - the simplest thing to do would be to put the correct answers in a hidden column to the right on each sheet, in the same row as the entered answer. Then you can loop through the correct answers and compare them to the user's answers, and prompt for a review. Does that sound like something you would like to do? The code would be along the lines of this, with the correct answers entered into column Z (Z19, Z31, etc.) of each sheet.
Please Login or Register to view this content.
Yes something like this could work, as long as they don't see their answers til the end, and if they go back to change an answer, it will update. Is it possible to set it up so if they put in say A on question 1, it locks their answers once they move to the next page? I am a proctor, and can give serv safe tests to people, so I'm trying to make this pre test easy for them but have it look nice
Format all the cells on all the sheets as locked (on the protection tab of the format dialog), but don't protect the sheets. Then on your last sheet, add a "Click here to finish button" with this macro assigned:
Please Login or Register to view this content.
Yea that worked perfect! Thank you for that! For the answer sheet though. If I put all the correct answers on each page and hide that tab, then add that code, it will generate all the answers onto a sheet?
You could add an answer sheet that summarizes the answers like this:
Please Login or Register to view this content.
and what does that coding do? lol im sorry im still learning on my own xD
It adds a summary sheet as the last sheet, with all the questions, entered answers, and correct answers. It could be changed to include the wording of the correct answer and the incorrect answer if the question was missed.
I put that in, and it doesn't get all the questions, missing 1-5. and if they go back and change a question it doesnt autopopulate to fix on the end sheet. sorry if im making this too complicated..
Change
toPlease Login or Register to view this content.
I had assumed that you kept my sheet with the progress bar, which made Questions 1-5 the fourth sheet and not the third.Please Login or Register to view this content.
And it will not re-populate if they change answers, so you should only create the summary sheet after they agree that they are done and their answers are locked. Or you can delete the sheet and re-run the code (the code can also overwrite existing values if the sheet already exists).
Last edited by Bernie Deitrick; 04-07-2016 at 01:00 PM.
I did keep your idea but Jeff put it on each page for me, my DM would rather it be on every page. And okay I will change that, ty for your help with this, you've been great Bernie
The "Quiz" sheet that was added does a lot of the other functionality already... so my code may not be needed if you are using that version.
Now my last question I hope is. 1) why does it generate the questions but doesn't start them until row 8, instead of starting on row 3 or 4. And 2) how do you make it so I can change the column and background color and all that? I want the page to look like the rest when it generates.
This one will copy the format of the source cells:
Please Login or Register to view this content.
Alright ty so much Bernie that's all the questions I have :D I think I have enough to complete what I need.
I actually do have one more question. With the tab "Quiz" how can i get it to show if it is indeed wrong, here is the correct answer, so they can review
Here is the sheet that's been getting the updates. I went thru and put all the correct answers on every sheet like you suggested. All in row O 19/31/43/55/67. I also added a column on the answer sheet for if it is incorrect it will give them the correct answer. Can you help me with the rest? XD
Last edited by DDayWalker182; 04-07-2016 at 07:40 PM.
I also don't know how to fix it so when it's incorrect it displays the right answer, it just says #Name?
Nevermind I fixed my own issue xD
How do you force certain cells to be auto cap? if they go thru the quiz and type "a" at the answer sheet it shows a but I want it to be A...eventually it will start looking sloppy if some are cap and some are lower. Any suggestions? Is there a way to make cells B19/31/43/55/67 auto capped on each sheet?
I'm sorry if it seems like I'm spamming, I post something, then I figure it out, and I can't delete the post lol. The issue I'm having now and you can see on the sheet is, I'm trying to go from page Name to Q1-5. Which works fine. BUT I want the cells B19/31etc etc to be unlocked. AFTER they go thru and hit next page I want those cells to go back being a locked cell so when your at the end and you hit submit, everything gets locked. The problem I noticed is when you lock everything at the end, If the cell is unlocked it wont lock it. The reason for this is, when the user is on the test they cant accidentally type over a question, everything is protected. But it wont let me do that....I kind of got the idea I wanted down, but everytime I type in the answer on Q1-5 page and hit go to next, they get erased..... And im sorry, the macro's screen is a wreck, the one's I really been messing around with are GoToQ1-5 and 6-10
Hi DDay,
I am not a fan of the whole Worksheet Hide and Show Design but i guess for a beginner its easier to Code it this way.
Anyway i Like the Quiz Idea in Excel i will take your quiz and see if i can put it in a nice userform based Quiz that gives you the Functionallity that you need.
So if you not totally against another Approach keep an eye on the Thread i will post my worksheet later today at least tomorrow. Depending on how complicated it gets :D
Greets Loki
Cause i am a little bit exited, here the nearly finished version if i did not miss something it should already work as intented
i just want to make it more dynamic and a tidy it up a bit :D
Test it and have fun if you like it
hey can i use this - how do i change the questions and answers
Sure feel free to use it.
But at the moment i am not finished with making it completely Dynamic means it goes through exactly 92 questions in certain places wich messes it up when you have less or more questions.
I think after the weekend i will have finished the thing so far that you can add questions dynamically i will then also provide a little guide how to use it.
Basically it is very simple it has 2 hidden sheets (vba hidden so you have to unhide them via code)
On The Questions Sheet you provide the questions
on the Answer sheet the answer options and specify wich is the correct answer the rest should then work by itself
Greets
Loki
Loki my man I love what you have done with it so far, one thing, I finished what I had. I really like the buttons in the front for a table of contents and a progress bar at the beginning in case they missed anything. Ofc the beginning one isnt needed with yours since it looks amazing but my only concern. if something happens and they close out. they have to start all over again, and can't skip to say Q20 if they did them all. anyway to make it so it can? It seems like you have it all protected which is nice, looks amazing. I have another project that i'm working on that's extremely complicated for me, and maybe if you get freee time and don't mind helping me, that would be amazing
One other thing I could like to see if possible. The click here to start button should go to the Name page. and the name page should be hidden at first until u click it. After you put your name in and start that sheet should go away. Lastly, if the quiz is going to be a free floating quiz with whichever tab open in the background, can we add a blank sheet with just a burger king or for anyone else who uses this a certain background? I work for BK and would like to have the logo shown or something while they take the quiz if possible?
Also when answering all the questions and you get to the end. I answered all 90 and it's saying i only answered 88 but when u look at the answer page it shows all 90 answered
how do i change the code to unhide the question and answersOriginally Posted by LordLoki
Last edited by dougers1; 04-12-2016 at 03:52 PM.
can this be changed so it is only 20 Q & A Please
Hi Guys,
I am happy to see that you like the result so far :D
As i said before i am not finished with the whole thing yet. I liked the idea of an Excel quiz and will use it for my team too so it will be more dynamic when i am finished.
I know about the bug with the number of answered Questions not showing correctly but i have a big project at work that i need to finish until end of the week and i am not 100% on track with that right now :D
I will come back to this when i have catched up with my backlog or somewhen next week.
If you have any more suggestions feel free to post them here i will see what i can put in then.
For the things you mentioned already let me say this.
I think i will add the name part into the userform as well so there will be no sheet with the Name anymore.
I will also make it so that you can directly go to a specific question maybe even add a failback procedure so that it starts right were you left if you accidentally close the window.
Greets Loki
Edit: Find attached my latest version which should have the fix for the wrong answer Count already and i added a button to change unhide all the sheets for questions and Answers.
It should also adapt to the number of Questions so if you only put in 20 Questions it should work. As said before its not 100% testet so bugs might be in there Password is "Quizmaster85" can be changed in Module1
Also i suggest putting a password to the VBA Code in case someone knows about vba they could easy look at the code and unhide the answers :D
Greets Loki
Last edited by LordLoki; 04-13-2016 at 06:08 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks