creating a macro to edit protected workbooks

[pman354]

I'm writing a macro that will open a log file and change the value in cell A85 from a one to a zero and then move on to the next log file. The problem I'm running into is that each log file has its own macro that protects the cell i'm trying to overwrite. So every time my code opens a log file, the log's macro stops my code from changing the cell value. Here's my code:




Sub macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+m
'
Dim numrow As Integer
Dim wb As String
Workbooks("Inspection report master list.xlsm").Activate
ActiveWorkbook.Sheets("sheet4").Activate
numrow = 148
Do While numrow < 4094
wb = Range("A" & numrow)
Workbooks("Inspection report master list.xlsm").Activate
Worksheets("Sheet4").Activate
Application.EnableEvents = False
Workbooks.Open FileName:="Q:\Incoming Inspection Reports\" & wb
Workbooks(wb).Activate
ActiveWorkbook.Sheets("sheet1").Activate
ActiveWorkbook.Sheets("sheet1").Unprotect
ActiveWorkbook.Sheets("sheet1").Range("A85").FormulaR1C1 = "0"
Application.EnableEvents = True
ActiveWorkbook.Close SaveChanges:=True
Workbooks("Inspection report master list.xlsm").Activate
ActiveWorkbook.Sheets("sheet4").Activate
Range("O" & numrow).Select
ActiveCell.FormulaR1C1 = "1"
numrow = numrow + 1
Loop
End Sub


The program runs without error but when I check the log files afterwords, cell A85 has not change. Is there a way to edit each log file without the log files macro interfering? I thought Application.enableevents=false would do the trick but it does not seem to be working. Any help would be greatly appreciated.
*For clarity: The workbook im opening is not protected(sorry for the misleading title)

[Kenneth Hobs]

Obviously you would need to pass the password in the Unprotect line and then Protect it after. I normally use Protect with the UserInterface parameter in the workbook's open even to allow code to modify without unprotect/protect each sheet each time.

ThisWorkbook object:

Private Sub Workbook_Open() Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect "ken", UserInterfaceOnly:=True 'True allows code to change data.
Next ws
End Sub

[pman354]

Sorry, the title is misleading.The entire workbook isn't password protected. Anyone can open the workbook but certain cells(including the one i need to change) are protected

[Kenneth Hobs]

Either a worksheet is protected, or it is not. That governs whether locking of cells is in effect or not. If you don't know the password for the worksheet you are trying to change, then you will not be successful.

[pman354]

There is no password protection involved. The log is a form that is that anyone can open and edit. the area outside of the form is hidden/protected by the logs macro. This macro protects ONLY the cells outside of the form area to prevent the user from editing formula's/ data that helps the form run. I'm looking for a way to bypass this macro so my program can edit a cell outside of the form area.

[Kenneth Hobs]

It would not be ethical for us to help you break someone else's routines. Either you are the owner, or you are not.

Feel free to attach example files for us to test if they replicate the concept though. Obviously, if password protected for the VBAProject or worksheet, we would need to know the password(s).

[pman354]

My boss just told me that he set the password as empty quotes. This explains why I was not being prompted for the password(and thought the sheet was not password protected). I can post the code for the logs macro if that will help.

[Kenneth Hobs]

Yes, post the code if you know which is the culprit. Short example files are the best way to get on-target help.

As I explained, be sure to Protect after you UnProtect so that the cell locking will be in effect. Or else, you might be out the door?

Your code can set the events to false early on and set it back to true at the end. I like to put an Error catching routine to make sure the events are set back on should it error.

[pman354]

it looks like the logs macro just searches for blank/ incorrectly filled out fields and provides error messages. Each sub of this macro contains this line at the end: "Sheet1.Protect". I believe these statements are the culprits. I tried using "Application.EnableEvents = False" before opening the log file hoping it would stop the macro from running but it didn't work.

[Kenneth Hobs]

I think you may be off on which workbook is active. One seldom needs to use ActiveWorkbook or Activate. Step through your code one line at a time with F8. You can add a

Debug.Print ActiveWorkbook.Name
'or
s = ActiveWorkBook.Name
Hover cursor of the string variable s to see what it resolves to after each F8 or view debug results in the Immediate Window after each line.

[pman354]

I tried your method of setting a variable equal to the active workbook and used a msgbox to display the active workbook. According to my msg box, the active workbook is set correctly. I also paused the program after it opens the log file and i seem to be able to edit the protected box. this makes me think that application.enableevents command is working correctly. I'm now thinking i may have messed up the line that changes the value of cell A85 or maybe the log is not saving when my program closes it.

[pman354]

It looks like I found the problem. this line: "ActiveWorkbook.Close SaveChanges:=True" was not saving the document when it closed. I added a Activeworkbook.save to my code and it seems like everything is working now. Thanks for your help Kenneth, your debugging strategies helped me figure out my problem.

[LordDragon]

Thank you for sharing that code. Making sure I'm putting the Unprotect and Protect stuff in every time I run a code can be a major headache. This has helped reduce my code size and my headaches a lot.