Event on form buttons?

[cactus13]

Is it possible to do something like events like in userforms when pressing a commandbutton? It would save me a lot of extra (double) coding.

I would like to use a tag from the button to specify the action taken, as I have a range of buttons whit very similar code I would like to reduce it by using the event property if possible?

Doe ssomeone know if, and if so than how, this is possible in a button on a sheet(not a userform)?

Thanks

 

[msmith]

I am assuming you are working in Excel.

Create your command buttons using the Control Toolbox toolbar NOT the Forms toolbar. Once added to the workhseet, right-click and choose View Code. This will open the code editor and insert a shell click event handler, something like CommandButton1_Click. Inside this event handler, call a separate procedure, which will be located in a standard code module. This procedure will take a single parameter that will serve to id the particular button calling it. Here is an example of the procedure:

Sub MyProcedure( BtnID as Integer )

  Select Case BtnID
  Case 1
    ' Do stuff relevant to Button 1
  Case 2
    ' Do stuff relevant to Button 2
'Etc...
  End Select

' Other code as needed

End Sub

Example of button 1 event handler code:

Private Sub CommandButton1_Click( )
  MyProcedure 1
End Sub

Repeat for each additional button, incrementing the number supplied to MyProcedure.

Hope this helps.

Mike