Any news from Chad Rothschiller at Microsoft?

On Tuesday, September 21, 2010 7:42 PM Clif McIrvin wrote:

New install of Office 2010 (after removing Office 2003) [company
decision outside my control]

"Document Index.xls" converted and saved as "Document Index.xlsm"

Initial testing shows that macro code is doing what I expect, until
"ThisWorkbook.Close", which (so far) always results in the

Microsoft Excel has encountered a problem and needs to close. We are
sorry for the inconvenience. (etc.)

crash.

I created a new, blank workbook, created a macro with
thisworkbook.close, and that worked just fine.

I deleted all my macro code, re-compiled, saved the workbook, re-opened
it, created a macro with thisworkbook.close, compiled, saved and tested
and it works.

I deleted the new macro code, pasted the original code (from NotePad),
compiled, saved, tested .. and it crashes every time.

Any suggestions?

The code follows:

Option Explicit

Const RO As String = "Read Only"
Const RW As String = "Read/Write"
Const ROFlag As String = "$H$1"

Const Description As Long = 3 ' Description Column
Const Link As Long = 1 ' Pathname Column = A
Const Flag As Long = 8 ' Link Type Column = H

Dim SelectionFlag As String ' R/W flag for selected row
Dim SelectionLink As String ' Link value for selected row

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)

' *** inserted for testing
ThisWorkbook.Close
Exit Sub
' *** end testing code

With Target
Select Case .Column
Case Description
If VarType(.Value) = vbString Then
' assume Link is a valid pathname
Cancel = True
' Check flag column for Empty, Read Only, Read/Write or
"else"
' Else = hyperlink, not workbook
' Empty uses Read Only Flag cell to open workbook
SelectionFlag = .EntireRow.Cells(Flag).Value
SelectionLink = .EntireRow.Cells(Link).Value
If Not IsEmpty(SelectionFlag) Then
Select Case UCase(SelectionFlag)
Case UCase(RO)
Workbooks.Open Filename:=SelectionLink, _
ReadOnly:=True

' *** this is the branch under test
Case UCase(RW)
Workbooks.Open Filename:=SelectionLink, _
ReadOnly:=False
' ***

Case Else 'hyperlink
ActiveWorkbook.FollowHyperlink _
Address:=SelectionLink, _
NewWindow:=True
Application.CommandBars("web").Visible = False
End Select
Else
Workbooks.Open Filename:=SelectionLink, _
ReadOnly:=(Range(ROFlag) = RO)
End If ' End process link code
If Cancel Then
' Cancel = True IFF link followed,
' so close this link document

' *** this is the line that fails ... at least, this is the last line
the debugger
' *** single step brought up before the crash pop-up

ThisWorkbook.Close ' prompt to save changes

' ***

End If ' End Close Workbook code
End If ' End test for vbString (assumed link) code
End Select ' End test for Link Column
End With
End Sub


' *** this sub works as expected
Private Sub Worksheet_SelectionChange(ByVal _
Target As Range)

With Target
Select Case .Address
Case ROFlag
' Toggle Read Only Control Flag in Worksheet
Select Case .Value
Case RO
.Value = RW
Case RW
.Value = RO
End Select
End Select
End With

End Sub


On Wednesday, September 22, 2010 12:36 PM Clif McIrvin wrote:

Status Update:

So far, I have tested BeforeDoubleClick, BeforeRightClick and
SelectionChange events.

ThisWorkbook.Close works in the SelectionChange event, but crashes in
either Click event:

Private Sub Worksheet_BeforeRightClick(ByVal _
Target As Range, Cancel As Boolean)
ThisWorkbook.Close
End Sub

always crashes in my testing.

Private Sub Worksheet_SelectionChange(ByVal _
Target As Range)
ThisWorkbook.Close
End Sub

always works.




--
Clif McIrvin

Change nomail.afraid.org to gmail.com to reply by email.
(nomail.afraid.org has been set up specifically for
use in usenet. Feel free to use it yourself.)


On Thursday, September 23, 2010 6:54 AM Jim Rech wrote:

Target As Range, Cancel As Boolean)
ThisWorkbook.Close
End Sub


Same here. If you want a workaround I'd put in a delay, and kill the popup
menu:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True
Application.OnTime Now, "CloseMe"
End Sub

and in a standard module:

Sub CloseMe()
ThisWorkbook.Close
End Sub


On Thursday, September 23, 2010 9:55 AM Clif McIrvin wrote:

Nice workaround. I had never noticed the OnTime method before, Thanks!!

I did get a response from Chad Rothschiller at Microsoft; they are
looking into it.

Clif




--
Clif McIrvin

Change nomail.afraid.org to gmail.com to reply by email.
(nomail.afraid.org has been set up specifically for
use in usenet. Feel free to use it yourself.)


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Create List Add/Edit Form Web Part With Custom Toolbar and Attachments Option
http://www.eggheadcafe.com/tutorials...ts-option.aspx