CODEWITHSUNDEEP
excelonclickvba
byle.05
byle.05

Reputation: 196

Excel VBA - Multiple buttons pointing to same event

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!!

Upvotes: 0

Views: 1487

Answers (1)

Tragamor
Tragamor

Reputation: 3634

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

Upvotes: 1

Related Questions