VBA macro error (macro does not exist)

[brian sullivan]

My question at hand is why do these Macros for creating Menu Command buttons work on my personal computer (Excel 2010/2016), but generates macro may not be available messages on my work one.

My only thoughts are it's relative to the bit /configuration of my work laptop, some various compiler issue tied to old VBA libraries, assignment references being missed, & something random related to private Subs.

Macros in This Workbook. The coding is done in This Workbook, Module, & User Form (Command Button) . The VBA essentially programs a new button on the Excel Ribbon (Menu Commands) that when you click it an user form will generate. However, this does not happen with the VBA error. In overall, the coding is very sound. Therefore, I believe the errors I'm getting are tied to system/configuration items.

This Workbook

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
RemoveMenuItem
End Sub

Private Sub Workbook_Open()
AddMenuItem 
'frmMain.Show
End Sub

MODULE

Option Explicit

Sub Selectfile()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\Users\brian\Desktop\practice file2.xlsm")
End Sub

Sub AddMenuItem()
Dim cmbWorkSheetMenuBar As CommandBar
Dim cmbControl As CommandBarControl

Set cmbWorkSheetMenuBar = Application.CommandBars("Worksheet Menu Bar")
Set cmbControl = cmbWorkSheetMenuBar.Controls.Add(Type:=msoControlButton, temporary:=True)'adds a menu item to the Menu Bar
With cmbControl.Caption ="Show Form"'names the menu item
     .OnAction ="ShowTheForm"'runs the specified macro
     .FaceId =1098'assigns an icon to the dropdown
End With
End Sub

Sub RemoveMenuItem()
On Error Resume Next 'in case the menu item has already been deleted
Application.CommandBars("Worksheet Menu Bar").Controls("Show Form").Delete 'delete the menu item
End Sub

Sub ShowTheForm()
frmMain.Show
End Sub

UserForm Click Code

Option Explicit

Private Sub btnOne_Click()
Application.FindFile
End Sub

 

[NARAYANK991]

Hi Brian ,

When posting a VBA code error problem , try to do the following :

1. Post the exact error description , as it is displayed on screen

2. Post the line of code which has generated the error , since Excel always highlights the line of code when a run time error occurs

3. If necessary , detail the actions which led to this error being generated.

As far as your posted code is concerned , when the workbook is opened , the Workbook_Open event macro is executed ; this calls the macro AddMenuItem , which is present.

Similarly , before you close the workbook , the Workbook_BeforeClose event macro is executed ; this calls the macro RemoveMenuItem , which is present.

Neither of these two macros calls any other macro.

Can you give any additional information which will help ?

Narayan