Before Right click Event

According that stupid paper clip in the VBE help the code below will create and display a shortcut menu whenever the user right clicks.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As _ Object, ByVal Target As Excel.Range, Cancel As Boolean)
Dim x as Commandbar, y as Control, z as Object
Set x = CommandBars.Add("Custom", msoBarPopup)
Set y = x.Controls.Add
With y
    .FaceId = 26
    .Caption = "Analyze the data"
End With
Set z = x.Controls.Add
With z
    .FaceId = 17
    .Caption = "Graph the data"
End With
x.ShowPopup 200, 200
End sub

But I cannot get it to work, I think I'm missing something very obvious but I don't know what!

antrat

LVL 9
antratAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ahammarCommented:
Good question.
I can't get it to work either. I can't get past the first line.

I'm experimenting though.

Cheers!
ahammar
0
ahammarCommented:
Ok antrat,
I think I figured it out (or atleast partially).
There are 2 things I had to do.
First I had to dim y as a variant, instead of a control. I don't know why yet.
Secondly, it works the very first time you run it.
It won't work again unless you change the name of the command bar, because it has to have a different name the next time I think. You can add a temporary argument at the end to make it a temporary bar, but then I get an error on the very last line. Still working on that one.

Try changing y to a variant, then change the word custom to something else, and see what happens.
Remember it will only work once.

I'm still experimenting.

Cheers!
ahammar

0
ahammarCommented:
********************************************************************************
Guess what? I've been using the WorkSheets event instead of the WorkBook event. This (and what I posted previously) works in the Sheet BeforeRightClick event, but not the Workbook BeforeRightClick event so it's still not what you really want, but maybe it will help.
I couldn't find a way to make it work perfectly yet by using the arguments. I can get it to accept the temporary argument, but I can't get it to accept the MenuBar argument as True.
Temporary = True is suppose to mean the bar is temporary,
MenuBar = True is suppose to mean to overwrite the existing bar.

Here is the help listing on the arguments :
Syntax

expression.Add(Name, Position, MenuBar, Temporary)

expression   Required. An expression that returns a CommandBars object.

Name   Optional Variant. The name of the new command bar. If this argument is omitted, Word assigns a default name to the command bar (such as "Custom 1").

Position   Optional Variant. The position of the new command bar. Can be one of the following MsoBarPosition constants.

Constant      Description
msoBarLeft, msoBarTop, msoBarRight, msoBarBottom      Indicate the left, top, right, and bottom coordinates of the new command bar
msoBarFloating      Indicates that the new command bar won't be docked
msoBarPopup      Indicates that the new command bar will be a shortcut menu
msoBarMenuBar      Indicates that the new command bar will replace the system menu bar on the Macintosh
MenuBar   Optional Variant. True to replace the active menu bar with the new command bar. The default value is False.
Temporary   Optional Variant. True to make the new command bar temporary. Temporary command bars are deleted when the container application is closed. The default value is False.


(I don't know how good that's gonna show up.)

That seems strange to me. Why are Temporary and MenuBar variants, when they are true and false values......Oh well.

I'm not sure how you make these controls do what you want, but it might be possible that if you close them or something (although I think they close by themselves) then maybe it would work.

Here is a workaround by using an error handler to change the name each time.
(Im not quite sure how I managed this one either because BarNum is declared as a string, but adding 1 to it makes it one larger. I didn't know Excel did that. I didn't know you could add numbers to a string, but it works here.

Try this code. It should work fine, but only in the Sheets event, not the workbook's event. It's probably not the most optimized way to go, but maybe it will get you closer.
All the error handler does is keep changing the name of the control until it finds one that hasen't been used yet.
Although I didn't try it, if you could make BarNum a Static, public, or private variable instead of resetting it to 1 each time, it would be better.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)

Dim x As CommandBar, y As Variant, z As Object, BarNum As String
    Cancel = True
   
    BarNum = 1
    On Error GoTo Errhandler

    Set x = CommandBars.Add(Name:="Custom" & BarNum, Position:=msoBarPopup, Temporary:=True)
    Set y = x.Controls.Add
    With y
        .FaceId = 26
        .Caption = "Analyze the data"
    End With
    Set z = x.Controls.Add
    With z
        .FaceId = 17
        .Caption = "Graph the data"
    End With
    x.ShowPopup 200, 200
   
Exit Sub

Errhandler:
BarNum = BarNum + 1
'Debug.Print BarNum

Resume

End Sub


I gotta go to bed now, but I'll see what's happenning with this tomorrow

Sorry I couldn't help more right off.

Cheers!
ahammar

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

vboukharCommented:
Hi,
May I come in?
I found the main mistakes:
1. Vartype of y has to be CommandBarControl
2. CommandBars.Add has to be used with explicit definition of object - Application
To avoid error on adding of existed popup menu I used error handling

Here is my sample:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
Dim x As CommandBar, y As CommandBarControl, z As Object
On Error GoTo nxtlbl
Set x = Application.CommandBars.Add("Custom", msoBarPopup)
Set y = x.Controls.Add
With y
    .FaceId = 26
    .Caption = "Analyze the data"
End With
Set z = x.Controls.Add
With z
    .FaceId = 17
    .Caption = "Graph the data"
End With
nxtlbl:
Set x = Application.CommandBars("Custom")
x.ShowPopup 200, 200
End Sub

Hope it helps
0
antratAuthor Commented:
Ahammer and vboukhar

Thanks for both of your inputs

ahammer, the last code you posted works as you said, It has really got me stumped though concerning it working the first time and never again I had exactly the same prob when I was playing around with it, I also cannot understand why "y" must be a variable. I had spent hours on this and gave up in frustration.

vboukhar, Sorry to say your code keeps going to "nxtlbl" each time I right click.

I'll leave this running over the next few days to see what sort of input I recieve. If no-one has the answers I'll give you the points Ahammer.

Happy 2000

antrat


0
ahammarCommented:
Hi antrat,
I haven't found out much more yet, but I did figure out that by using the error handler like in the last code I posted, it starts over again when you close and reopen Excel.
In other words, Excel remembers each command bar name everytime the code runs, so the command bar has to be given a different name every time. That's what the error handler does as you know. But when you close and reopen Excel, it starts over again with nameing the toolbar, and not where it left off. So, even though there probably is a better way, it shouldn't be to bad. I would guess that you wouldn't notice any slowdown the way it is unless you were to run the code a few thousand times without ever closeing Excel, and that's probably unlikely. If you were to make the BarNum variable public or static or something, then you would never notice any slowdown no matter how many times you run it.

But, I would do the same thing. Leave it open for awhile.
I imagine ture would come up with something if he comes by.

I may play with it some more if I have some time later.

Happy New Year!

Cheers!
ahammar


0
tureCommented:
antrat and all,

I have a few things to add to the discussion:

1. y and z should both be declared as CommandBarControl.

2. The procedure works better if you use Application.CommandBars instead of just CommandBars.

3. Try to delete the commandbar (with error handling turned on) before adding it.

4. Add a Cancel = True at the end of the sub, otherwise the default 'right-click-menu' will show for a short moment.

5. Actually, you don't need any variables at all for this. Use nested withs, as in my second version below.


One working version:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
  Dim x As CommandBar
  Dim y As CommandBarControl
  Dim z As CommandBarControl
 
  On Error Resume Next
  Application.CommandBars("Custom").Delete
  On Error Resume Next
 
  Set x = Application.CommandBars.Add("Custom", msoBarPopup)
   
  Set y = x.Controls.Add
  With y
      .FaceId = 26
      .Caption = "Analyze the data"
  End With
 
  Set z = x.Controls.Add
  With z
      .FaceId = 17
      .Caption = "Graph the data"
  End With
 
  x.ShowPopup 200, 200

  Cancel = True
End Sub


Preferred version:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
  On Error Resume Next
  Application.CommandBars("Custom").Delete
  On Error Resume Next
  With Application.CommandBars.Add("Custom", msoBarPopup)
    With .Controls.Add
      .FaceId = 26
      .Caption = "Analyze the data"
    End With
    With .Controls.Add
      .FaceId = 17
      .Caption = "Graph the data"
    End With
    .ShowPopup 200, 200
  End With
  Cancel = True
End Sub

Ture Magnusson
Karlstad, Sweden
0
tureCommented:
vboukhar,

When re-reading the thread, I see that you had already suggested the CommandBarControl data type and explicit declaraion of Application.CommandBars.
Sorry for repeating.

/Ture
0
antratAuthor Commented:
Hi all

Ture, I was sort of hoping you would stop by, your code seems to work just fine. I had tried declaring y and z as CommandBarControl and using Application.CommandBarControl but I still could not get it to work. Looks like it was because as ahammer had said it retains the name "Custom" and will not create another with the same name. What had me confused was the "Run time error: Object Variable or With block variable not set" so I tried everthing including "with" statements. I didn't even think about having to delete the old one.
Still learning.

Anyway being Xmas and all I think I would like to give you all 50 points, except ahammer of course because I may have a chance of passing him if I don't award him any points.





Just kidding :o)

I'll accept Tures' answer here as it will be better as a Previously asked question.

Once again thanks to all and happy 2000

antrat
0
tureCommented:
Thanks, antrat! You are such a nice guy.

An additional comment: Consider setting the 'Temporary' argument to True when creating the CommandBar. This will delete the CommandBar when you quit Excel.

Just change this:
  With Application.CommandBars.Add("Custom", msoBarPopup)

To this:
  With Application.CommandBars.Add("Custom", msoBarPopup, , True)

Ture Magnusson
Karlstad, Sweden
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
antratAuthor Commented:
Thanks

antrat
0
kodiakbearCommented:
Fixed the -1 problem on this question.
Escrow points corrected.

kb
Community Support Moderator
Experts Exchange
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.