[Excel vba] Issue with macro assignment -> object

re :

Here, the command chain should look like this:

 Sub Test() Dim Chaine As String Dim N As Long, J As Long N = 2: J = 5 Chaine = "'SELECTION_TRONCON" & "(" & N & "," & J & ")'" MsgBox Chaine End Sub ' 

The name of the function, including parameters, must first be enclosed in single quotes (quote)
and the whole thing in double quotes.

That's why the syntax starts like this:

Chaine = " ' SEL...


Lupin

Thank you Lupin,
Your solution works well with the MsgBox instruction, but when I try to assign the string to a button via .OnAction, it doesn't work and I get "Could not find the macro." I imagine I need to add some series of quotes somewhere, but I'm not quite sure where?

Here is the code:

Sub ...

H As Integer
W As Integer

...

.OnAction = "affectemacro(" & H & "," & W & ")"

End Sub

I don't remember what to do to achieve this. Thank you for your help.

re:

Is the macro you are calling [ affectemacro ] in another module?
Is this macro declared as [ Private ]?

Otherwise, you would need to paste the complete instruction so that I can
make it work. I discovered this syntax in my book a few
years ago, but I only used one argument.

Given that a function or routine can have more than one
argument, it is clear that it should work.

Lupin

To be brief, here is the logic of the code.

I have a macro that performs various calculations based on a range of data (Range) and an integer.

I need to be able, via a button, to duplicate the table containing the data and to add a button for each new occurrence to execute the previous macro (calculations)


Here is the code in question

The compute macro

Public Sub compute_delivery(my_cells As Range, market As Integer)

Dim...

Line1 = Line2 + 1 'formulas are actually more complicated in reality
Line2 = Line3 * 1/2

...

End Sub

The part adding a button (the tricky part):

Sub Add_button_w_macro(PositionCell As Range, Txt As String, Data_macro As Range)
Const H As Integer = 51
Const W As Integer = 100
'
'
With ActiveSheet.Shapes.AddShape(msoShapeActionButtonCustom, PositionCell.Left + 6, PositionCell.Top + PositionCell.Height + 6, W, H)
With .Fill
.ForeColor.RGB = RGB(37, 64, 97)
.TwoColorGradient msoGradientHorizontal, 1
End With

With .TextFrame
.MarginBottom = 5
.MarginLeft = 5
.MarginRight = 5
.MarginTop = 5
.Characters.Text = Txt
'"Click here to" & Chr(10) & "realize schedule" & Chr(10) & " -MPR- "
.Characters.Font.Color = RGB(0, 0, 0)
.Characters.Font.Bold = True
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
End With
.OnAction = "'compute_delivery " & "(" & """" & Data_macro & """" & ")'" 'works well with a parameter
.OnAction = "'compute_delivery (""Data_macro"",""1"")'" 'does not work with a 2nd
End With
End Sub

Hi,
I believe I’ve partially understood your problem. I made an example that might help you. It takes 2 minutes to create.

1) In an Excel sheet, draw 2 rectangles. In the top left, you’ll have Rectangle 1, and Rectangle 2 in the name box next to the text box.
2) In cell A1, enter 1
3) Create the following macro:

Sub selectionshape ()

A = Cells(1, 1).value
ActiveSheet.Shapes("Rectangle " & A).Select

End Sub

4) Run your macro. If your cell A1 contains the value 1, your Rectangle 1 will be selected; if it contains the value 2, your Rectangle 2 will be selected.

This is just an idea; I hope it helps you. Let me know how it goes.

Thank you, but it's not really what I need.

I need to have the same macro on the OnAction of my shapes, and that this macro is able to detect the name of the shape that was just clicked (with something like ActiveShape.Name, but that doesn't work) in order to assign the section number to the parameter N and then launch the macro SELECTION_TRONCON(N).

In fact, the main problem is that I don't know the syntax that can return the name of the shape that was clicked, for the rest it will be easy!

Thank you in advance for your ideas.