Cell Toolbar Dilemma xl2013

Discussion in 'Excel Programming' started by nosliwgerg2@gmail.com, May 4, 2014.

  1. Guest

    I just upgraded to 8.1 / Office 2013 and am trying to adapt some of my old xl2003 programs. I've encountered a dilemma with the right click "Cell" popup toolbar. I want to supplant it with my own for ease of access, instead of going through the Addins menu on the Ribbon. Problems:

    1) If I create my own popup and call it using the Workbook_SheetBeforeRightClick event, setting the Cancel parameter to True, then an error state occurs. Specifically, if you delete a worksheet that is active thus forcing activation of another sheet, the mouse wheel isn't recognized and you can't close the workbook. If you select another worksheet through the UI and return then it's OK again. There does not appear to be a code workaround, e.g. selecting a different sheet before deleting the active sheet etc.

    2) If I instead add my controls to the Cell toolbar and use the same eventto process the controls, displaying mine and hiding the native controls, then an autosense "Paste Options:" control is added, and I can't prevent it.

    My take on this is that Excel tries to add the autosense control to the Cell toolbar. If it is not displayed then an error state is generated. The autosense addition happens AFTER the right click event is processed, and therefore, I cannot prevent it.

    I tried parsing the Cell toolbar controls with the intent of disabling the Paste Options control proactively. However, it is not listed.

    Your thoughts much appreciated. New to xl2013 and an amateur programmer.

    Greg
     
    , May 4, 2014
    #1
    1. Advertisements

  2. GS Guest

    > I just upgraded to 8.1 / Office 2013 and am trying to adapt some of
    > my old xl2003 programs. I've encountered a dilemma with the right
    > click "Cell" popup toolbar. I want to supplant it with my own for
    > ease of access, instead of going through the Addins menu on the
    > Ribbon. Problems:
    >
    > 1) If I create my own popup and call it using the
    > Workbook_SheetBeforeRightClick event, setting the Cancel parameter to
    > True, then an error state occurs. Specifically, if you delete a
    > worksheet that is active thus forcing activation of another sheet,
    > the mouse wheel isn't recognized and you can't close the workbook. If
    > you select another worksheet through the UI and return then it's OK
    > again. There does not appear to be a code workaround, e.g. selecting
    > a different sheet before deleting the active sheet etc.


    What does this have to do with your popup?
    >
    > 2) If I instead add my controls to the Cell toolbar and use the same
    > event to process the controls, displaying mine and hiding the native
    > controls, then an autosense "Paste Options:" control is added, and I
    > can't prevent it.
    >
    > My take on this is that Excel tries to add the autosense control to
    > the Cell toolbar. If it is not displayed then an error state is
    > generated. The autosense addition happens AFTER the right click event
    > is processed, and therefore, I cannot prevent it.
    >
    > I tried parsing the Cell toolbar controls with the intent of
    > disabling the Paste Options control proactively. However, it is not
    > listed.
    >
    > Your thoughts much appreciated. New to xl2013 and an amateur
    > programmer.
    >
    > Greg


    My right-click popups replace the 'Cells' popup just fine without any
    modification. (I'm still running early apps in both early/late
    versions, so both use the same code!)

    Show us the code for creating your popup AND the event code that
    handles it.

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 4, 2014
    #2
    1. Advertisements

  3. Guest

    Thanks Gary for responding.

    This assumes Windows 8.1 / Excel 2013. You need to have 3 worksheets named "Instructions", "Report Template" and "Settings". Also add a few more to act as the report sheets I want to delete. They can have default names like "Sheet1" etc. It doesn't matter.

    After pasting the below code:
    1) Close and reopen the workbook,
    2) Activate one of the regular worksheets (say "Sheet1"),
    3) Right click, then click the "Delete Report Worksheets" button.

    This will delete all but the Instructions worksheet, forcing this worksheetto become active. When this occurs, my mouse wheel is no longer recognizedand I cannot close the workbook. If I add a new sheet, the new sheet becomes active. If I return to the Instructions worksheet through the UI, everything is OK. Code follows.

    Paste the following to a standard module:

    Option Explicit
    Option Private Module
    Declare Function GetKeyState Lib "user32.dll" (ByVal nVirtKey As Long) As Integer
    Public Const ProgTitle As String = "Elastic Modulus Program"

    Sub MakeMainMenu()
    Const msg As String = "- Right click to access the program's main menu."& vbCr & _
    "- Hold down the <Shift> key to access the normal right click menu." & vbCr & _
    "- The new menu will be deleted when the workbook is closed."

    MsgBox msg, vbInformation, ProgTitle

    On Error Resume Next 'even though Temporary may not delete if crash
    Application.CommandBars(ProgTitle).Delete
    Err.Clear
    On Error GoTo 0

    'actual code adds several controls - for demo only one added
    With Application.CommandBars.Add(ProgTitle, msoBarPopup, Temporary:=True)
    With .Controls.Add()
    .FaceId = 1592
    .Caption = "Delete Report Worksheets"
    .OnAction = "DeleteReportWorksheets"
    End With
    End With
    End Sub

    Sub DeleteReportWorksheets()
    Dim ws As Excel.Worksheet, ws2 As Excel.Worksheet
    Dim msg As String

    msg = "WARNING: This will DELETE all worksheets except the 'Instructions', 'Report Template' and 'Settings' worksheets. " & _
    vbCr & vbCr & "Are you sure?"
    If MsgBox(msg, vbExclamation + vbYesNo + vbDefaultButton2, ProgTitle) = vbNo Then Exit Sub

    With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
    'test if a permanent sheet is visible
    Select Case ws.Name
    Case "Instruction", "Report Template", "Settings"
    If ws2 Is Nothing Then
    If ws.Visible Then
    Set ws2 = ws
    Exit For
    End If
    End If
    End Select
    Next
    'if no permanent sheet visible then make Instructions visible
    If ws2 Is Nothing Then
    Set ws2 = Sheets("Instructions")
    ws2.Visible = xlSheetVisible
    End If

    For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
    Case "Instructions", "Report Template", "Settings"
    'do nothing
    Case Else
    ws.Delete
    End Select
    Next
    .ScreenUpdating = True
    .DisplayAlerts = True
    End With

    Set ws = Nothing: Set ws2 = Nothing
    End Sub


    Then paste this to the ThisWorkbook class module:

    Option Explicit

    Private Sub Workbook_Open()
    Sheets("Report Template").Visible = xlSheetHidden
    Sheets("Settings").Visible = xlSheetHidden
    Sheets("Instructions").Visible = xlSheetVisible
    Call MakeMainMenu
    End Sub

    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal TargetAs Range, Cancel As Boolean)
    Dim b As Boolean
    If GetKeyState(vbKeyShift) >= 0 Then
    Cancel = True
    Application.CommandBars(ProgTitle).ShowPopup
    End If
    End Sub
     
    , May 5, 2014
    #3
  4. GS Guest

    I don't have 8.1/2013 to test this. I do have a user of one of my apps
    running it on 8.1/2013 without issue with sheet popup menus. These vary
    in menuitems as per sheet context when right-click occurs. (IOW,
    available choices vary)

    As for your code.., I don't see why it doesn't work so long as one of
    your permanent sheets is the activesheet when you delete the others.

    Here's how I'd do it...

    Sub DeleteReportSheets()
    Dim sMsg$, n&, bPermVisible As Boolean
    Const sPermSheets$ = "Instructions,Report Template,Settings"

    sMsg = "WARNING: This will DELETE all worksheets except the
    'Instructions', 'Report Template' and 'Settings' worksheets. " & _
    vbCr & vbCr & "Are you sure?"
    If MsgBox(sMsg, vbExclamation + vbYesNo + vbDefaultButton2,
    ProgTitle) = vbNo Then Exit Sub

    For n = 1 To ThisWorkbook.Sheets.Count
    'test if a permanent sheet is visible
    bPermVisible = Sheets(n).Visible
    If bPermVisible Then Sheets(n).Activate: Exit For
    Next 'n
    'if no permanent sheet visible then make Instructions visible
    If Not bPermVisible Then Sheets("Instructions").Visible = True

    GroupSheets sPermSheets, False, ThisWorkbook

    With Application
    .DisplayAlerts = False: ActiveWindow.SelectedSheets.Delete:
    ..DisplayAlerts = True
    End With
    End Sub 'DeleteReportSheets

    Public Sub GroupSheets(Sheetnames As String, _
    Optional bInGroup As Boolean = True, _
    Optional Wkb As Workbook)
    ' Groups sheets in Wkb based on whether Sheetnames
    ' are to be included or excluded in the grouping.
    ' Arg1 is a comma delimited string. (ie: "Sheet1,Sheet3")

    Dim Shts() As String, sz As String
    Dim i As Integer, wks As Worksheet, bNameIsIn As Boolean

    If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
    For Each wks In Wkb.Worksheets
    bNameIsIn = (InStr(Sheetnames, wks.Name) > 0)
    If bInGroup Then
    If bNameIsIn Then sz = wks.Name
    Else
    If bNameIsIn Then sz = "" Else sz = wks.Name
    End If
    If Not sz = "" Then '//build the array
    ReDim Preserve Shts(0 To i): Shts(i) = sz: i = i + 1
    End If
    Next
    Wkb.Worksheets(Shts).Select
    End Sub 'GroupSheets

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 5, 2014
    #4
  5. Guest

    Thanks again Gary for replying.

    I ran your code and, unfortunately, it does the same thing. After running it, the mouse wheel was not recognized and I could not close the workbook. Adding another sheet (which makes it active), then returning to the Instructions worksheet through the UI fixed it.

    I used a new workbook devoid of additional code. I commented out my versionof DeleteReportSheets and included MsgBox responses within your code to make sure it was fully executing. It was.

    I mentioned that I had already tried activating the Instructions worksheet before doing the deletions, and it didn't help. Also, as mentioned, if I don't create my own popup, just add my controls to the Cells popup, this doesn't happen. Just the annoying "Paste Options:" control gets added and I can't prevent it (gets added after the right click event is processed).

    So I guess all we can do is see if someone else running 8.1 / xl2013 confirms or denies the problem.

    Thanks again.

    Greg
     
    , May 5, 2014
    #5
  6. GS Guest

    > Thanks again Gary for replying.
    >
    > I ran your code and, unfortunately, it does the same thing. After
    > running it, the mouse wheel was not recognized and I could not close
    > the workbook. Adding another sheet (which makes it active), then
    > returning to the Instructions worksheet through the UI fixed it.
    >
    > I used a new workbook devoid of additional code. I commented out my
    > version of DeleteReportSheets and included MsgBox responses within
    > your code to make sure it was fully executing. It was.
    >
    > I mentioned that I had already tried activating the Instructions
    > worksheet before doing the deletions, and it didn't help. Also, as
    > mentioned, if I don't create my own popup, just add my controls to
    > the Cells popup, this doesn't happen. Just the annoying "Paste
    > Options:" control gets added and I can't prevent it (gets added after
    > the right click event is processed).
    >
    > So I guess all we can do is see if someone else running 8.1 / xl2013
    > confirms or denies the problem.
    >
    > Thanks again.
    >
    > Greg


    Sorry I couldn't help. Looks like I'll have to get myself 8.1/MSO2013
    so I can troubleshoot stuff myself.

    Also, in my code where the If bPermVisible line is I forgot to activate
    the sheet.

    I hope my version didn't offend! (I just couldn't see the point of
    looping the sheets collection twice in the same routine!)

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 5, 2014
    #6
  7. GS Guest

    If Not bPermVisible Then
    With Sheets("Instructions")
    .Visible = True:.Activate
    End With 'Sheets("Instructions")
    End If 'Not bPermVisible

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 5, 2014
    #7
  8. Guest

    Gary:

    Your help is still very much appreciated.

    FYI, a kludge, but one that works (on minimal testing), is to delete the worksheets indirectly calling:
    Application.OnTime Now + Timevalue("00:00:00"), "DeleteWorksheetsMain"

    The time value of "00:00:00" still worked and produced no noticeable delay.I apparently assumed correctly that this would allow dismissal of my custom popup before the offending action occurred.

    Of note, I found that the problem doesn't just apply to worksheet deletions, but also when you just change worksheets. I think it's actually the forced worksheet change that causes it when the sheets are deleted. I haven't the time because it's very late to triple check that observation, but that's my strong impression.

    All the best,

    Greg
     
    , May 6, 2014
    #8
  9. GS Guest

    > Gary:
    >
    > Your help is still very much appreciated.
    >
    > FYI, a kludge, but one that works (on minimal testing), is to delete
    > the worksheets indirectly calling: Application.OnTime Now +
    > Timevalue("00:00:00"), "DeleteWorksheetsMain"
    >
    > The time value of "00:00:00" still worked and produced no noticeable
    > delay. I apparently assumed correctly that this would allow dismissal
    > of my custom popup before the offending action occurred.
    >
    > Of note, I found that the problem doesn't just apply to worksheet
    > deletions, but also when you just change worksheets. I think it's
    > actually the forced worksheet change that causes it when the sheets
    > are deleted. I haven't the time because it's very late to triple
    > check that observation, but that's my strong impression.
    >
    > All the best,
    >
    > Greg


    As my reply suggests.., if you pre-activate one of the permanent sheets
    before deleting, no sheet changes occur as a result of the delete
    action.

    Another consideration is to look into any 'sheet' events that have
    other code that executes. If this is the case then you can mitigate
    problems as follows...

    <snip>
    With Application
    .DisplayAlerts = False: .EnableEvents = False: On Error Resume Next
    ActiveWindow.SelectedSheets.Delete
    .DisplayAlerts = True: .EnableEvents = True
    End With
    End Sub

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 6, 2014
    #9
  10. Guest

    Hi Gary:

    I think it's one of those things where you have to see it to believe it.

    I did what you suggest with my code before my first post and did so just now with yours. I even included a DoEvents statement plus a MsgBox so I couldwatch the Instructions sheet get activated BEFORE the deletions took place, then watched them get deleted. It still didn't work. I used a brand new workbook with no other code in it other than what I have posted. So there isno sheet event code. Previously, I not only disabled events but also used a public Boolean variable to ensure no sheet event code fired: "If Abort Then Exit Sub". I even tried commenting out all other event code.

    What we know:

    1) Worksheet deletion isn't necessary. Only changing worksheets.

    2) The problem only happens when the the custom popup is displayed throughthe Workbook_SheetBeforeRightClick event.

    3) Setting the Cancel parameter to True isn't required. In other words, westill have the problem if the Cell toolbar is delayed (appears after the custom popup is dismissed) instead of cancelled.

    4) Adding controls to the Cell toolbar and hiding the native controls avoids the problem but results in the unwanted Paste Options control.

    5) Creating a standard toolbar and accessing through the Addins menu avoids the problem.

    6) The problem can be fixed (or avoided) if the worksheet change or deletion code is run indirectly with Application.Ontime Now, "DeleteWorsheetsMain". Note that no delay appears necessary - I removed the "+ TimeValue" part..

    7) Clearing the clipboard doesn't help. I tried OpenClipboard, EmptyClipboard, CloseClipboard without success, hoping the Paste Options control wouldbe avoided. It just disables it (appears grayed).

    8) The Paste Options button is atypical in that it does not reside on the Cell toolbar but is added dynamically after processing of the right click event. This is my prime suspect because it logically is looking for a home and can't find it if the Cell toolbar is not displayed in response to a right click event.

    Cheers,

    Greg
     
    , May 7, 2014
    #10
  11. Guest

    I forgot to mention in Point 2 that Workbook level right click event isn't necessary. Using the worksheet level BeforeRightClick event doesn't avoid the problem.

    Greg
     
    , May 7, 2014
    #11
  12. GS Guest

    > I forgot to mention in Point 2 that Workbook level right click event
    > isn't necessary. Using the worksheet level BeforeRightClick event
    > doesn't avoid the problem.
    >
    > Greg


    I'm thinking that if your popup is activated by one of the delete
    sheets it might be the cause. I always put something like that in an
    events handler class. In your case I'd put it in ThisWorkbook (if you
    don't know how to set up an events class). The events class allows me
    to filter which sheets get which menuitems. I can't speak to sheet
    deletes because my popup doesn't do that in the app I mentioned.

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 7, 2014
    #12
  13. Guest

    Hi Gary:

    I'm not sure if I understand you, but the right click event that I capture is workbook level, not worksheet level. I use the Workbook_SheetBeforeRightClick event in the ThisWorkbook class module.

    FWIW, I decided to implement the kludge I mentioned, e.g. Application.OnTime Now, "DeleteWorksheets". I have it working fine. In theory my popup is dismissed before the deletions or activation changes take place. This avoids the problem.

    The issue likely originates with xl2013.

    Greg
     
    , May 7, 2014
    #13
  14. GS Guest

    > FWIW, I decided to implement the kludge I mentioned, e.g.
    > Application.OnTime Now, "DeleteWorksheets". I have it working fine.
    > In theory my popup is dismissed before the deletions or activation
    > changes take place. This avoids the problem.


    Glad you got it working for now...
    >
    > The issue likely originates with xl2013.


    Sounds like it but I think I'll run a test in 2010 to see how it goes
    there...

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 7, 2014
    #14
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Alex@JPCS

    Screen Update Dilemma

    Alex@JPCS, Nov 17, 2003, in forum: Excel Programming
    Replies:
    0
    Views:
    88
    Alex@JPCS
    Nov 17, 2003
  2. brio

    XL add-in dilemma

    brio, Feb 17, 2004, in forum: Excel Programming
    Replies:
    1
    Views:
    80
    Josh Sale
    Feb 17, 2004
  3. J. Cornor

    BeforeClose Dilemma

    J. Cornor, Jun 21, 2004, in forum: Excel Programming
    Replies:
    0
    Views:
    101
    J. Cornor
    Jun 21, 2004
  4. GCF

    Multi-user dilemma.

    GCF, Oct 12, 2004, in forum: Excel Programming
    Replies:
    1
    Views:
    129
    Harald Staff
    Oct 12, 2004
  5. Simon Lloyd

    Counting by colour dilemma!!

    Simon Lloyd, Nov 8, 2004, in forum: Excel Programming
    Replies:
    1
    Views:
    101
    crispbd
    Nov 8, 2004
  6. Simon Lloyd

    Counting by colour dilemma!!

    Simon Lloyd, Nov 8, 2004, in forum: Excel Programming
    Replies:
    1
    Views:
    125
    Dave Peterson
    Nov 9, 2004
  7. Simon Lloyd

    Counting by colour dilemma!!

    Simon Lloyd, Nov 9, 2004, in forum: Excel Programming
    Replies:
    1
    Views:
    124
    Dave Peterson
    Nov 10, 2004
  8. Simon Lloyd

    Counting by colour dilemma!!

    Simon Lloyd, Nov 10, 2004, in forum: Excel Programming
    Replies:
    1
    Views:
    126
    Dave Peterson
    Nov 11, 2004
Loading...