Save workbook when updating exchange rates

[CV899000]

Hello,

I have, with help of this forum, gotten my workbook to close without asking to save.

I have done that with this code;

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Saved = True
End Sub

Now, the problem is, that this code disables anybody from saving any changes (and that is good, most of the time).

However, I have 1 sheet and two cells in another sheet that I would like to be able to save, and that revolves around my exchange rates.
I have a command button that I can press to update my exchange rates in a sheet called "exchange rates" and then I have two cells in a sheet called "Prices" which contains the date and time of the last update and who updated it.

But, the code that disables the save function, also does so that I cannot save the new exchange rates and information in "Prices" when I have updated the rates.

Can I put a code into my command button sub, that saves the document when the exchange rates has been updated?

Here is the code for the command button that updates my rates:

Sub Update_rates()
Application.ScreenUpdating = False
Sheets("Exchange rates").Visible = True
' Update_rates Macro
    If MsgBox("Internet connection needed to update exchange rates. Update now?", _
    vbYesNo) = vbNo Then
    Sheets("Exchange rates").Visible = xlVeryHidden
    Application.ScreenUpdating = True
    Exit Sub
    End If
    Sheets("Exchange rates").Select
    Range("A1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Prices").Select
Sheets("Exchange rates").Visible = xlVeryHidden
Application.ScreenUpdating = True
    MsgBox "Exchange rates has been updated from [URL="http://www.x-rates.com/"]Exchange Rates - X-Rates[/URL]"
Range("K7") = Now
Set objAD = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" & objAD.UserName)
strDisplayName = objUser.DisplayName
Range("K5").Value = (strDisplayName)
End Sub

 

[steve the fish]

Could you not just use:

ThisWorkbook.Save

rather than

Saved = True

 

[CV899000]

I could have, but the problem is that I have another code that unhides three sheets when I open the workbook.
If I save the workbook with these sheets visible, then they won't be hidden next time.
But I kind of used your advice, I did this to my update rates code:

Sub Update_rates()
Application.ScreenUpdating = False
Sheets("Exchange rates").Visible = True
' Update_rates Macro
    If MsgBox("Internet connection needed to update exchange rates. Update now?", _
    vbYesNo) = vbNo Then
    Sheets("Exchange rates").Visible = xlVeryHidden
    Application.ScreenUpdating = True
    Exit Sub
    End If
    Sheets("Exchange rates").Select
    Range("A1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Prices").Select
Sheets("Exchange rates").Visible = xlVeryHidden
Application.ScreenUpdating = True
    MsgBox "Exchange rates has been updated from [URL="http://www.x-rates.com/"]Exchange Rates - X-Rates[/URL]"
Range("K7") = Now
Set objAD = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" & objAD.UserName)
strDisplayName = objUser.DisplayName
Range("K5").Value = (strDisplayName)
Application.ScreenUpdating = False
    Sheets("Macros disabled").Visible = True
    Sheets("Information").Visible = xlVeryHidden
    Sheets("Prices").Visible = xlVeryHidden
    Sheets("Copied Prices").Visible = xlVeryHidden
ThisWorkbook.Save
    Sheets("Information").Visible = True
    Sheets("Prices").Visible = True
    Sheets("Copied Prices").Visible = True
    Sheets("Macros disabled").Visible = xlVeryHidden
Application.ScreenUpdating = True
End Sub

And kept my beforeclose code the same.

 

[Jaafar Tribak]

Or just place ThisWorkbook.Save as suggested by Steve at the very end of the Update_rates routine but be aware that as per your previous thread you may also have to hide the worksheets that are supposed to be hidden and make the "macro disabled" sheet as the welcoming sheet before saving the workbook.

 

[CV899000]

Or just place ThisWorkbook.Save as suggested by Steve at the very end of the Update_rates routine but be aware that as per your previous thread you may also have to hide the worksheets that are supposed to be hidden and make the "macro disabled" sheet as the welcoming sheet before saving the workbook.

That I exactly what I have done