Run Time Error 5 - Invalid Procedure Call or Argument Q

[John]

I have a run time error 5 - Invalid Procedure Call or Argument that is
stopping the falling code running (kindly supplied by Andrew in this Group.
I'm running Excel 97. It works fine on Excel XP.

I have looked up this error on Microsoft's site and there is a bewildering
return on this error. I've tried a couple of their suggestions, but I'm
kinda going around blind. Has anyone else experienced this problem?


Private Sub Workbook_Open()
Sheets("Access Log").Visible = True
Sheets("Access Log").Select
ActiveSheet.Unprotect Password:="1234"
x = Sheets("Access Log").Cells(1, 2) ' cell B1 holds log count
Sheets("Access Log").Cells(x + 2, 1) = Format(Now(), "ddd dd/mm/yy at
hh:mm:ss ampm ")
Sheets("Access Log").Cells(x + 2, 2) = Application.UserName
Sheets("Access Log").Cells(1, 2) = x + 1 ' Increment Log count
Sheets("Access Log").Visible = xlVeryHidden
ActiveWorkbook.Save
Sheets("Home").Select
Range("A1").Select
Range("K12").Activate
End Sub

 

[Tim Williams]

Which line is causing the error? You don't seem to be declaring your
variables: you would be better off with "Option Explicit" every time.

I'm not sure you need to unhide the sheet in order to unprotect it or write
to it. Try something like this:

Private Sub Workbook_Open()

Const PW As String = "1234"
Dim r As Long

With ThisWorkbook.Sheets("Access Log")
.Unprotect Password:=PW
r = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Cells(r, 1) = Format(Now(), "ddd dd/mm/yy at hh:mm:ss ampm ")
.Cells(r, 2) = Application.UserName
.Protect Password:=PW
End With

Thisworkbook.save

With ThisWorkbook.Sheets("Sheet2")
.Select
.Range("K12").Activate
End With


end sub


Tim.

 

[John]

Tim, thanks for your code.

I believe the problem might be the line - Sheets("Access Log").Visible =
xlVeryHidden. A neat line if you really want to hide the Access Log from
view. I think Excel 97 can't handle this line

 

[Tim Williams]

John,

I suspected it might be something like that. My code was assuming that the
Access log sheet is always hidden.

Regards
Tim