VBA to set form scroll bar control minimum using another scroll bar

[RickOlson]

I'm working on a project that uses scroll bars on a worksheet named "Interface" to change the values of cells on another worksheet named "Calculations". Because the final workbook will be used on PCs and Macs I'm using form controls instead of ActiveX.

One thing users set is the projected total % of homes renovated by the end of 2015 and by 2020. In the abstract, the range for either year is between 0% and 50%. But I need to make certain that the % selected for 2020 is at least as large as the % selected for 2015.

My intention was to change the min for the 2020 scroll bar whenever the 2015 scrollbar is changed. Is this possible? As far as I can tell there aren't any events for form controls. I tried creating a sub named Testit in a module and making that the macro for the 2015 scroll bar, but when I set a break point in the Testit, moving the 2015 scroll doesn't seem to trigger Testit. I also tried putting Testit into the code for the worksheet that has the scroll bars, but that didn't do anything.

Even if it did I'm not sure how I'd change the min of the 2020 scrollbar with vba. Would it be with something like:
ActiveSheet.Shapes(Scroll2020).Min =ActiveSheet.Shapes(Scroll2015).Value

Thanks for any light you can shed on this--

 

[bensonsearch]

HI,
Been ages since i used scroll bars but there is a change event for scroll bars.

double click the bar in vba and it should defult to the change event.

 

[RickOlson]

Thanks for finding my question so many pages, into the forum and bumping it. It looks like the double-click trick works with ActiveX controls, but not the Forms control that I need.

 

[bensonsearch]

is this on the sheet or a user form?

ill think of some more ways in the meantime

 

[bensonsearch]

if on a sheet, i just inserted a form control, went to the developer tab and clicked view code. comes up with the change event

 

[RickOlson]

if on a sheet, i just inserted a form control, went to the developer tab and clicked view code. comes up with the change event

<Hanging my head sheepishly> I completely overlooked that View Code button. It's late now. I'll look at it tomorrow. Thanks for the hint.

 

[bensonsearch]

no worries, let me know how it goes

 

[RoryA]

That is not really a change event, it's just a macro called controlname_change (which is why it's in a normal module). However, an assigned macro should be triggered by a change in the scrollbar.