Userform Controls (SpinButtons)

[Gixxer Lad]

Good evening, I have a little problem to which I need some assistance if possible please.

I have a userform with 20 spinbuttons on it and would like the same piece of code to run when I click on any of the SpinUps.
Is this possible without putting the same code it 20 times?

Any help is greatly appreciated.
Best regards

 

[Micron]

Two methods I know of:
1)
- put the main code in a sub/function in a standard module. That makes it visible to the entire project (as long as you don't start the sub with Private keyword)
- each control needs a short sub that calls the main code. If the called code needs to act according to which control called it, the main code needs a parameter for that and the calling code needs to pass it.

2) if you research you may find code that creates a custom class that will do all that but I don't have any. TBH, it's one of those things I wished I had learned before, but now I have no practical need to do so. Someone may chime in here and give you that or direct you to where you can download code.

As an aside, spin buttons can be cool but also annoying. Much quicker to enter 150 in a textbox rather than sit there with your finger on a mouse button to spin from 1 to 150 - and maybe pass it and have to back up. Not likely that I'd ever use them again because nobody really complains about having to type in a 3 digit number but they tend to complain about spinners.

 

[6StringJazzer]

Yes, it is possible.

You have to create a class for the group of buttons. This code is just for a demo for a command button. You have to replace the code here with what you want to happen when the button is clicked. I'll see if I can whip up an example for spin buttons.

handle multiple buttons by creating a class.xlsm

Shared with Dropbox

www.dropbox.com

For the class:

Option Explicit

Public WithEvents ButtonGroup As CommandButton

Private Sub ButtonGroup_Click()
    Dim Msg As String
   
    Msg = "You clicked " & ButtonGroup.Name & vbCrLf & vbCrLf
    Msg = Msg & "Caption: " & ButtonGroup.Caption & vbCrLf
    Msg = Msg & "Left Position: " & ButtonGroup.Left & vbCrLf
    Msg = Msg & "Top Position: " & ButtonGroup.Top
    MsgBox Msg, vbInformation, ButtonGroup.Name
End Sub

In the form:

Private Sub UserForm_Initialize()
    Dim ButtonCount As Integer
    Dim ctl As Control
    
'   Create the Button objects
    ButtonCount = 0
    For Each ctl In UserForm1.Controls
        If TypeName(ctl) = "CommandButton" Then
            If ctl.Name <> "OKButton" Then 'Skip the OKButton
                ButtonCount = ButtonCount + 1
                ReDim Preserve Buttons(1 To ButtonCount)
                Set Buttons(ButtonCount).ButtonGroup = ctl
            End If
        End If
    Next ctl
End Sub

 

[Gixxer Lad]

Thank you both for your assistance and comments, I will see what I am able to achieve.
Best regards

 

[6StringJazzer]

Here is a fully functional demo for both command buttons and spin buttons. You will have to download a copy to run the VBA.

handle multiple buttons by creating a class.xlsm

Sheet1

docs.google.com