Forum Discussion
Controlling a Shape (arrow) based on value
Is it possible to change a shape based on value? I would like to have a sliding arrow that moves to match the students current level.
As you can see in the photo, if the student is level 1 then the sliding arrow will be at the beginning but if the students current level is 2 then it would slide to the mid point and finally to the goal when the current level is 3. Elsewhere in the sheet will have the students current level from which to draw the value. No VBA unfortunately.
2 Replies
I think you would need a macro to do that. You could adjust the progress arrow's width by taking the Level shapes left value and subtracting the progress arrows left value. But, you would need to figure out how you want to activate the macro, whether it is attached to a button that you click to update, or if you have the macro run when you input a value into a particular cell, or when the worksheet calculates, etc.
This appears to work (but, it's incomplete as it would still need some logic to decide which level the arrow needs to be adjusted to):
Sub Macro1() With Worksheets("Sheet1") .Shapes("ProgressArrow").Width = .Shapes("Level1").Left - .Shapes("ProgressArrow").Left End With End Sub- Thank you for your advice. I eventually went with using a variety of arrow lengths and then using an INDIRECT lookup to the appropriate arrow length.
https://www.xelplus.com/excel-lookup-on-pictures/
