Percentage Value in Scroll Bar

[jayman7]

Hi,
I've been trying to set the scroll bar min/max/small change to a decimal (percentage value) within the properties, obviously this does not work.

Since users can enter a value in the link cell for the scroll bar, how can I modify the current min/max/small change via macros so the scroll bar will show a min on 1%, small change of 0.25% and max of 25%?

Thanks in advance,
jay

[Andy Pope]

You have to use another cell.

Min Value: 100
Max Value: 2500
Increment: 25
Large: 100

Linked B1

In A1 use the formula
=B1/(10000)

[jayman7]

Andy,
Yes this works. Thanks. However, this limits the ability for the user to manually enter a numberical value in the link cells. (A user requirement).

Is there a way where I can achieve the same result and when the user uses the link cells to enter a % value, the scroll bar will change accordingly, as if we have 0 -100 range instead of %.
Thanks,
j

[Andy Pope]

It will require VBA.

Worksheet event

Please Login or Register  to view this content.

Standard code module with OnAction of scrollbar assigned to this macro

Please Login or Register  to view this content.

[jayman7]

Andy,
WOW...impressive! Thanks! This works fine.
Thanks a bunch!
j

[jayman7]

Andy,
I replicated your code above in 2007 but it seems it's not picking the Range ("A1") in the module. That is , when you enter a value, the scroll will not control the entered value like you had in you attached file. Any suggestions?
Thanks in advance.
j

[Andy Pope]

I just tried my example file in xl2007. It works the same as in xl2003.

Are you sure you replicated it exactly?

Worksheet_Change code in the actual sheet object.
And AdjustScroller in a standard code module.

[jayman7]

Yes. I will try again.
Many thanks again!
j

[jayman7]

I am using the Scroll Bar- Active X. I see that in your example you'd use the Scroll in Forms, but this should not matter right?
thanks,
j

[jayman7]

Hi,
Can someone please advise and assist? I am trying to perform this using the scroll bar Active-X Control. This is not working, I am not sure where I call the Sub AdjustScroller() Code below. in VB scripts since I am not calling the macro?

Thanks in Advance,
j

Please Login or Register  to view this content.

[Andy Pope]

The activex controls have events rather than assignment of a macro.

If you go into design mode and select the scroller you can right-click it and pick View code. This should take you to the Change event.

Place the code that is currently in Adjust_Scroller in to this event.

[jayman7]

Ok. I will try this. Thanks again for your insights!
j

[jayman7]

I tried this but the scroll bar does not respond. See code and attached file. Please advise if input is correct? Thanks again!

Please Login or Register  to view this content.

[Andy Pope]

You still need to link the scrollbar to B1 via the LinkedCell property.

[jayman7]

Yes, now it works great. Thanks!

BTW, can you advise whom would best suggest on my other post below or if they are in the correct area?
Thanks!
j


http://www.excelforum.com/excel-prog...ml#post2153868

http://www.excelforum.com/excel-prog...ml#post2159989

[Kovalyonok]

6ffefcb3-b5e6-4fe4-b62e-b6a59f398d00.gif
a3232375-07b6-4808-a79f-1622655cafdc.gif

Any ideas how to customize the widget so it looks like this? I've used the description from the microsoft's website, but mine looks simple, without any additional thingies like shown on the images. Can you help?