Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Right Click menu for an embeded ppt object (2002 SP3)

    Edited by HansV to provide link to post and to add <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags to preserve code indentation - see <!help=19>Help 19<!/help>

    Hi, I have had some great help from the Lounge with a previous problem so I hope you excel wizards can give me some assistance. I have a spreadsheet with over 200 embeded powerpoint files and I would like to replace the menu that appears when I rightclick on an object. If possible I only want 4 things on the menu.
    1) Open the file in ppt
    2) save the file to a user specified location (without opening PPT)
    3) print the file to a user specified printer (without opening PPT)
    4) email the file using the system default email app (without opening ppt)

    Please can anyone give me some pointers as I am losing the will to live with this. I can't seem to find any hints on cross application controls from Excel and my VBA skills are very basic. Any help would be appreciated. All I have seen on custom menus seems to involve putting code in multiple places in a spreadsheet. Is there a simple solution that I can use to keep the code associated with only the worksheet?

    There is some code here - <post#=436682>post 436682</post#> which may help with the application calls to ppt.

    And this is all I have been able to find on custom menus.
    <pre>Sub CreatePopUp() ' creates the custom popup menu
    Dim cb As CommandBar, m As CommandBarPopup
    DeletePopUp
    Set cb = CommandBars.Add(PopUpCommandBarName, msoBarPopup, False, True)
    With cb
    With .Controls.Add(Type:=msoControlButton)
    .OnAction = "MyMacroName"
    .FaceId = 71
    .Caption = "Custom Menu 1"
    .TooltipText = "Custom Tooltip Text 1"
    End With
    With .Controls.Add(Type:=msoControlButton)
    .OnAction = "MyMacroName"
    .FaceId = 72
    .Caption = "Custom Menu 2"
    .TooltipText = "Custom Tooltip Text 2"
    End With
    With .Controls.Add(Type:=msoControlButton)
    .OnAction = "MyMacroName"
    .FaceId = 73
    .Caption = "Custom Menu 3"
    .TooltipText = "Custom Tooltip Text 3"
    End With
    Set m = Nothing

    End With
    Set cb = Nothing
    End Sub
    </pre>

    If someone can give me an idiots guide to where I need to start etc. that would be greatly appreciated. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Right Click menu for an embeded ppt object (2002 SP3)

    Hi Michael,

    Welcome to Woody's Lounge.

    The thread starting at <post#=412664>post 412664</post#> has information and several links about creating / modifying right-click menus.

    I doubt whether your options 2), 3) and 4) are possible with the added condition that PowerPoint shouldn't be opened.

  3. #3
    New Lounger
    Join Date
    Dec 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Right Click menu for an embeded ppt object (2002 SP3)

    Edited by HansV to split code into separate lines - an entire procedure on one line isn't readable.

    I had a look at the post but I can't get the test code Pieterse suggests to work on an object. I have changed Cell to object but no joy. Is it possible at all to customise it on an object in excel?

    Sub test()
    Dim cControl As CommandBarControl
    With Application.CommandBars("Cell")
    For Each cControl In .Controls
    cControl.Delete
    Next
    Set cControl = .Controls.Add(msoControlButton, , , , True)
    cControl.Caption = "Test"
    End With
    End Sub

    Sub ResetCommandbar()
    Application.CommandBars("Cell").Reset
    End Sub

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Right Click menu for an embeded ppt object (2002 SP3)

    The code works fine for me, what happens for you?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Right Click menu for an embeded ppt object (2002 SP3)

    If you are trying to bring a short-cut menu when you right click an ole object (not on the cell) change your 2 lines of code to reference the "ole Object" menu and not the "cells" menu

    Use:
    Application.CommandBars("OLE Object")

    Instead of
    Application.CommandBars("Cell")

    Steve

  6. #6
    New Lounger
    Join Date
    Dec 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Right Click menu for an embeded ppt object (2002 SP3)

    Hurrah! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> I needed the object to be OLE Object not just object. I should have thought of that myself. I will have a tinker this morning and see if I can progress the menu. I will probably be back for some tips...

  7. #7
    New Lounger
    Join Date
    Dec 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Right Click menu for an embeded ppt object (2002 SP3)

    Right - I have been tinkering with the code (I can't really say mine as you guys have given me the hard bits!). And I am making headway but have run into a few things I don't know how to do.

    OK I have put my code in this post and I have some questions in the comments. But in general I have the code for the custom menu, but not a means to activate it on a right click on an embeded object. I tried right click before but no joy with that. Then I am struggling to adapt the code for saving the files and I have no idea on how to write the email code. I have given up on trying to hide powerpoint from running, but if the code for saving an email could close it afterwards that would be great.

    Can you clever people give me some more pointers?
    Sub test()
    Dim cControl As CommandBarControl
    With Application.CommandBars("OLE object")
    For Each cControl In .Controls
    cControl.Delete
    Next
    Set cControl = .Controls.Add(msoControlButton, , , , True)
    With .Controls.Add(msoControlButton)
    .OnAction = "OpenPlay"
    .FaceId = 71
    .Caption = "Open Play in PowerPoint"
    End With
    With .Controls.Add(msoControlButton)
    .OnAction = "SavePlay"
    .FaceId = 72
    .Caption = "Save Play to"
    End With
    With .Controls.Add(msoControlButton)
    .OnAction = "emailplay"
    .FaceId = 73
    .Caption = "e-mail Play to"
    End With
    End With
    End Sub

    Sub OpenPlay()
    Selection.Verb Verb:=3
    End Sub

    Sub SavePlay()
    'this sub is not working and it relates to the two Set statements but I can't see why
    Dim obj As OLEObject
    Set ppt = CreateObject("PowerPoint.Application")
    Set ppt = obj.Object.Application
    ppt.ActivePresentation.SaveAs File & ppt.ActivePresentation.Name & ".ppt"
    ppt.Quit
    Set ppt = Nothing
    Set obj = Nothing
    End Sub

    Sub emailplay()
    'no idea where to begin
    End Sub

    Sub ResetCommandbar()
    'do I need this to run to reset the menus? and if so when should I call it?
    Application.CommandBars("OLE object").Reset
    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Right Click menu for an embeded ppt object (2002 SP3)

    In SavePlay, you never specify what obj is, so the code doesn't know what to do with it. Try this:

    Sub SavePlay()
    Dim ppt As Object
    Set ppt = Selection.Object.Application
    ppt.ActivePresentation.SaveAs File & ppt.ActivePresentation.Name & ".ppt"
    ppt.Quit
    Set ppt = Nothing
    End Sub

    PowerPoint VBA doesn't seem to expose the "Send to e-mail recipient as attachment" function.

    You would call the ResetCommandBar procedure in the BeforeClose event of your workbook.

  9. #9
    New Lounger
    Join Date
    Dec 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Right Click menu for an embeded ppt object (2002 SP3)

    Sorry to come back negative but that bugs out on the 2nd line complaining about the being unable to get the object properties.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Right Click menu for an embeded ppt object (2002 SP3)

    <P ID="edit" class=small>(Edited by sdckapr on 22-Dec-04 13:27. Added PostScript)</P>The object must be selected. Is the object selected? It should be when you right-click it.

    I think you will also need:
    Selection.Activate
    ppt.ActivePresentation.SaveAs File & ppt.ActivePresentation.Name & ".ppt"
    ActiveCell.Select

    The first activates and opens the object, the last gets back to xl proper. ppt must have an active presentation for it to work

    Steve
    PS I also had more consistent luck with the line
    Set ppt = CreateObject("PowerPoint.Application")
    instead of trying to have excel figure out the object

  11. #11
    New Lounger
    Join Date
    Dec 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Right Click menu for an embeded ppt object (2002 SP3)

    Thanks for that - I will continue to play with the code and let you guys know how I get on.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •