Passing a sheet name to a macro on a command button click.

[Dragondude]

I have 30 buttons on a sheet. At the moment when I click on each button it calls the related macro that loads data from its related sheet to charts on the sheet with the buttoms.. Each macro is identical except for the embedded sheet name. I would like to reduce the 30 macros down to 1.

Is it possible to have a "Case" function that detects which button has been clicked and pass the sheet name to the single macro?

[teylyn]

Hello,

each button will have one sub that fires when the button is clicked. The only command in that sub is to call the main routine, passing the sheet name as a text parameter.

Here are some ways to do that.

Please Login or Register  to view this content.

[Dragondude]

Thanks for the reply.

I am not sure that will work. I am already calling macros for the sheets I need. What I really want to do is have a variable name in a single macro that will change depending on the button click. This would save having 30 macros.

For example the small bit of code below is called from button 1 and the bold text DG # 1 is the sheet name for that macro. If I click on button 2 I want to change it to DG # 2 and so on. This reference appears 7 times in the macro.

Hope that makes sense.

Please Login or Register  to view this content.

[mgs73]

You can create a global variable (it must be in a code module, not a workbook or worksheet module) e.g.

Please Login or Register  to view this content.

and then for each button code in whatever worksheet you need

Please Login or Register  to view this content.

Also, looking at your code snippet

Please Login or Register  to view this content.

you should be aware that in VBA, each variable must be declared individually. Your line of code declares c as a long integer, but the remaining ones will be variants.

[Dragondude]

So how does this achieve what I am trying to do?

Also I was told that the Dim as I have it declares them all to be long as shown below.

Please Login or Register  to view this content.

[teylyn]

So you have 30 buttons, each calling the same macro?

[Dragondude]

No. I have 30 buttons calling 30 macros. The only thing different in each macro is the sheet name for the data as I stated in post # 3.

I want something that can detect the button that was clicked and apply the relevent sheet name to a variable that would replace the DG # 1.

[Dragondude]

I thought it might be an idea to give you the whole of the main macro. There is also a module full of the button clicks.

[mgs73]

It is possible to do this using shapes instead of buttons. I don't have time to show you how to do that today, but I should be able to do it tomorrow.

As for my other point , try this:

Please Login or Register  to view this content.

It will not run, as you cannot assign a string or floating point number to a long variable. Then try this:

Please Login or Register  to view this content.

It will run just fine, as a and b are Variants.

[Dragondude]

Its done. Not how I expected it to be but even simpler. Sometimes I amaze myself having virtually no programming experience I stumble on to the simplest answers.

Thank you to all that helped.