• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing target cell for slider bar

T

tpmbrian

New Member
Hi

I have a challenge, which is on my wish list, and not critical to my progress, but seeing as how you talented people have always risen to great heights to help me, I thought this might engage your imagination.

I have a spreadsheet which is profiling Emotional Intelligence and looking at how well an individual profile fits a generic, proven job profile.

To achieve this the user has to give a score for each of 51 questions on a scale of 0-10, and the user can either type in the number, or use the slider adjacent, however that is 51 slider bars.

What I wonder is: Can anyone devise a way by which the user scrolls to the next question, and uses the same scroll bar to score the new question. Now that would be elegant.

Any takers?
 
Hi tpmbrian,


Are you trying to use just one scroll bar to rank between 0-10 for the 'active question'?


Regards,

Faseeh
 
Hi Brian ,


How does the user go on to the next question ?


The slider helps to select a value , but moving the slider by itself will not freeze the selected value ; as and when the value is frozen , the linked cell can be changed so that the previous linked cell contains the last selected value.


If you have a submit button to submit the answer to any question , this button can run a macro , which increments the linked cell to point to the next question.


But this is not quite ideal ; any questionnaire has a provision for skipping questions , which means the user should be allowed to scroll to the question that he / she wants to answer and then move the slider to the desired value. Are you going to provide this facility ?


Essentially there are two questions :


1. How do you select a question which you wish to answer ?


2. How do you select a value answer for the question ?


For the first , you need a scrollbar with a pointer ; for the second you need a submit button.


Narayan
 
Hi Brian ,


Can you check out the following worksheet ?


https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21172


There is still a lot of work to be done , but this should give you an idea.


Narayan
 
Just a quick idea:


Have each question in an individual cell.

Have a dashboard-type interface that brings up a question.

Drag slider.

Hit button to go to next question.

Don't allow skipped questions (this is a personality test, so it should not have any need to "think about" your response)

Slider remains all the time. Value is assigned to "value" for each "active question"


Does this make sense? I'm going to bed, or I'd work on the idea a bit more concretely.


Don
 
@DonMinter

Hi!

Just as the old saying: common sense is the least common of the senses.

Sweet dreams... if you dare to go to bed without going on...

Regards!
 
Everyone, my apologies, I left the question not expecting any serious response, so I did not see your inputs until now, so here are my collective answers.

Faseeh, yes, scroll down the questions, and use the one slider to score each question as they are selected.

Narayan, 1-scroll down to the next question (currently the rows are not contiguous, ie there are spaces for ease of reading, but I could change that), 2-Thats what I use the slider bar for. I looked at the spreadsheet, but there were features blocked on it, so I could not imagine what you were trying to show me.

DonMinter, in essence I think that is where I have to go, but not sure how to.

I hope you have all had sweet dreams since my post, which is still in my thoughts.

Regards, Brian
 
Hi Narayan

Following your prompt, I went back to your link, and downloaded the file, and gave several permissions, then I was able to open up your excellent solution.

It does pretty much what I wanted it to do, thank you.

I am not sure if I can develop this to fit my profiling spreadsheet (which has had 50 downloads from LinkedIn Management Group, so I am happy). I am not fluent with VBA, or macros, and the pointer does not position itself consistently in the correct cell, and I don't see where I can change properties such as size, position or value range.

I will look at this in more detail, time permitting, but nevertheless this is an excellent solution, worthy of applying in dashboards, and many other places.

My grateful thanks to you.

Brian
 
Hi Brian ,


Thank you. I am having problems with the pointer behaving consistently when I rely only on the Workbook_Open event , which is why I gave an additional RESET button ; also , I didn't want to spend more time on this without getting any confirmation from you.


Thanks for your feedback.


One point I wanted to make is that you have mentioned in your earlier post that you have blank rows in between ( your data rows are not contiguous ) ; I suggest that you avoid this since it makes even copying formulae difficult ; it is always better to modify the row height to accommodate the contents.


Narayan
 
Thanks Narayan

I can't seen to get into the slider properties, otherwise I would have a much closer look at this solution. From my limited skill, I might be tempted to discard the pointer if I could embolden the active question when the selection slider made it active for the input.

I could make the rows contiguous, maybe colour coding the questions to sections, and even linking the final completed questionnaire to a non contiguous mask.

The work I have done so far is now published, and being requested by more people than I imagined, so it has been a good exercise, giving value to over 80 people so far.

Brian
 
Hi Brian ,


Did you select Design mode first ? I have not protected or locked anything ; once you select Design mode , you can right click on the slider and open its properties window.


I think making the currently selected question bold should not be a problem ; let me do it and upload the workbook so that you can incorporate that into yours.


Narayan


EDIT : The revised workbook is here :


https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21175
 
Narayan

Amazing, I have downloaded the revised workbook, AND found the properties. I can see I need to do a lot more work to make this a part of my toolbox. Thanks very much for your excellent help. When I find time, I will revisit your revision, and research the VB code (unfamiliar territory), and understand this.

Brian
 
You must log in or register to reply here.
Back
Top