The Run-Time Error 1004 That Wouldn't DIE

D

Damian Carrillo

I am slowly going mad trying to figure out what has gotten into this
particular workbook. I have used similar code numerous times in the past,
but for some reason this time it JUST... WON'T... WORK! I don't know why!

Everytime it hits the last line, VBA bleats like a sheered sheep about how
the target cell is protected. But its not. The targeted workbook comes
unprotected from a vendor. Even if it was protected, I've tried multiple
ways to make it not be protected.

<---snip---->
'Open file for Stage 1 processing and file conversion
Workbooks.Open FileName:=FilePath & "\Travel.xls"
'Windows("Travel.xls").Activate
Workbooks("Travel.xls").Activate
EnginesOffline
ShieldsDown
Defenseless
Select Case Worksheets(1).Range("R1").Value
Case "1" Or "2" Or "3"
Response = MsgBox("Stage ONE part " &
Worksheets(1).Range("R1").Value & " has already been run on this file!" &
Chr(13) & _
"It is recommended that you rebuild the file from scratch,
unless you have already made changes." _
& Chr(13) & "Do you wish to rebuild the file?", vbYesNo,
"Incomplete File Cycle Detected!")
If Response = vbYes Then
FileCopy DestinationFile, SourceFile 'Replace file from backup
Else
MsgBox "Travel Bill Automation Halted by user " &
CurrentUserName, vbOKOnly, "Program Halt!"
Exit Sub
End If
Case Else
MsgBox "Stage ONE now commencing on this file", vbOKOnly,
"Processing..." 'Continue
End Select
Workbooks("Travel.xls").Unprotect
Workbooks("Travel.xls").Sheets(1).Cells(1, 18).FormulaR1C1 = "1"
<-----Snip----->

Any suggestions? Is there a known issue with random unprotect failures? To
rule out my custom subroutines, here's what they do:

EnginesOffline - just shuts off updating;
ShieldsDown - shuts off protection to every sheet in the active workbook;
Defenseless - I made this up specifically to overcome this obstacle; it
calls a subroutine that unlocks every cell on every worksheet in the active
workbook.)

This may be another one of those "Blatent Idiotic Overlooking of the
Obvious" moments, and I'd be glad to know why!

Sincerely,
Damian Carrillo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top