自定义菜单通过.OnAction传递WorkSheet变量 [英] Custom menu Passing a WorkSheet variable through .OnAction

问题描述

我一直使用这篇文章作为传递变量的指南，直到现在

I have been using this post as a guide to pass variables until now Excel, VBA: How to pass multiple variables to .OnAction

它的效果非常好。不幸的是，我这次尝试传递一个工作表参数，它不工作。这是一个代码块，所以你可以看一下。这是一个右键菜单：

And it has worked very well. Unfortunately, I am trying to pass a worksheet argument this time, and it is not working. Here is a block of code so you can take a look. It is for a right click menu:

       With menu.Controls.Add(Temporary:=True, Type:=msoControlButton, before:=i_menu)
            .BeginGroup = False
' ' ' ' ' ' .OnAction = "'" & ThisWorkbook.Name & "'!" & "'saute """ & num_doc & """,""" & num_etape & """'"
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "'saute """ & num_doc & """,""" & num_etape & """,""" & ws & """'"
            .FaceId = 478
            .Caption = "Sauté"
            .Tag = "commande_custom"
        End With

具有多个撇号的行是我的旧行，那是工作。我在其下添加了一个工作表参数。作为参考， saute 是一个子，其参数是（num_doc作为字符串，num_etape作为字符串，ws作为工作表）。我也尝试使用ws左右两个较少的引号，无济于事。任何人都可以帮忙弄清楚如何发送，和/或提出另一种方式将信息发送到我的Sub（不管是字符串还是其他东西）？

The line with multiple apostrophes is my old line, that was working. I added a worksheet argument just under it. For reference, saute is a sub, and its arguments are (num_doc as string, num_etape as string, ws as Worksheet). I have also tried using two fewer quotation marks both left and right of ws, to no avail. Can anyone help figure out how to send, and/or propose another way of sending the information to my Sub (be it a string or something else)?

推荐答案

显然，在弄清楚之后，我发现一个工作表不是一个，而是三个，但是两个报价。使用数字变量和变体采取1对引号，字符串占用3.已解决的行：

Apparently, after fiddling with it, I figured out that a Worksheet takes not one, not three, but TWO quotations around it. With numeric variables and variants taking 1 pair of quotations, and strings taking 3. Resolved line:

.OnAction = "'" & ThisWorkbook.Name & "'!" & "'saute """ & num_doc & """,""" & num_etape & ""","" & ws & ""'"

这篇关于自定义菜单通过.OnAction传递WorkSheet变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！