Search code examples
excelvba

Excel macro with argument returns error "Cannot run the macro..."

I created a small macro-enabled excel worksheet, where I have several form buttons that increment a cell value. This means I wanted to create a Sub with a String argument, being the cell in mention:

Sub increment_cell(cell As String)
  Range(cell).Value = Range(cell).Value + 1
End Sub

This worked fine, until I saved the book as an .xlsm file. I forgot to save the code for the macro, and rewrote it. Unfortunately, the button click now returns an error:

Error returned when clicking button

I assign the macro with 'increment_cell "XX"', with XX being the cell in mention, e.g. "A2". This results to 'dice-randomness-calculator.xlsm'!'increment_cell "XX"' next time I open to look at assigned macros for the button

I have enabled macros in the settings:

Macro settings

I also tried creating a test-macro, without arguments, which works fine:

Sub increment()
  Range("N2").Value = Range("N2").Value + 1
End Sub

A picture of my code in Module1:

VBA code

I guess my question is, simplified, how do I create a macro that takes a string input for a button click? And more general, what am I doing wrong?

I am not used to coding in VBA, and I am slowly running out of ideas on how to debug this.

I did not want to use a spin button, as I never need to decrement, the buttons would be too small, and the risk of hitting a decrement when not needed was too big.

Solution

  • It's not possible to assign the sub with parameter(s) to a button. You can bypass it with an intermediate sub assigned to the button.

    Sub Button1_Click()
  increment_cell ("A1")
End Sub

Public Sub increment_cell(cell As String)
  Range(cell).Value = Range(cell).Value + 1
End Sub

    So, assign Button1_Click to the button. enter image description here

    On this screenshot increment_cell and increment_cell1 are not enlisted to assign them to the button. Only suitable subs can be assigned.