none
Question on Excel Userform when the userform is closed RRS feed

  • Question

  • Hi,

    I have multiple userforms in my application. When I switch from one userform to another I use unload me command to close the previous form and open the new form.

    What I am looking for is that when I close the form with X button (at the top right corner of the userform) I want to save the excel file and close it.

    I have tried using QueryClose and Terminate Command butthe problem is...when I unload the form these functions are auto called and my excel file gets closed.

    Is there a way to resolve this.


    Regards, Hitesh

    Monday, February 16, 2015 9:03 PM

Answers

  • Does this help?

    With Workbooks("Budget.xlsm")
        If .ReadOnly Then
            SetAttr .FullName, vbNormal
            .ChangeFileAccess xlReadWrite
            .Save
            SetAttr .FullName, vbReadOnly
            .ChangeFileAccess xlReadOnly
            .Close savechanges:=False
        Else
            .Close savechanges:=True
        End If
    End With
    


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Proposed as answer by jackafx Thursday, February 19, 2015 12:59 AM
    • Marked as answer by L.HlModerator Tuesday, February 24, 2015 5:20 AM
    Tuesday, February 17, 2015 8:47 PM

All replies

  • Have you tried...???

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        If CloseMode = 0 Then 'vbFormControlMenu
            'your code to save and close workbook
        End If
    End Sub

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Monday, February 16, 2015 10:39 PM
  • I have made a button called "Save and Close"...

    it contains the below code

    Windows("Budget.xlsm").Activate
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "Budget.xlsm", xlWorkbookNormal, "", , , False
    ActiveWorkbook.Save
    ActiveWorkbook.Close

    I am trying to save the file at the location from where it is opened (which can be any location)

    But nothing gets saved (and no error message). Any idea why??


    Regards, Hitesh


    Tuesday, February 17, 2015 1:03 AM
  • It looks like the following would suffice...

    Workbooks("Budget.xlsm").Close savechanges:=True
    

    Although, you'll probably want to add error handling to make sure that the workbook is in fact open before closing and saving any changes.

    Did you try the QueryClose event handler I proposed?


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Tuesday, February 17, 2015 1:57 PM
  • Hi Domenic,

    Instead I made a button called "Save and Close"

    it contains the below code

    Windows("Budget.xlsm").Activate
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "Budget.xlsm", xlWorkbookNormal, "", , , False
    ActiveWorkbook.Close

    When I try to find the location using ActiveWorkbook.path it gives me address of C:\user\document and not the drive where the budget.xlsm file is saved.

    I want to save the file at the location from where it is opened (which can be any location)

    It always

    What if the workbook is opened in read only mode will it then be saved using above code?

    But nothing gets saved (and no error message). Any idea why??

    Please let me know


    Regards, Hitesh


    Tuesday, February 17, 2015 3:53 PM
  • If Book2.xlsm is the active workbook, ActiveWorkbook.Path should give you the path for that workbook.  Also, the file format should be xlOpenXMLWorkbookMacroEnabled, not xlWorkbookNormal.

    If the workbook is opened in read-only mode, you'll need to deal with it in your code.  In this case, how do you want to proceed?  Do you want to save a copy of the workbook as "Copy of Book2.xlsm"?


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Tuesday, February 17, 2015 5:00 PM
  • Hi Domenic,

    ActiveWorkbook.Path does not give me the path from where the excel file is opened instead it gives me path as "C:\User\[usernam]\documents

    If the file is read only I just want to save the file with the original file name at the same location and replace the older version.


    Regards, Hitesh

    Tuesday, February 17, 2015 5:17 PM
  • Does this help?

    With Workbooks("Budget.xlsm")
        If .ReadOnly Then
            SetAttr .FullName, vbNormal
            .ChangeFileAccess xlReadWrite
            .Save
            SetAttr .FullName, vbReadOnly
            .ChangeFileAccess xlReadOnly
            .Close savechanges:=False
        Else
            .Close savechanges:=True
        End If
    End With
    


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Proposed as answer by jackafx Thursday, February 19, 2015 12:59 AM
    • Marked as answer by L.HlModerator Tuesday, February 24, 2015 5:20 AM
    Tuesday, February 17, 2015 8:47 PM
  • Thanks Domenic,

    This works...thanks a lot


    Regards, Hitesh

    Wednesday, February 18, 2015 10:45 PM
  • You're very welcome!

    Cheers!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Wednesday, February 18, 2015 11:09 PM