Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correcting a cell formula via a patch
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correcting a cell formula via a patch
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. -- Regards, Tom Ogilvy "Rick K" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with correcting writing a formula | Excel Worksheet Functions | |||
HSI in data cell is auto correcting to HIS | Excel Discussion (Misc queries) | |||
CORRECTING ERROR IN A SUM FORMULA | Excel Discussion (Misc queries) | |||
correcting a negative date formula | Excel Discussion (Misc queries) | |||
need help in correcting the formula | Excel Worksheet Functions |