StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy is my VBA button behaving strangely on onAction call?
text
Body
copied!<p>I get the following message when my VBA button 's onAction method gets called. <em>The macro may not be available in this workbook or all macros may be disabled</em> </p> <p>In the test.xlsm in ThisWorkbook file I have a very simple code</p> <pre><code>Option Explicit Private Sub Workbook_Open() UpdateMenuSR End Sub Private Sub UpdateMenuSR() Dim cb As CommandBarButton Dim Solver As CommandBar For Each Solver In Application.CommandBars If Solver.name = "Test" Then Exit Sub Next Solver Set Solver = Application.CommandBars.Add("Test", msoBarFloating, False) With Solver .Visible = True With .Controls Set cb = .Add(Type:=msoControlButton) With cb .FaceId = 31 .Visible = True .OnAction = "!b" End With Set cb = .Add(Type:=msoControlButton) With cb .FaceId = 19 .Visible = True .OnAction = "!c" End With Set cb = .Add(Type:=msoControlButton) With cb .FaceId = 30 .Visible = True .OnAction = "!a" End With Set cb = .Add(Type:=msoControlButton) With cb .FaceId = 8 .Visible = True .OnAction = "!d" End With End With End With End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ctrl As CommandBarControl Application.CommandBars("Test").Delete For Each ctrl In Application.CommandBars("Tools").Controls If ctrl.Tag = "Test" Or ctrl.Tag = "Test" Then ctrl.Delete End If Next ctrl End Sub </code></pre> <p>In the Main module I have only </p> <pre><code>Public Sub a() MsgBox "a" End Sub Public Sub d() MsgBox "d" End Sub Public Sub b() MsgBox "b" End Sub Public Sub c() MsgBox "c" End Sub </code></pre> <p>If I do the following procedure:</p> <ul> <li><p>Create new file test2.xlsx</p></li> <li><p>Open test.xlsm</p></li> <li><p>Open test2.xslx</p></li> <li><p>Clicking one after another on the buttons:</p></li> </ul> <p>Results in: "b"</p> <pre><code>"Cannot run the macro '[test.xslm]Sheet1A:A'. The macro may not be available in this workbook or all macros may be disabled" "a" "Cannot run the macro 'd'. The macro may not be available in this workbook or all macros may be disabled" </code></pre> <ul> <li>Switch to test.xlsm </li> </ul> <p>Results in: </p> <pre><code> "b" "Cannot run the macro '[test.xslm]Sheet1A:A'. The macro may not be available in this wo rkbook or all macros may be disabled" "a" "d" </code></pre> <p>Could anyone help me? </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload