VBA Many buttons point to the same _Click sub

by

If you are OK to use Form Controls rather that ActiveX, as it looks as though you may be at the moment, then Chris’ solution seems good.

However if you need ActiveX CommandButtons then you are unable (as the VBA compiler will tell you, “Procedure declaration does not match…”) to have parameters in the callback for the click event, and you are unable to raise the event from multiple objects, although you do of course know which button raised the event (since the relationship is 1 CommandButton = 1 Sub).

So… I would go with something like:

Private Sub Value1Cmd_Click()
    Call TheMethod(Value1Cmd)
End Sub    

Private Sub Value2Cmd_Click()
    Call TheMethod(Value2Cmd)
End Sub


Private Sub TheRealMethod(sender As CommandButton)
    ' Do your thing '
    Dim tb As TextBox
    Set tb = GetTBByName(s.Name)
    PutValueToDatabase(s.Name,tb.Text)
    ' Etcetera... '
End Sub

Requires a stub for each button, so some copying and pasting to begin with, but then easy to maintain etcetera as all _Click event callbacks are pointing at the same method…

Edit:
E.g.

Sub AutoWriteTheStubs()
    Dim theStubs As String
    Dim i As Long
    For i = 1 To 10
        theStubs = theStubs & "Private Sub Value" & CStr(i) & "Cmd_Click()" & vbCrLf _
                   & "    Call TheMethod(Value" & CStr(i) & "Cmd)" & vbCrLf _
                   & "End Sub" & vbCrLf & vbCrLf
    Next i
    Debug.Print theStubs
End Sub

More Related Contents:

  1. Save excel file multiple times with file names in another workbook.
  2. Sequence of numbers based on start and end value of 2 cells [closed]
  3. Function to convert column number to letter?
  4. Read/Parse text file line by line in VBA
  5. Better way to find last used row
  6. Excel VBA: Answer gets “stuck”
  7. 2 Dimensional array from range [closed]
  8. filter out multiple criteria using excel vba
  9. Excel VBA autofilter all but three
  10. Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”
  11. Using Google Chrome in Selenium VBA (Installation Steps)
  12. Dir() function not working in Mac Excel 2011 VBA
  13. Excel VBA code to copy a specific string to clipboard
  14. How to use workbook.saveas with automatic Overwrite
  15. How do you run a .exe with parameters using vba’s shell()?
  16. Excel VBA – Range.Copy transpose paste
  17. How to use FindWindow to find a visible or invisible window with a partial name in VBA
  18. How do you get a Range to return its Name?
  19. Passing variable from Form to Module in VBA
  20. Excel VBA: Convert a date string to a Unix timestamp
  21. If string contains word from list, return value adjacent to list value
  22. Refreshing all the pivot tables in my excel workbook with a macro
  23. Countif With Multiple OR Criteria
  24. Vlookup to copy color of a cell
  25. excel vba freeze pane without select
  26. How to delete rows in Excel based on criteria using VBA?
  27. Converting a string representation of a constant into a constant?
  28. Macro to export MS Word tables to Excel sheets
  29. VBA WinHTTP to download file from password proteced https website
  30. How to protect Excel workbook using VBA?

Leave a Comment