Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Toolbar Dilemma xl2013
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 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Toolbar Dilemma xl2013
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Toolbar Dilemma xl2013
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 worksheet to become active. When this occurs, my mouse wheel is no longer recognized and 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 Target As Range, Cancel As Boolean) Dim b As Boolean If GetKeyState(vbKeyShift) = 0 Then Cancel = True Application.CommandBars(ProgTitle).ShowPopup End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Toolbar Dilemma xl2013
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Toolbar Dilemma xl2013
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Toolbar Dilemma xl2013
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Toolbar Dilemma xl2013
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Toolbar Dilemma xl2013
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Toolbar Dilemma xl2013
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Toolbar Dilemma xl2013
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Toolbar Dilemma xl2013
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Toolbar Dilemma xl2013
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Toolbar Dilemma xl2013
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maddening Dilemma - Compare each cell within column a to each cell in column b and find unique matches | Excel Worksheet Functions | |||
What if Dilemma | Excel Discussion (Misc queries) | |||
BIG Dilemma.....HELP!! | Excel Worksheet Functions | |||
If Then Dilemma | Excel Worksheet Functions | |||
XL add-in dilemma | Excel Programming |