Page 1 of 2 12 LastLast
Results 1 to 15 of 21

I copied VBA from MSDN for a shortcut menu. Don't know how to fix it.

  1. #1
    PeakH is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    84

    I copied VBA from MSDN for a shortcut menu. Don't know how to fix it.

    I know I've seen this code on other posts here, but couldn't find the answer I needed.


    I am also very new to VBA.

    I'm trying to make a custom shortcut menu for my reports, found this code on MSDN, and just copied and pasted it since I don't understand VBA enough to read it, understand it and make it unique to my database. I figured I would play around with it later to understand it better, but I haven't gotten that far.

    When I try to run it I get a message "Compile Error: User-defined type not defined" and highlights the very first line.

    I would really appreciate any insight on how I can use this code (or an easier way to make shortcut menus).


    Code:
    Sub CreateReportShortcutMenu()
    
    
    Dim cmbRightClick As Office.CommandBar
        Dim cmbControl As Office.CommandBarControl
     
       ' Create the shortcut menu.
        Set cmbRightClick = CommandBars.Add("cmdReportRightClick", msoBarPopup, False, True)
     
        With cmbRightClick
             
            ' Add the Print command.
            Set cmbControl = .Controls.Add(msoControlButton, 2521, , , True)
            ' Change the caption displayed for the control.
            cmbControl.Caption = "Quick Print"
             
            ' Add the Print command.
            Set cmbControl = .Controls.Add(msoControlButton, 15948, , , True)
            ' Change the caption displayed for the control.
            cmbControl.Caption = "Select Pages"
             
            ' Add the Page Setup... command.
            Set cmbControl = .Controls.Add(msoControlButton, 247, , , True)
            ' Change the caption displayed for the control.
            cmbControl.Caption = "Page Setup"
             
            ' Add the Mail Recipient (as Attachment)... command.
            Set cmbControl = .Controls.Add(msoControlButton, 2188, , , True)
            ' Start a new group.
            cmbControl.BeginGroup = True
            ' Change the caption displayed for the control.
            cmbControl.Caption = "Email Report as an Attachment"
             
            ' Add the PDF or XPS command.
            Set cmbControl = .Controls.Add(msoControlButton, 12499, , , True)
            ' Change the caption displayed for the control.
            cmbControl.Caption = "Save as PDF/XPS"
             
            ' Add the Close command.
            Set cmbControl = .Controls.Add(msoControlButton, 923, , , True)
            ' Start a new group.
            cmbControl.BeginGroup = True
            ' Change the caption displayed for the control.
            cmbControl.Caption = "Close Report"
        End With
         
        Set cmbControl = Nothing
        Set cmbRightClick = Nothing
    End Sub

  2. #2
    PeakH is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    84
    I did just realize that on the source website for that code it says that I must set a reference to the Microsoft Office 15.0 Object Library. I checked my reference, which is to the Microsoft Office 16.0 Object Library.
    I did not have 15.0 available to me. Is there a way to change the VBA to fit with 16.0?

  3. #3
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    2,711
    I was about to tell you that you have a missing reference! There will only be one available to you and the number refers to the version you have installed, so that one is the one you want.

  4. #4
    PeakH is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    84
    Okay, so do I just need to figure out what parts of the code need to be updated to 16.0 objects?

  5. #5
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    2,711
    No, just add the reference and recompile, it should be error-free.

  6. #6
    PeakH is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    84
    Hmm, I can't seem to find it anywhere. It's not on the reference list. Not sure how to find it through browse since they all have weird names that aren't what display on the list, so I don't know if I need to download it...
    I'll keep searching for it.

  7. #7
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    2,711
    Where are you looking? In post #2 you inferred that you had access to it? You are in Tools>References, right?

  8. #8
    PeakH is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    84
    I am looking in Tools>References.
    I meant to say that it is not there. Only Office 16.0 is there. Excel 15.0 is on the list, but not Office 15.0 so I clicked Browse but I don't even know where to start there. References seem to be in tons of folders and with unrecognizable names.

  9. #9
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    2,711
    Select the Office 16.0

  10. #10
    PeakH is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    84
    Oh, maybe I didn't understand what you were saying earlier.
    I'm already set on 16.0, but 15.0 is needed to run the code.
    So are you saying that I just need to recompile the database and it should work?

  11. #11
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    2,711
    15.0 is not needed to run the code - Office Object Library is. The number refers to whatever version of Access you are running - 15.0 is Access 2013, 16.0 is Access 2016.

  12. #12
    PeakH is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    84
    Oh! Okay. I was very confused since I have no idea what I'm talking about.

    I found that while the office 16.0 was already selected, there was a second office 16.0 option! So I selected that, re-compiled, and it works!!

    Thank you for going round in circles with me. It was a huge help!

  13. #13
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    2,711
    There is an Access Object Library too which also gets people confused - don't worry, this happens to everyone!

  14. #14
    PeakH is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    84
    Thought I had it all working, but I've got another problem.
    When I close/open the database, I have to re-run the module to keep my shortcut menu.
    The site that I got the code from says:

    "Once you've run the code, the shortcut menu is saved as part of the database. You don't have to run the same code to re-create the shortcut menu every time that you open the database."

    But this seems to be exactly what I need to do.
    Any more words of wisdom?

  15. #15
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    2,711
    Yes, it needs to be run every time the database opens. What happens when you open your database? To automate, either run a macros called "AutoExec" or add a form to open in the Access options. If the macro, run the subroutine; if a form opens, run the sub in the OnLoad event of the form.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Custom shortcut menu ?
    By Lukael in forum Access
    Replies: 3
    Last Post: 02-08-2016, 10:50 AM
  2. Error with Custom Shortcut Menu
    By MatthewR in forum Forms
    Replies: 1
    Last Post: 09-15-2015, 12:17 PM
  3. Unable to create shortcut menu
    By lawdy in forum Programming
    Replies: 36
    Last Post: 05-25-2014, 09:36 AM
  4. Print Preview Custom Shortcut Menu
    By Swatskeb in forum Modules
    Replies: 2
    Last Post: 05-22-2014, 07:02 PM
  5. custom shortcut menu help!
    By ninachopper in forum Access
    Replies: 5
    Last Post: 09-05-2010, 05:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums