Cell Toolbar Dilemma xl2013

[nosliwgerg2]

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

 

[GS]

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

 

[nosliwgerg2]

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

 

[GS]

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

 

[nosliwgerg2]

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

 

[GS]

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]

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

 

[nosliwgerg2]

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

 

[GS]

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

 

[nosliwgerg2]

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

 

[nosliwgerg2]

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

 

[GS]

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

 

[nosliwgerg2]

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

 

[GS]

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