• 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.

Scroll bars with dynamic cell links

N

nick.mexal

New Member
Hi, first off let me say I'm a huge, HUGE fan. Truly excellent stuff here. As for my request, I'm trying to create a single range of cells in which the user can provide numeric input using scroll bars. For example, There are five cells in a row, each with a scroll bar assigned to it. The user uses the scroll bars to assign values to each cell which corresponds to a category of labor. Now, here's the rub: there are five or six "organizations" for which the user can assign labor category values. So labor category A for org 1 may have a value of 33; for org 2 it could be 40, etc. etc. You get the idea.


What I would like to do is use a drop down menu to determine which org area the user is defining labor categories for and, using the single row of input, save the user assigned values as unique so if the org area is changed in the drop down menu the last input value(s) for that area are then populated in the input row and available for update.


I'm not really sure how to attach a file otherwise I'd do that for reference. I hope this is at least semi-clear. Thanks in advance for any help!


-Nick
 
Hi Nick ,


The following link details how to upload a sample workbook to this forum :


http://chandoo.org/forums/topic/posting-a-sample-workbook


You might also check out a similar post in this forum :


http://chandoo.org/forums/topic/changing-target-cell-for-slider-bar


Narayan
 
Thanks so much for the quick reply! Sorry I didn't notice until now. Below is a link to the file (I hope it works):


https://skydrive.live.com/redir.aspx?cid=2efc3a6b8886eed1&resid=2EFC3A6B8886EED1!119&parid=2EFC3A6B8886EED1!104&authkey=!AMBTd7H44wSevUE


I saw the thread you suggested but must admit that I just couldn't figure out how to adapt it to my needs. I'm trying to get the scroll bars in row 5 to archive the input for a particular commodity (in drop down from cell Q5) and be able to pull that archive data back into row 5 (where the scroll bars are) when that commodity is selected again.


Hopefully I'm explaining myself well enough


Thanks again!
 
Hi Nick ,


I downloaded your file , but could not find either the drop-down or the scroll bars ; have you already inserted them or is it still at the idea stage ?


I defined a range name called Category , referring to =Sheet2!$C$5:$C$14 , and added the drop-down in Q5 , using this for a list.


Once the scroll bars are added in row 5 , you want to store their selections in some area of the workbook , so that when the same category is selected in the drop-down in Q5 , the corresponding selection can be retrieved. When is the data to be stored ? The user may just move the scroll-bar without intending to actually enter that value ; preferably , a STORE button should be provided for the user to click.


Narayan
 
Hi, nick.mexal!


Give a look at this file:

http://dl.dropbox.com/u/60558749/Scroll%20bars%20with%20dynamic%20cell%20links%20-%20Model%20%28for%20nick.mexal%20at%20chandoo.org%29.xlsm


I defined 3 named ranges (for easy referencing, you can see them from Formula tab), replaced the 5 form's scrolls for ActiveX ones (for naming and far more easy property assignment), added code to the Worksheet_Change event (for retrieving Min, Max and Last values for sliders) and for each slider _Change event (for saving Last selected value), created a table by Organization with the min, max and last sliders values (for holding all this stuff).


Just check if it's what you were looking for. Please advise if having troubles or if you can't fully understand certain points.


Regards!


PS: BTW, who in hell defined the background color? Not a friend, I guess... :)
 
This is amazing! VBA is not really my forte so I'm not entirely sure I do/can understand what you did. But everything seems to work perfectly so, frankly, understanding is a distant priority by comparison.


And the color scheme was picked by yours truly hahaha. I suppose I should rethink it after that comment.


There's some additional functionality I'm trying to add so I may be back if I can't integrate with what you've done here. A million thanks!
 
Hi, nick.mexal!


Glad you get it solved. And don't worry, you're be learning at the same time you're using, looking, getting your hands dirty, and so. Just ask about anything you don't understand, if you want to go deeper.


The color scheme... ehmmm... what to say? That perhaps on a video wall it looks fine? But for editing... nooop!!!


Welcome back whenever needed or wanted.


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