Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Right click commandbars (2000)

    Hi all,

    Have just realized the power and the outright coolness of a customized right click menu. The following code creates a right click popup menu that is displayed when right clicking a cell in the sheet that is unprotected. My question is where do I put the macro for the "OnAction" - in the sheet itself or in a module? Also, how would I perform action on the "Target" using this menu? Do I need to pass "Target" to a sub if it is outside the worksheet or will Excel still remember what "Target" is?

    I am trying to either change the colorindex of the cell, or the value, I really haven't decided yet.

    Here is the code:

    <font color=blue>Create the toolbar and eliminate the normal right click menu</font color=blue>
    <pre>Private Sub Worksheet_Activate()
    CommandBars("Cell").Enabled = False
    Set myBar = CommandBars _
    .Add(Name:="CC1Bar", Position:=msoBarPopup, Temporary:=False)
    With myBar
    .Controls.Add Type:=msoControlButton
    .Controls.Add Type:=msoControlButton
    .Controls.Add Type:=msoControlButton
    .Controls.Add Type:=msoControlButton
    End With
    With CommandBars("CC1Bar").Controls(1)
    .Caption = "Mark as Verified"
    .OnAction = ""
    End With
    With CommandBars("CC1Bar").Controls(2)
    .Caption = "Mark as Delayed"
    .OnAction = ""
    End With
    With CommandBars("CC1Bar").Controls(3)
    .Caption = "Mark as Missing"
    .OnAction = ""
    End With
    With CommandBars("CC1Bar").Controls(4)
    .Caption = "Mark as Verified but Inaccurate"
    .OnAction = ""
    End With
    End Sub
    </pre>


    <font color=blue>Call the toolbar if the cell is unprotected</font color=blue>
    <pre>Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Locked = False Then
    CommandBars("CC1Bar").ShowPopup
    End If
    End Sub
    </pre>


    <font color=blue>Delete the toolbar and restore the normal right click menu</font color=blue>
    <pre>Private Sub Worksheet_Deactivate()
    CommandBars("CC1Bar").Delete
    CommandBars("Cell").Enabled = True
    Set myBar = Nothing
    End Sub
    </pre>


  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    If you put the 'onaction' routines in a general module, then you can make your target public by setting it equal to a range variable that was declared public.

    Declare a variable e.g. dummy as public in the general module and

    <pre>Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Locked = False Then
    CommandBars("CC1Bar").ShowPopup
    Set Dummy = Target
    End If
    End Sub
    </pre>


  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    Thanks Hans,

    Will try that. I am thinking that using this approach I could perform an action within another sheet, even though my popup menu is not in that sheet.

    Thanks,

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    Well, evidently, I am not grasping how to call the sub.

    Here is what I have (I backed off a bit from my original post in order to find out why the sub is not being called, or does not appear to be called):
    <font color=blue>Create the bar and assign caption and action</font color=blue>

    <pre>Private Sub Worksheet_Activate()
    CommandBars("Cell").Enabled = False
    Set myBar = CommandBars _
    .Add(Name:="CC1Bar", Position:=msoBarPopup, Temporary:=False)
    With CommandBars("CC1Bar")
    .Controls.Add Type:=msoControlButton
    End With
    With CommandBars("CC1Bar").Controls(1)
    .Caption = "Mark as Verified"
    .OnAction = "MarkVerified"
    End With
    End Sub</pre>


    <font color=blue>Call the bar if cell is not locked</font color=blue>
    <pre>Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Locked = False Then
    CommandBars("CC1Bar").ShowPopup
    End If
    End Sub
    </pre>


    <font color=blue>Delete the bar after switching sheets</font color=blue>
    <pre>Private Sub Worksheet_Deactivate()
    CommandBars("CC1Bar").Delete
    CommandBars("Cell").Enabled = True
    Set myBar = Nothing
    End Sub
    </pre>

    <font color=blue>Define sub to be called</font color=blue>
    <pre>Private Sub MarkVerified()
    MsgBox "This is a Test"
    End Sub
    </pre>


  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    Mike,

    Add a general module to your project and add the code for the last sub in that module, not as private. So, like this,
    <pre>Sub MarkVerified()
    MsgBox "This is a Test"
    End Sub
    </pre>


    This should work.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    Thanks Hans, that works perfectly! <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    Sorry Hans, another question? For one of the right click buttons, I put a msoControlEdit. It has a little box that I can type an entry in while the popup bar is showing. How do I refer to the contents of that edit box?

    I tried .value but I do not think there is any such thing for a control. What I have is this:

    <pre>Goal1.Value = CommandBars("CC1").Controls(3).<font color=red>???</font color=red>
    </pre>


    Thanks,

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    Sorry.... not straightforward, in other words, I don't know for the moment. I tried 'parameter', 'tag', 'tooltiptext', 'descriptiontext', no avail ...

    If I find it, I'll come back to you.

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    Hi Mike

    It seems that it MAY be a.k.a a MaskEdBox and it accepts the .Change event; rather than the usual .OnAction event.

    HTH
    Gre

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    Hi Unka,

    It looks like a textbox, but in the drop down list there is no .change event, there is a .onaction event. Where would I put a reference to the .change event for that? It almost looks like it should have the same properties as a textbox in a userform.

    Thanks,

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    Mike

    Having looked again, there does seem to be something strange with the Object Model. You could any of 22 or so Controls in and still have the same problem: stuck with OnAction. There must be something better than the messy option of using the ShortcutMenu control to launch a "real" input box and read the value from there. FWIW: using Numbers for Types makes the code run slightly quicker in both 97 & 2000: msoControlButton is 1; msoControlEdit is 2 etc. HTH
    Gre

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    Maybe it's a reference issue. I'll poke around some of the references, and see if that changes anything. I would think that if controlbutton is there, then the others mentioned in the help file would be there too, but I can't see anyway to return or otherwise work with it.

    I forgot about the numbers - thanks. A little while ago I caught my self writing a for-next loop instead of
    <pre>Range("A1:a335").Font.ColorIndex=12</pre>

    <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

    Thanks,

  13. #13
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    I'm not sure if you've got this sorted but the following code works for me in 2k: (note that there is nothing else in custom 1 for this to work without changes)

    Sub create_textfield()
    Set myBar = CommandBars("Custom 1")
    myBar.Visible = True
    Set newText = myBar.Controls.Add(Type:=msoControlEdit)
    End Sub

    Sub anothertest()
    CommandBars("Custom 1").Controls(1).OnAction = "YetanotherTest"
    End Sub

    Sub yetanothertest()
    MsgBox CommandBars("Custom 1").Controls(1).Text
    End Sub


    note that you can call yetanothertest independantly as well as from the onaction. Does this do what you want?

  14. #14
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    Thanks Brooke,

    I guess what had me snookered was the fact that "Text" is not listed in the drop down as a choice for the command bar control.

  15. #15
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right click commandbars (2000)

    No problems. It got me for a while as well - until I just went at it <img src=/S/bash.gif border=0 alt=bash width=35 height=39> style and hey presto.

Page 1 of 2 12 LastLast

Posting Permissions

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