Correcting a cell formula via a patch

[Guest]

I have a sales forecasting spreadsheet that has been distrubited to our sales
force. An error was found in one cell that requires a fix, but, the workbook
is passworded, the page the error is in is protected with password and the
column the cell is in is hidden. I was wondering if it is possible to create
a patch to correct this simple error via vb or vba. To call all of these
sheets back would be too time consuming and to allow the sales people access
to passwording and page protection would be too dangerious.
Any thoughs on this
Thanks

 

[Tom Ogilvy]

It would go something like this.
Dim sName as String, bk as workbook
sName = "C:\Myfolder\MyFile.xls"
set bk = Workbooks.Open(FileName:=sName, password:="SecretPassword")
bk.Worksheets(1).UnProtect Password:="ABCD"
bk.worksheets(1).Formula = "=Sum(A1:B9)"
bk.Worksheets(1).Protect Password:="ABCD"
bk.Close SaveChanges:=True

You could add code to prompt the user to use the mouse to find the file

sName = Application.GetOpenfileName()

Obiously you would want this code in a workbook where the code is protected

You don't need to unhide the column to update the cell.

Add other error handling as appropriate.