Issue with using the With End With Technique

Heshamello

New Member
Joined
Mar 10, 2016
Messages
1
Hello Excel Masters
This is my first question on Mr Excel I hope You can Help me
I am new to VBA and I whenever I come across a new code I try to simplify before learning
one of the things I do is I remove all With - End With and replace them with the original line so I can better understand the code
While I was learning how to do popup menus I found the below code that is working perfectly

Code:
[COLOR=#3366CC]Sub PopUpMenu()[/COLOR]    
    
    With Application.CommandBars.Add(Name:="Pop1", Position:=msoBarPopup, _
                                     MenuBar:=False, Temporary:=True)

        
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Button 1"
            .FaceId = 71
            .OnAction = msgbox ("Yahooooo, It worked")
        End With

   End With
End Sub

As usual I tried to remove all the With and replace it with the Original Line so my code was like this

Rich (BB code):
Rich (BB code):
Sub PopUpMenu()
 
    Application.CommandBars.Add(Name:="Pop1", Position:=msoBarPopup, MenuBar:=False, Temporary:=True).Controls.Add(Type:=msoControlButton).Caption = "Button 1"
    Application.CommandBars.Add(Name:="Pop1", Position:=msoBarPopup, MenuBar:=False, Temporary:=True).Controls.Add(Type:=msoControlButton).FaceId = 71
    Application.CommandBars.Add(Name:="Pop1", Position:=msoBarPopup, MenuBar:=False, Temporary:=True).Controls.Add(Type:=msoControlButton).OnAction = MsgBox("Yahooooo, It worked")


 End Sub

but I always get the error : Run-time error '5': Invalid Procedure call or argument.
I know it's a weird question but I always learnt like this and I am going mad trying to figure out the problem with this

Thanks in advance

 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,385
Office Version
  1. 2019
Platform
  1. Windows
Hi welcome to Forum

Nothing wrong with your With Statement but what you have done is place a call for a MsgBox againsts the OnAction line which is why getting the error.

- you would call a procedure as shown below:

Rich (BB code):
Private Sub PopUpMenu()
    
    With Application.CommandBars.Add(Name:=Mname, Position:=msoBarPopup, _
                                     MenuBar:=False, Temporary:=True)


        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Button 1"
            .FaceId = 71
            .OnAction = "mymacro"
        End With
   End With
End Sub


Rich (BB code):
Private Sub mymacro()
    MsgBox ("Yahooooo, It worked")
End Sub


Have a read here:https://msdn.microsoft.com/en-us/library/office/gg987030(v=office.14).aspx

which is where I suspect you may have taken the code from for further guidance with your project.

Dave
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,147
Members
414,365
Latest member
UUR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top