Cell Toolbar Dilemma xl2013
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 could watch 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 is no 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 through the Workbook_SheetBeforeRightClick event.
3) Setting the Cancel parameter to True isn't required. In other words, we still 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 would be 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
|