Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi guys,

    I have a right click menu popup that I would like to keep simple, it works on any other worksheet except the sheet that I would like it to work on, actually I only have one sheet, and a chart sheet. It does work when I copy and paste in a new workbook and or even a new sheet within the workbook I am working on.

    Is there a reason why I can't get it work on Sheet1?


    Thanks,
    Darryl


    Option Explicit
    Private Sub Workbook_Deactivate()
    On Error Resume Next
    With Application
    .CommandBars("Cell").Controls("2010").Delete
    End With
    On Error GoTo 0
    End Sub
    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim cBut As CommandBarButton


    On Error Resume Next
    With Application
    .CommandBars("Cell").Controls("2010").Delete
    Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True)
    End With
    With cBut
    .Caption = "2010"
    .Style = msoButtonCaption
    .OnAction = "k"
    End With
    On Error GoTo 0

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    This code should go into the ThisWorkbook module of the workbook, not into any worksheet module.

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    This code should go into the ThisWorkbook module of the workbook, not into any worksheet module.
    Hi Hans,

    I did that, and I am confused(normal), If I have a second worksheet it works, but not on the sheet that I would like it to work on...I thought maybe there was an option that I was unaware.

    Thanks,
    Darryl.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you post a small sample workbook that demonstrates the problem?


  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Could you post a small sample workbook that demonstrates the problem?

    Hans,

    Kind of Difficult can I just send it to you? the workbook?

    Thanks,
    Darryl.

  6. #6
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HUHandEH View Post
    Hans,

    Thanks, I figured it out, what a blast! I had the sheet in page break Preview, is the commandbar object only available in normal view?

    Thanks,
    Darryl.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    This is very confusing. There are actually three different right-click menus named Cell - one for normal view, another one for page break view and a third one for Excel macro sheets (which aren't used any more).

    To apply a change to all of them, you need to use a loop, for example

    Code:
    Dim cbr As CommandBar
    For Each cbr In Application.CommandBars
      If cbr.Name = "Cell" Then
        cbr.Controls("2010").Delete
        Set cBut = cbr.Controls.Add(Temporary:=True)
    	With cBut
    	...
    	End With
      End If
    Next cbr

  8. #8
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    An alternative is to simply create and display your own menu within the sheet's right-click event and cancel the default action. Then you don't have to worry about resetting the Cell commandbars.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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