How to call a macro from a button and pass arguments

by

Yes, you can assign a macro to a button (or other excel controls/menu actions) and pass constant OR variable arguments to it.

In the ‘Assign Macro’ window (right-click on object and select ‘Assign Macro’):

  • Enclose the macro name in single quotes
    e.g. to pass 2 constants: 'Button1_Click("A string!", 7)'
  • Select ‘This Workbook’ for the ‘Macros in’ field
  • If you wish to pass a variable (like the value of a cell), enclose the parameter in Evaluate()

For example, to pass the value of Sheet1!$A$1 to a button function, you would have the following text in the ‘Macro name:’ field:

Button1_Click(Evaluate("Sheet1!$A$1"))

If you don’t enclose your variable argument with an ‘Evaluate’ function, excel returns the error ‘Formula is too complex to be assigned to an object.’.

I would have included an image if this were allowed on my first post.

More Related Contents:

  1. SumIf using Excel VBA
  2. How can I call a macro which contains a loop but only run one iteration of that loop?
  3. Test or check if sheet exists
  4. How can I find last row that contains data in a specific column?
  5. Copy from one workbook and paste into another
  6. How to copy Outlook mail message into excel using VBA or Macros
  7. For Loop not fully cycling in excel VBA [duplicate]
  8. Unique Random Numbers using VBA
  9. Populate unique values into a VBA array from Excel
  10. Excel VBA App stops spontaneously with message “Code execution has been halted”
  11. How to remove all non alphanumeric characters from a string except period and space in excel?
  12. Save each sheet in a workbook to separate CSV files
  13. Is VBA an OOP language, and does it support polymorphism?
  14. Export each sheet to a separate csv file
  15. What are .NumberFormat Options In Excel VBA?
  16. Detect if range is empty
  17. Checking if a worksheet-based checkbox is checked
  18. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  19. Extract Data from Word Document to an Excel SpreadSheet
  20. Excel macro -Split comma separated entries to new rows [duplicate]
  21. VBA equivalent to Excel’s mod function
  22. Pointers needed for speeding up nested loop macro in VBA
  23. ‘1004’: “The sort reference is not valid.”
  24. Select entire column in table using Excel VBA
  25. Create hyperlink to another sheet
  26. Reducing WithEvent declarations and subs with VBA and ActiveX
  27. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  28. VBA: Modify chart data range
  29. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment