Using same subroutine for multiple Command Buttons

I have a rather large spreadsheet that uses several buttons to aid in navigation for the end user.

Each button calls a subroutine which executes code like this:

Please Login or Register  to view this content.

There are about 20 identical subroutines with the exception of the named ranges used to scroll down to various points in the spreadsheeet quickly, and they all work as intended.

I was curious if there was a method to combine the subroutines into 1 which would navigate to a named range the matches the button caption? (I Wish the objects had a "Tag" property to use for situations like this)

I know that in VB.Net you can use a directcast to accomplish this type of scenario similar to the following (non functional code in VBA)

Please Login or Register  to view this content.

In .NET, this basically "passes" the button to the subroutine and creates a new one on the fly that has the same properties as the button that was clicked. The caption/tag for the newly created button can now be used in the code for the navigation.

There is no real pressing need other than to satisfy my curiosity and the possiblity of cleaning up my code a little bit.

I have read through my books and googled a little and I have come up empty. I know there is no direct equivalent to the directcast command, but I was wondering if there is some sort of work-around

Thanks again for reading


Lee

Hello Lee,

Yes you can create a reusable Sub procedure to select a given Range.This code assumes you are using the Forms Control button on the worksheet. Add as many button names as you need to the Case statement. The range is assumed to be a named range which is the same as the button caption.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

That is exactly what I was looking for

I appreciate the help