Event on form buttons?
Event on form buttons?
(OP)
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
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





RE: Event on form buttons?
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