Excel VBA - Multiple buttons pointing to same event

Question

I am currently working on some excel document to automate it as much as possible. For this I have to add the same button (let's call it an Erase All button for example) in each of the sheets. Till here is OK. I have added it and all works fine. The problem is that the onClick event code is replicated in all the different sheets as a new Microsoft Excel Object code. What I would like is all the buttons to point to a single Module so I dont have to replicate code anywhere.

I come from a .Net environment and I know that VBA may have some restrictions or different usage but wondering whether I can avoid the onClick event in every sheet and put this onClick on a single generic module.

As a workaround I added my functionality as a SUB in the module and I call it from each of the different onClick events but if it is possible to directly clean all the sheet-specific code then it would be great :)

Any clue on that?

Hope is enough clear but in case need some more description please let me know and will try to add some graph/pic

Thanks a lot in advance!!

Tragamor · Accepted Answer · 2017-01-09 18:42:13Z

1

You can assign the same macro to each of the buttons. (Just right click on the button and 'assign macro')

As long as you designate the ActiveWorksheet as the sheet to process within the macro, this should be fine.

Alternatively you could instead create a non-modal userform with the required generic buttons which then has code to work on the active worksheet when pressed

answered Jan 9, 2017 at 18:42

Tragamor

3,6893 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

byle.05 Over a year ago

Great! I was using the command button (ActiveX) instead of the regular button (Form Control). Now it work fine Thanks! Just as curiosity, is it possible with the ActiveX command control?

Collectives™ on Stack Overflow

Excel VBA - Multiple buttons pointing to same event

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related