WorkBookBeforeClose with personal.xls

leaftye

New Member
Joined
Dec 13, 2005
Messages
21
This code is in a Class Module that's set up to receive events, and it does, but it only see's other workbooks closing and .xla files closing, but never itself (personal.xls).

Code:
Private Sub Monitor_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
  On Error Resume Next
  If Wb.Name = "personal.xls" Then
    With ThisWorkbook
      With .Sheets("Settings")
        Dim tbPosition As Integer
        tbPosition = Application.CommandBars("RobinsonToolbar").Position
        If .Range("ToolbarPosition").Value <> tbPosition Then .Range("ToolbarPosition").Value = tbPosition

      End With
      .Save
    End With
    If ThisWorkbook.Saved = False Then ThisWorkbook.Save
  End If
End Sub

I've also tried putting a copy in ThisWorkbook, but it doesn't ever fire. Can personal.xls see itself closing?


I currently have it set up like this, but every time I close ANY workbook, it saves personal.xls first. Why does it save personal.xls at all?
Code:
Private Sub Monitor_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
'  On Error Resume Next
'  If ActiveWorkbook.Name = ThisWorkbook.Name Then
    With ThisWorkbook
      With .Sheets("Settings")
        Dim tbPosition As Integer
        tbPosition = Application.CommandBars("RobinsonToolbar").Position
        If .Range("ToolbarPosition").Value <> tbPosition Then .Range("ToolbarPosition").Value = tbPosition

      End With
      '.Save
    End With
    If ThisWorkbook.Saved = False Then ThisWorkbook.Save
'  End If
End Sub
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

leaftye

New Member
Joined
Dec 13, 2005
Messages
21
I didn't quite post my question correctly the first time. Please offer any suggestions.
 

leaftye

New Member
Joined
Dec 13, 2005
Messages
21
Nevermind, I just changes personal.xls to personal.xls, and all is well.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,127
Messages
5,576,237
Members
412,709
Latest member
AD04
Top