.onaction open folder window

[sandysharma]

Hi Everyone,

Need help with .onaction

I need to open a folder window when someone clicks on PDF file msocontrolbutton but not sure how would I apply the code to open window folder in the below code. Tried Shell but did not work. Need help on the bold line.

Sub Custom_PopUpMenu_1()
  Dim menuItem As CommandBarPopup
 
  ' Add the popup menu.
  With Application.CommandBars.Add(Name:=Mname, Position:=msoBarPopup, _
  MenuBar:=False, Temporary:=True)

  ' Next, add a menu that contains one button named PDF Files.
 
  With .Controls.Add(Type:=msoControlButton)
  .Caption = "PDF Files"
  '.FaceId = 73
  .OnAction = "shell explorer.exe  C:\Users\sandeep20583\Desktop\Reporting - 2017\Contracts\Contracts - Signed PDFs"
  End With

End With
 
End Sub

 

[Hui]

This is the code to open an Explorer window in a specific directory

Sub ShowFolder()
Shell "explorer.exe /root,C:\Users\sandeep20583\Desktop\Reporting - 2017\Contracts\Contracts - Signed PDFs", vbNormalFocus
End Sub

 

[NARAYANK991]

Hi ,

The .OnAction method cannot call an external procedure such as Shell. It can only call a VBA procedure (macro).

Try this :

Sub Custom_PopUpMenu_1()
  Dim myBar  As CommandBar
  Dim myControl As CommandBarButton
  Dim Mname As String
 
  Mname = "Custom_PopUpMenu_1"
  On Error Resume Next
  CommandBars(Mname).Delete
  On Error GoTo 0
  Set myBar = CommandBars.Add(Name:=Mname, Position:=msoBarPopup, MenuBar:=False, Temporary:=True)
' Add the popup menu.
  With myBar
'      Next, add a menu that contains one button named PDF Files.
      Set myControl = .Controls.Add(Type:=msoControlButton)
      With myControl
            .Caption = "PDF Files"
'          .FaceId = 73
            .OnAction = "MySub"
      End With
  End With
End Sub

Public Sub MySub()
Shell "C:\WINDOWS\system32\CALC.EXE", 1 '"explorer.exe  C:\Users\sandeep20583\Desktop\Reporting - 2017\Contracts\Contracts - Signed PDFs"
End Sub

I have used the Windows Calculator to illustrate the functioning of the .OnAction method. You can replace this with the complete path for the Windows Explorer executable.

Narayan

 

[sandysharma]

Thanks every one....this worked well...had to use explorer.exe else it was giving error...not sure why...

Public Sub MySub()
Shell "explorer.exe" & " " & "C:\Users\sandeep20583\Desktop\Reporting - 2017\Contracts\Contracts - Signed PDFs", vbNormalFocus
End Sub