Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Close without saving (97sr2, XP sp1)

    I want to close a workbook without saving.
    I have used
    ThisWorkbook.Saved = True 'prevent workbook save
    and
    ThisWorkbook.Close False.

    Both work fine UNTIL I select a particular worksheet and move the cursor. I have code behind the Worksheet_SelectionChange event. There is a list box on the sheet which is hidden. The code makes the list box visible and sets it beside the cell being edited. However, it does not matter what branch of the code runs, it still does not work.

    The behavior I get is a crash of Excel when I click a close button on a worksheet

    Here is the code behind the button

    Sub btnClose_click()
    Dim result As Integer

    'ThisWorkbook.Saved = True 'prevent workbook save

    result = MsgBox("Are you sure?", vbYesNo, "Close?")

    If result = vbYes Then
    ThisWorkbook.Close False
    'else do nothing
    End If

    End Sub


    The List box is on a sheet named "FieldInfo". Its data is on a worksheet named "Lookups"

    Here is the code on the "Field Info" sheet

    Option Explicit
    Option Base 1

    Const COLUMN_NO_COL = 1
    Const START_POS_COL = 2
    Const DATA_TYPE_COL = 3


    Private lbDataType As Shape


    Private Sub Worksheet_Activate()
    Set lbDataType = Me.Shapes("lbDataType")
    'set lbdatatype =
    End Sub


    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim v As Long

    If Target.Column = DATA_TYPE_COL And Target.Rows.Count = 1 _
    And Target.Columns.Count = 1 Then

    On Error GoTo eh

    v = Target.Value

    lbDataType.Top = Target.Top
    lbDataType.Left = Target.Offset(0, 1).Left
    lbDataType.ControlFormat.LinkedCell = Target.Address
    If v > 0 Then
    lbDataType.ControlFormat.Value = v
    Else
    lbDataType.ControlFormat.Value = 1
    End If
    lbDataType.Visible = True
    'MsgBox Selection.Type


    Else
    lbDataType.Visible = False
    End If

    Exit Sub
    eh:

    'put stuff here someday :-)
    End Sub




    'Returns an Array of Arrays matching the fieldInfo
    'in the Workbook.OpenText Function, see VBA help for
    'more info

    Public Property Get FieldInfo() As Variant
    Dim lngCurrentRow As Long
    Dim bolSentinal As Boolean
    Dim aryV() As Variant

    lngCurrentRow = 2 'start on the second row

    bolSentinal = True

    Do While bolSentinal
    ReDim Preserve aryV(lngCurrentRow - 1)
    aryV(lngCurrentRow - 1) = _
    Array(Cells(lngCurrentRow, START_POS_COL).Value, _
    Cells(lngCurrentRow, DATA_TYPE_COL).Value)

    lngCurrentRow = lngCurrentRow + 1

    If IsEmpty(Cells(lngCurrentRow, START_POS_COL)) Then
    bolSentinal = False
    End If

    Loop

    FieldInfo = aryV


    End Property

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Close without saving (97sr2, XP sp1)

    have you tried:

    Activeworkbook.Close SaveChanges:=False
    (though your btnclick code, works on my machine)

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close without saving (97sr2, XP sp1)

    I tried that too. I removed the "Always create backup" option as well.
    If I close from the file menu, every thing works. If I no not move the cursor on the "FieldInfo" sheet, everything, including the close macro works. If I attempt to close using the macro, After I have moved the cursor on the "FieldInfo" sheet, I crash. Even If I save the workbook from the file menu first.

    It is attached this time.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Close without saving (97sr2, XP sp1)

    The problem seems to be, that when you activate, "fieldinfo" you assign an object reference (with SET) to lbdatatype. You never release this and when it is closed by your macro, excel doesn't like the "open memory".

    To fix that add this to the fieldinfo sheet macros

    <pre>Private Sub Worksheet_Deactivate()
    Set lbDataType = Nothing
    End Sub
    </pre>


    This will release the SET when the sheet is deactivated

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close without saving (97sr2, XP sp1)

    Thanks. I knew that, but it is not what I was looking for! Releasing resources will go on my list of "things to check", at least until next time I forget :-)

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •