Send Macro To Be Used To Update Another Workbook

[AndyPandy27]

Hi All,

I have a bit of an issue, which I'm hoping someone might be able to help me with.

I have created a Business Plan in Excel, which has been sent out, via email, to approximately 100 users.

Each user will then have saved his/her own copy from the email and populated their Plan with their own information, exclusive to them.

Since sending this out about 2 weeks or so ago, I have noticed a very basic error in one of the formulae in my Master document.

As all the documents are protected, and the majority of users are not at all skilled with Excel, I am hoping there might be a way of me sending them another Excel file, which just has a button on it, linked to a Macro, which when run could make the changes in their spreadsheet, and fix the issue.

I would envisage giving them 3 simple instructions:

1 - Save your Business Plan to your Desktop (in order for me to reference the correct path in the Macro)
2 - Ensure your file is called "Business Plan 2013" (same reason as above)
3 - Click the button in the Excel file I have emailed to you

The macro would then basically say, find the file "Business Plan 2013.xlsm" in Desktop, then change the incorrect formulae to be right.

Is this at all possible?

The only issue is that the SUM formulae in some cells on the worksheet "Front Cover", are omitting one cell, thus yielding incorrect totals.

e.g. 'Front Cover'!G11 currently is: =SUM(C11:E11), but it should be =SUM(C11:F11).

Is is the same for 'Front Cover'!G11, 'Front Cover'!G12, 'Front Cover'!G13, 'Front Cover'!G15 and 'Front Cover'!G16.

Any help you can provide would be greatly appreciated.

Many thanks,

Andy

 

[rallcorn]

You would also need instructions explaining to the users how to set up (or check) Macro Security and enable the macro in you "fix it" file.

You weren't specific about whether the workbooks or worksheets (or both) are protected. The below code assumes both are protected with the same password "bizplan".

Sub ChangeFormula()
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
'Open password protected workbook on desktop
Workbooks.Open Filename:="C:\Documents and Settings\" & Environ("username") & "\Desktop\Business Plan 2013.xlsx", Password:="bizplan", ignoreReadonlyrecommended:=True
 
Sheets("Front Cover").Activate
 
'If the "Front Cover" SHEET is also protected,
'unprotect it
ActiveSheet.Unprotect Password:="bizplan"
 
'Modify formula
Sheets("Front Cover").Range("G11:G13,G15:G16").Formula = "=Sum(C11:F11)"
 
'If the "Front Cover" SHEET is also protected,
'REprotect it
ActiveSheet.Protect Password:="bizplan"
 
'Save and close the opened workbook
ActiveWorkbook.Save
ActiveWorkbook.Close
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
MsgBox "Done!"
 
End Sub

 

[AndyPandy27]

Hi rallcorn,

This is superb, and works like a charm - thank you so much.

I'm not a VBA pro just yet (working on it!), so your comments in the above were fantastic and incredibly useful. I've managed to tweak it ever-so-slightly to adjust for my exact needs etc, and it's working brilliantly!

I think the guys should be able to cope with this rather than messing about editing any formulae themselves!

This will teach me to not check my work thoroughly first time.

Thanks again for your help,

Andy

 

[rallcorn]

This will teach me to not check my work thoroughly first time.

Been there . . . done that - I lived a few years in the budget/planning world!

Glad you were able to make it work and happy I was able to help out.

 

[AndyPandy27]

Hi rallcorn,

Despite your solution working brilliantly, and it answering my original question perfectly - the goalposts have now slightly changed, so I'm wondering if you could assist me further...?

The solution above, as I had originally specified, relies on the user having to ensure that the workbook to be amended is called a specific filename, and is saved in a specific location.

Management has decided that this poses a potential risk, as people who have renamed their Business Plan and/or saved it in a different location, will either not know how to rename the document, or will do a Save As, meaning they will have multiple copies of the Plan, and potentially get things wrong.

As such, it has been proposed to slightly amend the Macro to, instead of specifying the filepath, have a dialgue box (similar to the dialgue box you see when you select File > Open in Excel), to browse to their Business Plan workbook, and use that file path in place of the hard-coded one:

Workbooks.Open Filename:="C:\Documents and Settings\" & Environ("username") & "\Desktop\Business Plan 2013.xlsx", Password:="bizplan", ignoreReadonlyrecommended:=True

Is it possible to amend the Macro, to allow the user to browse to find their Business Plan, and then the Macro use the information they have inputted to then run exactly the same as before?

Thanks again - let me know if you need further explanation.

Andy

 

[rallcorn]

Try this:

Dim [COLOR=#ff0000]SelectFile[/COLOR] As String
 
'End-User browsing for file
[COLOR=#ff0000]SelectFile[/COLOR] = Application.GetOpenFilename
 
'Open password protected workbook on desktop
Workbooks.Open filename:=[COLOR=#ff0000]SelectFile[/COLOR], Password:="bizplan", ignoreReadonlyrecommended:=True

 

[AndyPandy27]

Hi rallcorn,

Apologies for the delay in my response. Thank you once again - this has worked like a charm!

So helpful to have this little gem up my sleeve.

Cheers,

Andy