Run Macro only when Saved but not Closed (different from when Saved and Closed)

[focuswiz]

I am working on a technique that appears to exploit either an intended or unintended feature of Excel. Specifically, I have found that I can enlarge the width of a dropdown validation list in a narrow column without first enlarging its associated column. This exploits an odd habit that Excel has of saving the width of the last activated cell with such validation on that sheet when the workbook is saved.

As a result, I want to activate a wider cell (with a dropdown validation list) when the file is saved, but I do not want to leave that cell activated (and potentially wider than normal) when the workbook is not closed.

For example,

• If the workbook is closed and saved, I want to activate a cell and possibly increase the width of its column and then save the spreadsheet. I will reset the size again in the open event and return to some default cell.
• If the workbook is closed but not saved, I want to do nothing.
• If the workbook is saved but not closed, I want to save the location of the currently active cell, activate a different cell and possibly increase the width of its column before the save but then reset the size again and then reactivate the originally active cell since I want to not disrupt the use of the sheet.

Is there a way to do this? Is there a way to detect that the save was not associated with a close event or some event that is triggered after a save if the workbook is still open?

Not sure I have even partially explained what I need, but I hope someone can interpret this and help.

Basically, I want to do some things when the worksheet is saved and closed and then undo some of that when the worksheet is reopened. If the save is not followed by a close, I want to do the same things and then undo them as if the worksheet has been reopened. In other words, if there was an easy way to force the sheet to always close and then reopen when "Save" is invoked, it would be messy (since I would likely have to store the last activated cell somewhere) but would probably work.

I guess I am looking for a way to test if the close is not going to happen or that the save has occurred but the spreadsheet has not been closed so that I can invoke a routine to undo the changes made prior to the save (probably with screen updating off so that the user doesn't see all the gyrations).

Thanks for any suggestions. I am sure that once I figure this out, I will realize how trivial it is, but I have no clue right now and cannot think of any straightforward method.

 

[PatOBrien198]

In the "ThisWorkbook" object (similar to where you'll find the Workbook_Open event, you'll find
Workbook_BeforeClose and Workbook_BeforeSave events.

Option Explicit
Dim AboutToClose As Boolean
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Me.Saved = False Then
        AboutToClose = True
        ' run your closing routines
    End If
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If AboutToClose Then
        ' do something
    else
         ' do your save only routine
    End If
    AboutToClose = False
End Sub

You'll have to try out the logic here to get the appropriate routines to fire in your various situations...

 

[focuswiz]

Thank you.

I will give it a try tonight, but that looks promising. lime

 

[focuswiz]

Not sure how "lime" got in my last post (from my phone), but I want to thank PatObrien198 again for clueing me in on this technique. I think I have it working. This was an interesting road to travel.

Some background...
I had a one-character field that I wanted to better control, so I wanted the end user to choose a literal from a validation list rather than remember which code to use for reconciliation. The literal was something like "Included in Report X but not in Report Y." I would then use a Worksheet_Change event to replace this literal with the one-character code.

The resulting dropdowns were tiny and only included an insignificant number of characters, so I wanted to change this. Techniques to accomplish this abound on the internet and nearly all of them include making the column wider during a Worksheet_SelectionChange event. This works by popping the column wider and then shrinking it down again later. In my attempt, I made it wider when the applicable cells were selected and then shrank it back when an inapplicable cell was selected or the value was replaced with the one-character value.

The above worked with a bunch of "flashy" (not in any positive sense) screens that were changing as you tabbed in and out of the applicable fields. I decided that I really did not like this technique.

While closing out the dozens of IE Tabs I had opened while searching for techniques, I came across this:
http://www.contextures.com/xlDataValWidth.html
and this:
https://superuser.com/questions/752...width-be-controlled-in-excel-2007-and-greater

These were interesting since they were describing the opposite problem from the one I had (mine was that the dropdown is too narrow and theirs was that it was too wide). Dan Henderson's reply in the second url gave me a clue where he suggested that to get rid of the extra width, you would:

Activate a cell in the narrowest column with drop-down validation (on every worksheet that has them), then save the workbook.

Unfortunately, he did not document what kind of steps he took to achieve that.

At that point, my goal became to activate a cell with a much wider width before saving the workbook. Hence the original post in this thread. I wanted to activate an appropriate cell before saving the file. A few manual tests seemed to confirm that this would work, but I wanted something more automatic. The way I decided to do this was to temporarily increase the width of the column with the one-character code before saving it. This meant that I could save it wide and then set it smaller on the open event. However, if I stayed in the workbook after the save, I would want to also set it smaller again if the workbook were not closed. Hence, my questions about how to tell if the workbook is saved and closed versus just saved.

This became quite clumsy when I realized that I had to include code to cancel the save in order to play with things but only when not closing the file. Of course, if I canceled the save and was trying to save the file, I needed to still save the file so I then needed to replace the save (and SaveAs) with my own code. This was obviously a bear to test since if I coded it wrong, I would not necessarily save my file and I could easily end up with events not firing if I abended in the wrong place in the code.

I think I got something to work. Macros need to be enabled in order for it to work, but It does appear better than the flashing screen every time I tabbed into one of the fields of interest. I probably could disable screen updating at the opening of the screen, but that width change is hardly noticeable. I also had not realized that there was also an "after save" event that could potentially be used for part of this solution. However, once I got this somewhat working, I was not too keen on making more changes.

If anyone has any alternative ideas for displaying a dropdownlist that is significantly wider than the column to which it relates, I would appreciate it even if it means discarding the code already written. Also, any criticisms of my code are welcome. I have had no training in this area and I just hack at things until they work. I often wish I knew someone who does this stuff for real.

Here is the code that replaces the verbose description with a code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strDecodeValue As String
    Dim strEnteredValue As String
    If Not Intersect(Target, Range("A2:A50")) Is Nothing Then
        strEnteredValue = Target.Value
        If Not Trim(strEnteredValue & vbNullString) = vbNullString Then
            strDecodeValue = Application.VLookup(strEnteredValue, Sheets("Validation").Range("Decodes"), 2, False)
            If Not IsError(strDecodeValue) Then
                Application.EnableEvents = False
                Target.Value = strDecodeValue
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub

Here is what is coded for the workbook events:

Option Explicit
Dim bAboutToClose As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If ThisWorkbook.Saved = False Then
        bAboutToClose = True
    End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim rngOldActiveCell As Range
    Dim varFileName As Variant
    Dim strFileName As String
    Dim strFileFormat As String
    Dim strFileFilter As String
    Dim strTitle As String
    Dim strDirectory As String
    Dim strDocuments As String
    Dim lFilterIndex As Long
    Dim lResponse As Long
    Dim iStandardWidth As Integer
    Dim iDropDownWidth As Integer
'   Set Widths
    iStandardWidth = 2
    iDropDownWidth = 18
'   Set where we will return
    Set rngOldActiveCell = Selection
'   Set file, folder and path names
    With CreateObject("WScript.Shell")
        strDocuments = .SpecialFolders("MyDocuments")
    End With
    strFileName = ThisWorkbook.FullName
    strDirectory = ThisWorkbook.Path
    If strDirectory = "" Then strDirectory = strDocuments
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Sheets(1).Columns(1).ColumnWidth = iDropDownWidth
    Sheets(1).Activate
    Sheets(1).Range("A2").Activate
    If Not bAboutToClose Then
        Cancel = True
        If SaveAsUI Then 'Do a save as dialogue and save
'            ChDir strDirectory
'   Set File Fileter, Format and Type
            If Val(Application.Version) < 12 Then 'Older Excel Version
                strFileFilter = "Excel Files (*.xls), *.xls"
                strFileFormat = ".xls"
                lFilterIndex = 5
            Else 'Newer Excel Version
                strFileFilter = "Excel workbook with macros, *.xlsm"
                strFileFormat = ".xlsm"
                lFilterIndex = 52
            End If
'   Set Dialogue Box Caption
            Title = "Save As"
'   Get varFileName from SaveAs dialogue
            varFileName = Application.GetSaveAsFilename(InitialFileName:=strFileName, _
                FileFilter:=strFileFilter, _
                FilterIndex:=lFilterIndex, Title:=strTitle)
'   Don't do anything but notify if dialogue was cancelled
            If varFileName = False Then
                lResponse = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
                Exit Sub
            End If
'   Display Full Path & File Name for debug purposes
'            lResponse = MsgBox("You selected " & varFileName, vbInformation, "Proceed")
            strFileName = CStr(varFileName)
'   Save & Close Workbook
            With ActiveWorkbook
                If Val(Application.Version) < 12 Then 'Older Excel Version
                    .SaveAs varFileName
                Else 'Newer Excel Version
                    .SaveAs FileName:=strFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
                End If
            End With
        Else 'Just Save
            Cancel = True
            ThisWorkbook.Save
        End If
'   You are staying in the sheet without closing so set the columns back
        Sheets(1).Columns(1).ColumnWidth = iStandardWidth
'   You are staying in the sheet so restore you position
        rngOldActiveCell.Activate
'   Notify that are not closing for debug purposes
'        MsgBox "Saving without any close"
'        ThisWorkbook.Saved = True
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    bAboutToClose = False
    ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
    Dim iStandardWidth As Integer
    iStandardWidth = 2
    Sheets(1).Columns(1).ColumnWidth = iStandardWidth
    Sheets(1).Range("A2").Activate
    ThisWorkbook.Saved = True
End Sub

If anyone wants to look at the test workbook that I used to play with this, you can access it here:
https://app.box.com/s/ff4qgmydsng392ggqag3yqvzcql4pbet

 

[focuswiz]

Actually, probably much easier with the after save event. I can get rid of most of the code after "If Not bAboutToClose" in the before save event and then put something like this into the after save event:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If Not bAboutToClose Then
        Sheets(1).Columns(1).ColumnWidth = 2
        rngOldActiveCell.Activate
        ThisWorkbook.Saved = True
    End If
End Sub