Ian has attended:
Excel VBA Intro Intermediate course
Saving changes made by VBA
I have used the following code to opne a file and protect and share the file however the changes are lost when file closes. any suggestions
Sub endofrepmessage()
With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
End With
Sheets("Menu").Select
Range("F34:G35").Interior.ColorIndex = 50
Range("F34:G35") = "Update Completed" & " " & Now()
'
Workbooks.Open FileName:= _
"S:\Healthcare\JOINT HEALTHCARE INFO\KPIs\AZ Cost KPI\AZ CC Archive.xlsm"
Sheets("AZ Archive Complete").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("AZ Archive New").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
If Not ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.SaveAs ActiveWorkbook.Name, accessmode:=xlShared
End If
ActiveWorkbook.Close SaveChanges:=True
MsgBox "Now Shared"
End Sub
RE: saving changes made by VBA
Hi Ian
Thanks for getting in touch. When you're using the SaveAs method in Excel 2007, you need to specify the FileFormat to make sure it is saved as an XLSM (macro enabled workbook).
You'll need a line like this:
ActiveWorkbook.SaveAs ActiveWorkbook.Name, accessmode:=xlShared, fileformat:=52
There's more detail on this from the excellent Ron DeBruin:
http://blogs.office.com/2009/07/07/use-the-vba-saveas-method-in-excel-2007/
You might want to try commenting out the "DisplayAlerts = False" line while you're testing, to make sure it trips the save command.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
