C
Clif McIrvin
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
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