"Worksheet.activate" with "CommandBarControl.onaction" doesn't working

1
votes

I have a dynamic rightclick menu, that I use to switch between the sheets, and select the necessary lines. My problem is that when the program starts run the macro with the ".onaction" method, the Worksheets("xy") does not prevail.

This is an example without any completeness:

[...]
Dim ws As Worksheet, i as long
i=0
For Each ws In ActiveWorkbook.Worksheets
    Menu1.Controls.add (msoControlButton)
    Menu1.Controls(i).Caption = ws.Name
    Menu1.Controls(i).OnAction = "SheetChange(""" & ws.Name & """)"
    i=i+1
Next

[...]

Sub SheetChange(x as string)
        worksheets(x).activate
        worksheets(x).Range("A1").select
end sub

Is there any way to use "activate" and "select" methods inside a sub, which called with ".onaction"?

1
vbaexcel
See if this helps – Mathieu Guindon
The action is working, can pass parameters as a string. The SheetChange is run correctly (i can do anything on that Sub, but it dosesn't select or change anything, until the Sub ended. – Oliver
Is ActiveWorkbook the workbook you mean to work with, and does it change between the moment you setup your menu buttons and the moment you click them? – Mathieu Guindon
Seems your setup loop would create 20 identical buttons given an active workbook with 20 sheets - all buttons wired up to the same sheet. .Caption = ws.Name, .OnAction = "SheetChange(""" & ws.Name & """)". – Mathieu Guindon
Yep, my bad, I edited, but it the activating with the menu still not working – Oliver

1 Answers

0
votes

Don't use "("...")" try:

Menu1.Controls(i).OnAction = "'SheetChange """ & ws.Name & """'"