ThisWorkbook.Close crashing Excel (2010)

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
 
C

Clif McIrvin

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 said:
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
Click to expand...



--
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.)
 
J

Jim Rech

Private Sub Worksheet_BeforeRightClick(ByVal _
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


Clif McIrvin said:
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 said:
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
Click to expand...



--
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.)
Click to expand...
 
C

Clif McIrvin

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

Jim Rech said:
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


Clif McIrvin said:
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 said:
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
Click to expand...



--
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.)
Click to expand...
Click to expand...



--
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.)
 
M

Michel D

Any news from Chad Rothschiller at Microsoft?
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
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.)
Click to expand...
Click to expand...
 
C

Clif McIrvin

Michel D said:
Any news from Chad Rothschiller at Microsoft?
Click to expand...


Well, yes and no.
Turns out this bug goes back as far as Excel
Click to expand...
2003 at least, maybe more, and we haven't fixed it yet (probably
Click to expand...
because we're not sure anyone cares for "real" reasons).
Click to expand...


I'll inquire and see if I can learn if they assigned a tracking ID or
anything; I'm simply using the workaround suggested by Jim Rech (below,
also ) of using
Application.OnTime to move the ThisWorkbook.Close command out of the
running procedure.

I have encountered two cases where Excel crashes, and Jim's workaround
succeeds:
-- DoubleClick or RightClick event code executing ThisWorkbook.Close
-- DoubleClick or RightClick event code executing sheetObject.Activate,
where the sheetObject is in a different workbook.

I found it interesting that though they said the bug exists in xl2003 my
code ran fine then.

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.)
 
M

Michel Demers

Hi Clif,

You have me in a loop. Perhaps it is because I'm more of a beginner in
VBA, but I can't find a way to integrate your solution. So this is my
situation...

When running a simple code such as
From Workbook “A.xls”

Sub Hello()
Workbooks.Open Filename:="d:\B.xls", UpdateLinks:=0,
ReadOnly:=True
End Sub

And From “B.xls”

Sub Byebye()
ThisWorkbook.Close False
End Sub

The “Hello” code works just fine, and when I run the “Byebye” code Via
the VBA Editor either using F5 or F8, it works fine too. But I have
an “Option Button” on a sheet that has the “byebye” macro assigned to
it in order to close the “B.xls” workbook.

Now here is a twist... I've replace the "option button" with a regular
grey 'macro button" and assigned the macro to it. It works fine ONE
TIME ONLY. If I run it a second time, it crashes. Now is that
weird ???

So can you offer me a more "step-by-step" as to how to resolve this
work around solution !!!

Curent system setup
WindowsXP SP3
Office XP (2002) SP 3
And also Office 2010 version 14.0.4760.1000 (32 bits)

Your help would be so appreciated

Best regards,

Michel
 
C

Clif McIrvin

(I changed the subject back to it's original to keep the topic "up
front", so to speak. "I have the same problem too" doesn't really
supply useful information, especially for someone who sees the message
standing on its own, without the benefit of the context of the
preceeding messages in the thread. In fact, I'd never have read your
first post if my newsreader hadn't connected it to the original thread
in spite of your changing the subject line.)

Michel, you use a couple terms that I'm not sure how to interpret. When
you say "Option Button" (in quotes), and "regular grey macro button" I'm
not at all certain what you mean.

Let's clarify: I understand that your sub Hello is in a standard module
of workbook "a.xls" and Byebye is in a standard module of workbook
"b.xls" (by standard module I mean a code module that the VBE project
explorer window shows under the heading "Modules", not "Excel Objects",
or "Forms".)

To integrate Jim Rech's solution, add another subroutine to the standard
module in b.xls:

Sub GoByebye()
Application.OnTime Now, "Byebye"
End Sub

and change your macro button to point to GoByebye instead of Byebye.

HTH!

Clif

Hi Clif,

You have me in a loop. Perhaps it is because I'm more of a beginner in
VBA, but I can't find a way to integrate your solution. So this is my
situation...

When running a simple code such as
From Workbook “A.xls”

Sub Hello()
Workbooks.Open Filename:="d:\B.xls", UpdateLinks:=0,
ReadOnly:=True
End Sub

And From “B.xls”

Sub Byebye()
ThisWorkbook.Close False
End Sub

The “Hello” code works just fine, and when I run the “Byebye” code Via
the VBA Editor either using F5 or F8, it works fine too. But I have
an “Option Button” on a sheet that has the “byebye” macro assigned to
it in order to close the “B.xls” workbook.

Now here is a twist... I've replace the "option button" with a regular
grey 'macro button" and assigned the macro to it. It works fine ONE
TIME ONLY. If I run it a second time, it crashes. Now is that
weird ???

So can you offer me a more "step-by-step" as to how to resolve this
work around solution !!!

Curent system setup
WindowsXP SP3
Office XP (2002) SP 3
And also Office 2010 version 14.0.4760.1000 (32 bits)

Your help would be so appreciated

Best regards,

Michel



Well, yes and no.


I'll inquire and see if I can learn if they assigned a tracking ID or
anything; I'm simply using the workaround suggested by Jim Rech
(below,
alsoof using
Application.OnTime to move the ThisWorkbook.Close command out of the
running procedure.

I have encountered two cases where Excel crashes, and Jim's workaround
succeeds:
-- DoubleClick or RightClick event code executing ThisWorkbook.Close
-- DoubleClick or RightClick event code executing
sheetObject.Activate,
where the sheetObject is in a different workbook.

I found it interesting that though they said the bug exists in xl2003
my
code ran fine then.

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.)
Click to expand...




--
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.)
 
M

Michel D

Hi Clif,

If my code is...
Sub byebye()
Activeworkbook.close False
End Sub

And that I have an Option button on a sheet that is linked to this macro, I don't understand how to integrate this workaround into my workbbook using the OnTime Now option.

Also, I need to point out that I did some testing and found that if I link the code to a standard grey macro button, It works fine BUT just one time. The second time I try to run the code it crashes.

NOTE: My workbook in question was created in EXCEL XP. and now with EXCEL 2010, It crashes.

Regards,

Michel



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
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.)
Click to expand...
Click to expand...
 
C

Clif McIrvin

Michel D said:
Hi Clif,

If my code is...
Sub byebye()
Activeworkbook.close False
End Sub

And that I have an Option button on a sheet that is linked to this
macro, I don't understand how to integrate this workaround into my
workbbook using the OnTime Now option.
Click to expand...

Did you see my reply on 10/16 evening explaining the mechanism to
integrate the workaround?
Also, I need to point out that I did some testing and found that if I
link the code to a standard grey macro button, It works fine BUT just
one time. The second time I try to run the code it crashes.
Click to expand...

It would help us help you if you could explain more about what you are
wishing to accomplish. Your explanation so far gives us a bit about
"what" you are doing but doesn't give any insight about "why". It may
be that a different approach would serve you better.

For instance: I have a workbook that is nothing more than a list of
other workbooks that I occasionally use. I found that it works well for
me to create a toolbar macro to open my cross-refrence workbook, then
double-click on the workbook I want to open rather than using Windows
Explorer, or File Open. This cross-reference workbook contains
Double-Click event code to open the workbook of interest, then close
itself.

So .. I have code running within a workbook that opens a different
workbook, then closes itself.

From your description (when working in workbook A the user opens
workbook B, then later clicks a button to close workbook B) I wonder if

Workbooks("b.xls").Close False

(or prehaps:)

Dim strBookName as String

strBookName = "b.xls"

Workbooks(xtrBookName).Close False

would work better for you.
NOTE: My workbook in question was created in EXCEL XP. and now with
EXCEL 2010, It crashes.
Click to expand...

For me, it didn't matter if I used a workbook created in xl2003 or
xl2010, the code worked fine in xl2003 and always crashed in xl2010.


HTH!

--
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.)
 
C

Clif McIrvin

RePost ... it seems that OP is not seeing my replies ... on
investigation it appears that OP is posting via eggheadcafe -- and my
replies aren't showing up in the original thread. ???!@*

Clif

(I changed the subject back to it's original to keep the topic "up
front", so to speak. "I have the same problem too" doesn't really
supply useful information, especially for someone who sees the message
standing on its own, without the benefit of the context of the
preceeding messages in the thread. In fact, I'd never have read your
first post if my newsreader hadn't connected it to the original thread
in spite of your changing the subject line.)

Michel, you use a couple terms that I'm not sure how to interpret. When
you say "Option Button" (in quotes), and "regular grey macro button" I'm
not at all certain what you mean.

Let's clarify: I understand that your sub Hello is in a standard module
of workbook "a.xls" and Byebye is in a standard module of workbook
"b.xls" (by standard module I mean a code module that the VBE project
explorer window shows under the heading "Modules", not "Excel Objects",
or "Forms".)

To integrate Jim Rech's solution, add another subroutine to the standard
module in b.xls:

Sub GoByebye()
Application.OnTime Now, "Byebye"
End Sub

and change your macro button to point to GoByebye instead of Byebye.

HTH!

Clif

Hi Clif,

You have me in a loop. Perhaps it is because I'm more of a beginner in
VBA, but I can't find a way to integrate your solution. So this is my
situation...

When running a simple code such as
From Workbook "A.xls"

Sub Hello()
Workbooks.Open Filename:="d:\B.xls", UpdateLinks:=0,
ReadOnly:=True
End Sub

And From "B.xls"

Sub Byebye()
ThisWorkbook.Close False
End Sub

The "Hello" code works just fine, and when I run the "Byebye" code Via
the VBA Editor either using F5 or F8, it works fine too. But I have
an "Option Button" on a sheet that has the "byebye" macro assigned to
it in order to close the "B.xls" workbook.

Now here is a twist... I've replace the "option button" with a regular
grey 'macro button" and assigned the macro to it. It works fine ONE
TIME ONLY. If I run it a second time, it crashes. Now is that
weird ???

So can you offer me a more "step-by-step" as to how to resolve this
work around solution !!!

Curent system setup
WindowsXP SP3
Office XP (2002) SP 3
And also Office 2010 version 14.0.4760.1000 (32 bits)

Your help would be so appreciated

Best regards,

Michel



Well, yes and no.


I'll inquire and see if I can learn if they assigned a tracking ID or
anything; I'm simply using the workaround suggested by Jim Rech
(below,
alsoof using
Application.OnTime to move the ThisWorkbook.Close command out of the
running procedure.

I have encountered two cases where Excel crashes, and Jim's workaround
succeeds:
-- DoubleClick or RightClick event code executing ThisWorkbook.Close
-- DoubleClick or RightClick event code executing
sheetObject.Activate,
where the sheetObject is in a different workbook.

I found it interesting that though they said the bug exists in xl2003
my
code ran fine then.

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.)
Click to expand...




--
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.)
 
M

Michel Demers

RePost ... it seems that OP is not seeing my replies ... on
investigation it appears that OP is posting via eggheadcafe -- and my
replies aren't showing up in the original thread. ???!@*

Clif

(I changed the subject back to it's original to keep the topic "up
front", so to speak.  "I have the same problem too" doesn't really
supply useful information, especially for someone who sees the message
standing on its own, without the benefit of the context of the
preceeding messages in the thread. In fact, I'd never have read your
first post if my newsreader hadn't connected it to the original thread
in spite of your changing the subject line.)

Michel, you use a couple terms that I'm not sure how to interpret. When
you say "Option Button" (in quotes), and "regular grey macro button" I'm
not at all certain what you mean.

Let's clarify:  I understand that your sub Hello is in a standard module
of workbook "a.xls" and Byebye is in a standard module of workbook
"b.xls" (by standard module I mean a code module that the VBE project
explorer window shows under the heading "Modules", not "ExcelObjects",
or "Forms".)

To integrate Jim Rech's solution, add another subroutine to the standard
module in b.xls:

Sub GoByebye()
    Application.OnTime Now, "Byebye"
End Sub

and change your macro button to point to GoByebye instead of Byebye.

HTH!

Clif


Hi Clif,

You have me in a loop. Perhaps it is because I'm more of a beginner in
VBA, but I can't find a way to integrate your solution.  So this is my
situation...

When running a simple code such as
From Workbook "A.xls"

Sub Hello()
    Workbooks.Open Filename:="d:\B.xls", UpdateLinks:=0,
ReadOnly:=True
End Sub

And From "B.xls"

Sub Byebye()ThisWorkbook.CloseFalse
End Sub

The "Hello" code works just fine, and when I run the "Byebye" code Via
the VBA Editor either using F5 or F8, it works fine too.  But I have
an "Option Button" on a sheet that has the "byebye" macro assigned to
it in order to close the "B.xls" workbook.

Now here is a twist... I've replace the "option button" with a regular
grey 'macro button" and assigned the macro to it.  It works fine ONE
TIME ONLY. If I run it a second time, it crashes. Now is that
weird ???

So can you offer me a more "step-by-step" as to how to resolve this
work around solution !!!

Curent system setup
WindowsXP SP3
Office XP (2002) SP 3
And also Office2010version 14.0.4760.1000 (32 bits)

Your help would be so appreciated

Best regards,

Michel







--
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.)- Hide quoted text -

- Show quoted text -
Click to expand...

Well Clif,

The workaround seems to work. Thank you Clif for your patience...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Automatically copy with save? 8
Excel is Ignoring ThisWorkbook.Save event after Thisworkbook.Close call 5
Save As and new workbook new name used in existing code 8
Shapes in 2010 1
Disable Cut/Copy/Paste in Excel 2010 0
Sound repeats unending until closing workbook 2
Can't delete sheets 8
VBA CreateNames (ignores last 5 rows?) 6

Top