Userform - run same code on any button click

[liampog]

Hi

I have a number of Command Buttons with different captions.

When the user clicks a command button, I want the code to change the activecell value to the caption of the button (the userform is called by double-clicking a single cell).

I don't want to create code for each command button but not sure if this is possible.

Thanks
Liam

 

[Kaper]

If the buttons are on the sheet (not on userform) you can assign the same macro to all the buttons:

Sub Button_Click()
ActiveCell.Value = ActiveSheet.Buttons(Application.Caller).Name
End Sub

If you want to save manual work and add this macro to all buttons in a sheet you may use such code to do it:

Sub Assign_macro_to_all_Btns()
Dim btn As Object
For Each btn In ActiveSheet.Buttons
  btn.OnAction = "Button_Click"
Next btn
End Sub

Of course you may use some filtering in the above procedure to add macro only to soeme of them

 

[liampog]

Thanks Kaper

The buttons are on the userform unfortunately (and need to be in order for them not to be visible all the time).

Basically, when the user double-clicks on a cell, the userform appears.

When they click any button on the userform, the caption for that button is input into the ActiveCell and then the Userform unloads itself (to disappear).

There are 57 buttons on the userform so I'm looking for the simplest way rather than having to add code to each button's "Click"

 

[RoryA]

You need to use a class. Searching this forum should provide several examples.

 

[liampog]

Can you give me an example search term? haha

 

[RoryA]

Userform class

Here is one example: Same code for Multiple optionbuttons

It's for option buttons but the principles are exactly the same.

 

[Kaper]

Or second result for such a bit longer search term:
same code for several commandbuttons on userform

https://stackoverflow.com/questions/69612204/unique-code-for-several-commandbuttons-in-userform

In my opinion this version almost perfectly fits your situation . The only change will be the output to active cell, not to always the same E5:

Private Sub CmdGroup_Click()
ActiveCell.Value = CmdGroup.Caption
End Sub

If you are not familiar with classes: note class module name, which has to be changed from standard one (Class1) - the rest shall be working smoothly

 

[liampog]

Thanks all for your help. Have researched classes in userforms and have been able to figure it out so it works