0

(SW versions: Excel 2010 - Microsoft Date and Time Picker Control 6.0 (SP4))

In the sheet "sheet1" I have an ActiveX Date Picker control calendar named "foobarCalendar". Next to it I put a button and in its "Assign Macro..." I want to specify a macro with as argument the date of this calendar "foobarCalendar".

In VBA I can get this date with "sheet1.foobarCalendar", however I'm unable to find any working way to put this as argument in the "Assign Macro..." window.

Is there a working syntax or it's simply not possible?

(another problem I encounter is that my macro name doesn't appear on the list, but I discovered that it's because Excel only display the macroes that don't get arguments in this list, although I can manually write them and use them successfully)

I tried for example 'macroName sheet1.foobarCalendar' but it's not working. I also tried many other syntax, including those that work for passing named ranges and including the "evaluate" variants, without success. Is it possible?

6diegodiego9
  • 503
  • 3
  • 14
  • 1
    AFAIK, you can't pass arguments from there, you will need to get the value from inside the macro itself. – Vincent G Aug 26 '19 at 14:02
  • 1
    A macro assigned to a control (CommandButton, in this case) can't have a parameter. But you have two options: 1) hard-code the handling of your date picker control into your macro so it doesn't need a parameter, or 2) in the macro, [determine which button was clicked](https://stackoverflow.com/a/3858067/4717755) and act accordinginly. – PeterT Aug 26 '19 at 14:02

1 Answers1

1

In VBA I can get this date with "sheet1.foobarCalendar", however I'm unable to find any working way to put this as argument in the "Assign Macro..." window.

You don't. Sheet1.foobarCalendar is already in scope: have the macro use it.

Public Sub MyMacro()
    Dim workingDate As Date
    workingDate = Sheet1.foobarCalendar
    '...
End Sub

That said, there is a way to invoke a parameterized macro, but as far as I could get things to work, it doesn't work with a variable argument, which appears to be required to be a hard-coded value - the syntax requires enclosing the "macro name" with single quotes, adding a space before the argument, like this:

'Test "test"'

Correctly invokes this macro:

Public Sub Test(ByVal value As String)
    MsgBox value
End Sub

But, if a macro needs to use data that exists on the worksheet at the time of invocation, there's no need to parameterize it. And if a macro needs to prompt the user for a date, a path, or anything, ...then it can absolutely do that.

Note that the date picker control will likely not work properly on a 64-bit host: Consider using another way to prompt the user for a date, that doesn't involve 32-bit ActiveX tech.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235