Excel Scroll Bar

[sdocking]

I am trying to create a form colored scroll box into excel that lists
SCALE of 100% to 70%.
Where 100% = all green, 95%=2/3, 1/3 yellow, 90%=1/3 green, 2/3 yellow
85%= all yellow, 80%=1/3 red, 2/3 yellow, 80=1/3 red, 2/3 yellow
75%=2/3 red, 1/3 yellow, 70%=all red.

So when I click onto either the right or left arrow, and stop at sa
85%, the bar will appear all yellow.
Can anyone help me create this?

 

[Greg Wilson]

None of the existing scroll bar controls support this. Nor
can I think of any contrivance involving these controls
that would look any ways half decent due to complications
I won't go into.

To be honest, I don't think what you're trying to do can
be made to appear sufficiently elegant. I may just not
quite have your vision on this however. What are your
plans for the slider control in between the two arrow
buttons? If you intend to keep it then this will be a big
technical problem.

Ignoring the slider control, the only way I can think to
do this is to combine shapes on the worksheet and
worksheet cells. I have developed code that replicates the
3D special effects (Raised, Sunken, Etched and Bump) which
can be applied to worksheet cells. These graphical effects
are applied to the perimeter of the cells. The raised and
sunken effects can be made indistinguishable from command
buttons and text boxes if you format the cell interior
colours appropriately. Absent the slider control, you can
create a very convincing scroll bar using these graphics.

Suggested is that you:
1) Change the font of two cells to Marlett. Say these
cells are respectively B3 and J3.
2) Enter 3 in A3 and 4 in the J3 and adjust the vertical
and horizontal alignments both to centre. When formatted
Marlett, these numbers will appear as right and left
arrows respectively.
3) Apply my raised 3D effect to both cells.
4) Format the interior colour of both cells to light
grey. You can play with the workbook colour settings to
create the exact shade of grey if you like.
5) In between these cells (C3:I3) apply my sunken 3D
effect making these cells appear like a text box.
6) Now place two rectangles from the Drawing tool bar over
the range C3:I3 but don't obscure the sunken 3D graphics
at the perimeter of these cells.
7) Group the above four shapes. (When grouped, if you move
them they will move as a unit so their relative positions
won't get screwed-up).
8) Assign a macro to the two raised graphics applied to
the cells in A3 and J3 (the arrow buttons). This macro
will be designed to increment and decrement an integer
value stored on a xlVeryHidden worksheet. The macro will
also manipulate both the fill colours and widths of the
two rectangles keying off of the stored integer value.
Variance in the degree of overlap will achieve the two-
tone colour scheme you desire.

As for the scroll bar slider control in between the two
arrows, I havn't got any ideas at this point.

Regards,
Greg

 

[Greg Wilson]

Corrections to my post:

In the part where I describe how to build the scroll bar,
in points 2 and 8, references to Cell A3 should instead be
B3.

Regards,
Greg