0

I try to make dynamic buttons which call a function. This works:

btn.OnAction = "'DieseArbeitsmappe.Update_DB'"

But when I add arguments, it breaks (1st argument is a string, 2nd is the button object):

btn.OnAction = "'DieseArbeitsmappe.Update_DB " & Chr(34) & t.Text & Chr(34) & ", " & btn & "'"

It throws:

Object doesn't support this property or method

What have I missed?

PS: Also this variant didn't work:

btn.OnAction = "'DieseArbeitsmappe.Update_DB """ & t.Text & """," & btn & " '"

Same error as above.

Chrismas007
  • 6,085
  • 4
  • 24
  • 47
SoBiT
  • 408
  • 5
  • 18
  • 1
    http://www.mrexcel.com/forum/excel-questions/53220-calling-fuction-passing-arguments-onaction.html http://stackoverflow.com/questions/18283786/dynamicaly-call-a-macro-passing-parameters-from-shape-onaction-property http://stackoverflow.com/questions/1410115/passing-arguments-to-method-registered-with-onaction-eventvba-excel http://stackoverflow.com/questions/13079727/excel-vba-how-to-pass-multiple-variables-to-onaction – Daniel Dušek Jan 12 '15 at 15:38
  • I've seen all of these links. The last one even brought me to the code above. But still it doesn't work. – SoBiT Jan 12 '15 at 15:43
  • What do the strings `t.Text` and `btn` look like? – Chrismas007 Jan 12 '15 at 15:46
  • `t.Text` holds an SQL query like "SELECT * FROM myDB" and `btn` is not a string, its the button object which is also use for `btn.OnAction` – SoBiT Jan 12 '15 at 15:49
  • You're trying to concatenate an `Object` (`btn` is an object) into a string. This can't be done, and will raise the 438 error you're describing. Might I ask why you are passing the button to its own `OnAction` procedure? Is this really necessary? – David Zemens Jan 12 '15 at 16:02
  • I'd like to call `btn.Delete` at the end of the procedure, so I think it is necessary. – SoBiT Jan 12 '15 at 16:06
  • 1
    Can't you pass the `btn.Name` and do `Shapes(btn).delete`? – David Zemens Jan 12 '15 at 16:12
  • 1
    In the called code, `Application.Caller` will give you the name of the button. You can use that to delete it. – Tim Williams Jan 12 '15 at 16:58

1 Answers1

0

Remove the Object of btn from your string as you CANNOT pass an Object into a string. If you want to perform an operation or method on the Object btn then do that in a separate line of code within the Sub.

btn.OnAction = "'DieseArbeitsmappe.Update_DB " & Chr(34) & t.Text & Chr(34) & "'"
btn.Delete 'or Shapes(btn).Delete
Chrismas007
  • 6,085
  • 4
  • 24
  • 47