Scrollbar Name

I need VBA to return the name of a Scrollbar after it is clicked (i.e. - in the scrollbar change event). How do I do that?

Yes - after the scrollbar is changed, I would like VBA to pass the name of the Scrollbar into a different routine. I have a worksheet with several Slider Bars (aka Scrollbars) on it and I would like to assign the same Macro to each one, with the Name unique for each one.

Originally Posted by 6StringJazzer

This is a little complicated, if I understand correctly what you want to do. The strategy is to write a class for the scrollbars so a single Sub can handle a Change event no matter which scrollbar caused it. I have attached an example of how to do this with checkboxes that was adapted from a solution posted by member Andy Pope some time back.

But before I go into that explanation you still need to give more information about your overall problem. "I would like to assign the same Macro to each one, with the Name unique for each one." I can't make sense out of the second half of this. Please describe what you want to happen when the scrollbars are changed.

So the Scrollbars represent years, from 2018 out to 2030. The user selects which year a program will begin. I am hoping I can rename each Scrollbar to match the Program Name, because I need to reference that in a separate routine.

Basically, I need to set the name off the scrollbar to a variable within the Scrollbar Change Event and then pass that to a different routine.

In the file I attached, in Class CChk, the Sub MyChk_Click will handle the Click event from any checkbox. It then calls a Sub SingleCheck in whatever module owns the checkbox and passes in the checkbox name. If you take the file I attached and change all occurrences of CheckBox to ScrollBar it may work, I haven't tried it to confirm.

Note that Sub UserForm1.UserForm_Initialize sets up the collection of checkboxes to begin with, necessary for this scheme to work. It's easy in a userform because you can use the Initialize method. If your controls are in a worksheet, we would have to move this to the ThisWorkbook.Workbook_Open sub.

If you need additional help, attach your file.

Originally Posted by 6StringJazzer

In the file I attached, in Class CChk, the Sub MyChk_Click will handle the Click event from any checkbox. It then calls a Sub SingleCheck in whatever module owns the checkbox and passes in the checkbox name. If you take the file I attached and change all occurrences of CheckBox to ScrollBar it may work, I haven't tried it to confirm.

Note that Sub UserForm1.UserForm_Initialize sets up the collection of checkboxes to begin with, necessary for this scheme to work. It's easy in a userform because you can use the Initialize method. If your controls are in a worksheet, we would have to move this to the ThisWorkbook.Workbook_Open sub.

If you need additional help, attach your file.

Great! I will give that a try aveand get back to you...thanks so much for the help