Code crashes when closing workbook

[Glaswegian]

Hi all

I'm creating a workbook that will allow users to amend an existing workbook. Since I don't know how users will have named their files I'm using GetOpenFilename and letting them select the relevant file. This is the code

Private Sub CommandButton1_Click()
Dim myFile
myFile = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
    If myFile = False Then
        MsgBox "No files were selected - please try again.", vbCritical + vbOKOnly, "Warning"
            Exit Sub
    End If
Workbooks.Open myFile
Application.ScreenUpdating = False
With ActiveWorkbook.Sheets("Sheet3")
    .Visible = True
    .Range("C23").Value = "E"
    .Range("C24").Value = "F"
    .Range("C25").Value = "G"
    .Range("C26").Value = "H"
    .Range("C27").Value = "I"
    .Range("C28").Value = "J"
    .Range("C18:C28").Name = "LendCat"
    .Visible = False
End With
ActiveWorkbook.Close True
Application.ScreenUpdating = True
End Sub

The problem occurs when the above code is closing the amended workbook. The second workbook has code that writes to a Log file and it crashes after the Unprotect line. This is the code in the second workbook - it will be the same no matter to what users have renamed the file.

With Sheets("Log")
    .Unprotect Password:="*****"
    .Range("F65536").End(xlUp).Offset(1, 0).Value = Date <---crashes on this line
    .Range("G65536").End(xlUp).Offset(1, 0).Value = Time
    .Range("H65536").End(xlUp).Offset(1, 0).Value = UserName
    .Range("I65536").End(xlUp).Offset(1, 0).Value = NameOfComputer
    .Protect Password:="*****"
End With

I can't see any particular reason for this as this second workbook will be opened and closed on a regular basis. Can anyone help?

Thanks

[lucas]

You don't say what the error is so it's guesswork but maybe you should try actually saving the workbook before you try to close it instead of closing and saving changes which might be causing a conflict with the workbook close event code......just guessing.

[Glaswegian]

Ooops! Sorry about that - it's the old 1004 error - "the cell you're trying to change is protected etc..."

Thanks - I'll give your suggestion a go. When using the second workbook on it's own, you can save and close it no problem. That's what I don't understand.

[Glaswegian]

No joy, I'm afraid - still the same 1004 error. Weird...

[X10A]

Not sure if it will help, but how about running the code in the second workbook from itself? If it works, at least you know the error comes from your first code. Then you can work on it.

[lucas]

Ooops! Sorry about that - it's the old 1004 error - "the cell you're trying to change is protected etc..."

actually it is crashing on the very first line instead of the line you point out........it's not unprotecting.

You tried something like this:
[VBA]
ActiveWorkbook.Save
ActiveWorkbook.Close
[/VBA]

[X10A]

In your first workbook, look at the following line of code

[VBA]
With ActiveWorkbook.Sheets("Sheet3")
[/VBA]

If Sheet3 is protected, then you have to add something like

[VBA]
With ActiveWorkbook.Sheets("Sheet3")
.Unprotect Password:="*****"
'rest of the code
[/VBA]

so that the 1004 error will go away.