+ Reply to Thread
Results 1 to 21 of 21

Assign macro to right click Context Menu (macro in userform)

  1. #1
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    548

    Assign macro to right click Context Menu (macro in userform)

    This workbook has 2 new right click context menu items as shown.
    When clicked, a macro is supposed to run.
    When the macro is written into a Module, it works.
    When a macro is written into a Userform, it doesn't work.
    Any reason why?

    Snapshot.png


    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Assign macro to right click Context Menu (macro in userform)

    .
    If not already done ... place the macro in a REGULAR module, not the UserForm.

    In the UserForm ... reference a call to the macro.

  3. #3
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    548

    Re: Assign macro to right click Context Menu (macro in userform)

    @Logit, I had this before with the code in a regular module. I want to have in the Userform because of other modules and want to use common declarations, etc.
    Was hoping that something like "'!UserForm1.MacroInForm" would work.
    Should I assume it's not possible?

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Assign macro to right click Context Menu (macro in userform)

    .
    Paste this code in the UserForm :

    Please Login or Register  to view this content.
    Note: It gives you immediate access to the single macro.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Assign macro to right click Context Menu (macro in userform)

    You cannot call a routine in a userform like that. It will have to be in a normal module, which I would suggest is where it belongs anyway.
    Rory

  6. #6
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Assign macro to right click Context Menu (macro in userform)

    .
    The suggestion in Post #4 does work.

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Assign macro to right click Context Menu (macro in userform)

    But not for the Cell shortcut menu.

  8. #8
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Assign macro to right click Context Menu (macro in userform)

    .
    As the OP presently has code written ... and his request was to provide for a "right click" access on the UserForm to run a macro ... the code does what he is requesting.

    He has been instructed "Note: It gives you immediate access to the single macro." ....meaning the right click will only run the macro as presently designed/written.
    It caused the MsgBox to display.

    Your observation is accurate ... the #4 suggestion does nothing else.

    It will be up to the OP to include additional code for the macro to provide the goal / s he is seeking. That could be a "Select Case" statement, initialize another UserForm or some other code
    for his purposes.

    I am curious for the purpose/s he intends ... especially as there may be other avenues he could take that might be more effective or easier for the end user.

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Assign macro to right click Context Menu (macro in userform)

    Quote Originally Posted by Logit View Post
    .and his request was to provide for a "right click" access on the UserForm to run a macro
    That is not my interpretation, based on the question and the code posted. I believe the OP wants an item on the right-click cell menu to directly run a routine stored in a userform, which isn't possible. It would require an intermediate routine to load the userform and run the code in it.

  10. #10
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Assign macro to right click Context Menu (macro in userform)

    .
    Possibly. We will see ....

  11. #11
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    548

    Re: Assign macro to right click Context Menu (macro in userform)

    The discussion above is worthwhile.
    The right-click item is supposed to run a routine within the form. From post #9 I gather this is not possible.
    Thanks for the effort.

  12. #12
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Assign macro to right click Context Menu (macro in userform)

    .
    What routine is to be run ?

  13. #13
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    548

    Re: Assign macro to right click Context Menu (macro in userform)

    @Logit, it's used to run the code which:
    Looks at cell strings and colours each character based on the font of the character.
    Unfortunately the code can't be written due to the following error: "Access Denied - Sucuri Website Firewall".
    Please see attached.
    Attached Files Attached Files

  14. #14
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Assign macro to right click Context Menu (macro in userform)

    I can't really see why that code should be in a userform?

  15. #15
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    548

    Re: Assign macro to right click Context Menu (macro in userform)

    @rorya, there are other modules in the form, as well as some common declarations.
    Anyway I've taken it onboard to have it in a module.

  16. #16
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Assign macro to right click Context Menu (macro in userform)

    Quote Originally Posted by Un-Do Re-Do View Post
    there are other modules in the form
    I guess you mean routines, since a form only has one module? Even so, that doesn't mean that routine has any real business being in the form, since it dpoesn't appear to relate to a userform in any way. And especially not if you need to call it from elsewhere.

  17. #17
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    548

    Re: Assign macro to right click Context Menu (macro in userform)

    @rorya, I understand the but the user usually has a reason for the requirement.
    There is a button in the form which also call up the same routine. There are several other routines within the form which are similar in nature.
    The code posted is simplified to try and get an answer.
    Understand and appreciate the holistic advice but a lot of the time user has a reason.
    In any event for this case, there was no option but to separate the routines into different modules.

  18. #18
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Assign macro to right click Context Menu (macro in userform)

    Why would it matter to the user how the code is implemented?

  19. #19
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    548

    Re: Assign macro to right click Context Menu (macro in userform)

    Sorry but by "user" I meant myself.
    The Project is quite large with many modules and routines. It's just a way for me to rationalise.

  20. #20
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Assign macro to right click Context Menu (macro in userform)

    Gotcha.

    Not what I would call good practice, for what that's worth.

  21. #21
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    548

    Re: Assign macro to right click Context Menu (macro in userform)

    Point taken.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Adding to Right-Click Context Menu
    By FlossHoss in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-06-2014, 10:02 AM
  2. [SOLVED] Add-In and Cell Context (Right-Click) Menu
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-06-2014, 10:04 AM
  3. [SOLVED] Add-In & Cell Context (Right-Click) Menu
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-28-2014, 04:39 AM
  4. [SOLVED] How to remove commands like cut, copy, assign macro or ... from shape context menu
    By hmaleki in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2013, 04:10 AM
  5. Right-Click (Context Menu)
    By Kryptonix in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-25-2005, 11:05 AM
  6. Assign macro to right click menu ?
    By Richard in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-25-2005, 11:05 AM
  7. Right Click Context Menu
    By Guy Lapierre in forum Excel General
    Replies: 1
    Last Post: 03-30-2005, 07:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1