?
Solved

Run vba with a right-click mouse command

Posted on 2011-05-11
18
Medium Priority
?
1,239 Views
Last Modified: 2012-05-11
I have the following code where I would like is when I right-click on the field there is an option which says "Open File" that will run the following:

Dim strfilepath As String

strfilepath = "\\jassrv03\jas-fp$\Documents\" & Me.DocKey.value

If IsFile(strfilepath) Then
Application.FollowHyperlink strfilepath

Else
MsgBox "File Does Not Exists"
End If

I have researched this and it appears I would have to write macros and all which I would prefer not to do.   Is there a way of doing this with VBA?

0
Comment
Question by:mtrussell
  • 6
  • 5
  • 4
  • +3
18 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35742006
<I have researched this and it appears I would have to write macros and all which I would prefer not to do.>
(...And possible goof around with the Ribbon interface to create the right click...?)


IMHO, a quicker, direct way would be to just use a "Open File" button...

...and insert your code on the OnClick event of this button.

JeffCoachman

0
 
LVL 31

Expert Comment

by:hnasr
ID: 35742057
MouseUp event can check for right button and execute your code.
Try:

Private Sub f2_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If Button = RIGHT_BUTTON Then
        SendKeys "{Esc}"
        'run your code here
    End If
End Sub
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35742138
hnasr,

Yeah, but I really don't like going against standard UI rules.
So I don't use right clicks to Run code directly, I use then to bring up the menu only.

This is why I suggested going with a simple button.

I don't know how doing this globally will effect how/when the standard Rt-click context menu appears...

JeffCoachman

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:mtrussell
ID: 35742529
Yeah, the button works but in this particular place it is easier if the user clicks or double-click of the field to launch something I am trying to do. I already have a double click routine so the idea of customizing the command options with a right click came to mind... the code works but what I was actually looking for was to add an item to the right-click command options where open file was one of the options instead of an automatic launch of the code.

0
 
LVL 26

Expert Comment

by:Nick67
ID: 35742903
You could have your double-click event bring up a small form with buttons for your options on it, the new one, and the old double-click one, and then the user could choose by clicking the appropriate button, or cancel by clicking the 'x'
A custom 'context' menu if you will, driven by the double-click.

I give all my controls that have a double-click event a red border.
The users don't necessarily remember what each does, but they do know that the red border signifies you can double-click
And they know that it'll be something useful, needful and risk-free that shows up when they do.

YMMV
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35742958
Well, I did it, but it is not the type of thing you can easily explain in a forum like this.

The crux is that you must create the bulk of this in Access 2003
Create a function that holds your code (possibly changing the control reference)
Create a macro that calls this function
Create a custom Popup menu and insert this macro
Assign this menu to the form's ShortCut Menubar property
(So obviously, you will have to be proficient with doing this type of thing in Access 2003 first...)

Then test this in Access 2003.

Then Open this file in Access 2007 then convert it to the Access 2007 format.

I tested it in the attached sample and it works fine, (opens the file if it exists) so it might be easier to simply take my attached sample and import all of your objects into it and change the references, and swap out my IsFile() Function for yours. (and probably a few other tweaks)

I'm sure you can take it form here....

Have Fun

;-)

JeffCoachman
 screen Access-EEQ27032799RightClickPopu.mdb
Access-EEQ27032799RightClickPopu.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35743052
added validation for blank DocKey

(Again, the kicker here is that the right click may not update the value in an unbound text box, so you may have to press tab after entering a value...)

Screencast attached
boag2000-458520.flv
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35743093
sorry,
updated file attached...
Access-EEQ27032799RightClickPopu.accdb
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 35743119
Jeff, a simpler way in code without setting up macros or anything else (sorry have not looked at your db so apologise if you did this)

something I have done before


First create your right click menu use a command bar object -- example gives it a name of MyRightClickStuff
you can put this in your form load code or something

    Dim cb As CommandBar
    Dim cbc As CommandBarControl
   
    Set cb = CommandBars.Add("MyRightClickStuff", msoBarPopup)
    Set cbc = cb.Controls.Add
    With cbc
        .Caption = "Open FIle"
        .OnAction = "=openSesame()"
        .Tag = "EESample"
    End With
   

Then create a function IN A MODULE. NOT FORM called openSesame

public function openSesame
    msgbox "Hello World. I am in need of some bagels"
end function


In form you simply assign the shortcutmenubar for the form control of your choice (or form)

    Me.MyTextBox.ShortcutMenuBar = "MyRightClickStuff"

now run the form
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35743232
RR,

What's up?
How have you been?

You know, ...I never noticed that a textbox could have it's own shortcut menu...
;-)

   "Every day is s school day"


At least the OP has lots of great options to consider now...

;-)

Jeff
0
 
LVL 75
ID: 35743304
My Motto to ALL my users:

"When I Doubt, Right Click."

Many of my custom Right Click (aka PopUp aka Context Menus) run custom function.  Very powerful stuff.  Guess that's why Microsoft included the On Action property :-)

mx
0
 

Author Comment

by:mtrussell
ID: 35744908
Rocki - thanks for your comments - I have done the following.  


1.  Added on FormLoad the MyRightClick Code
2.  Set up in a module a Public Function with the code I originally posted called Open Sesame
3.  I set the field and and form SortCut Menu Bar to MyRight Click


When I open the form I am getting the following:

RunTime Error '5':
Invalid procedure call or argument

and it is breaking at the line on loading>>     Set cb = CommandBars.Add("MyRightClickStuff", msoBarPopup)


I had a look around and it appears I haven't set the command bar object for myrightclickstuff.   How do I do this?  Is this an Access 2003 import requirement or is it something you can do in 2007?

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 35748283
I'm sorry, I forgot to mention.
You have to include the Office Object library as a reference

in vba window, click on Tools then references then look for Office Object Library. Version differs depending on your office version
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 35748528
And the other thing (old age catching up on me doh!)

in case the menu already exists we should create it

add this bit first

    On Error Resume Next
    Set cb = CommandBars("MyRightClickStuff")
    cb.Delete



0
 

Author Comment

by:mtrussell
ID: 35748686
Thanks for this.  I checked the references and I already have Microsoft Access 12.0 Object Library checked.  Is there another reference this applies to?
0
 

Author Comment

by:mtrussell
ID: 35753037
RR -

Adding the code fixed the issue.  Everything works great.  This is very cool stuff.  I was trying to add another command to the  command bar but every time I do only the second control shows up when I right click.  How do you add more than one control?

Here is the butchered code to show I am at least making an effort.  ;)  


Private Sub Form_Load()

Me.Combo15.SetFocus

Dim cb As CommandBar


    Dim cbc As CommandBarControl
    Dim cbc2 As CommandBarControl
   
    On Error Resume Next
    Set cb = CommandBars("MyRightClickStuff")
    cb.Delete
    Set cb = CommandBars.Add("MyRightClickStuff", msoBarPopup)
    Set cbc = cb.Controls.Add
    Set cbc2 = cb.Controls.Add
   
   
    With cbc
        .Caption = "Open File"
        .OnAction = "=openSesame()"
        .Tag = "EESample"
       
   With cbc2
        .Caption = "Send Email"
        .OnAction = "=openSesame()"
        .Tag = "EESample"
   
    End With

End Sub
0
 

Author Closing Comment

by:mtrussell
ID: 35753471
Looks like I figured it out.  thanks.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 35755721
Hi sorry, busy at work. Have the internet police down now, they have blocked all mail, online banking, and other types of sites down so cant even check notifications anymore :(

anyways for the benefit of others, how I would do it is add new controls one at a time though (similar to code above) you can get away with using the same CommandBarControl variable.

eg


Set cbc = cb.Controls.Add
With cbc
    .Caption = "Open File"
    .OnAction = "=openSesame()"
    .Tag = "EESample1"
end with
       
Set cbc = cb.Controls.Add
With cbc
    .Caption = "Send Email"
    .OnAction = "=openSesame()"
    .Tag = "EESample2"
End With

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question