Run Time Error 5 - Invalid Procedure Call or Argument Q

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

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.

--
Tim Williams
Palo Alto, CA


"John" <[email protected]> wrote in message
news:[email protected]...
> 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, 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" <timjwilliams at gmail dot com> wrote in message
news:[email protected]...
> 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.
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "John" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

John,

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

Regards
Tim

--
Tim Williams
Palo Alto, CA


"John" <[email protected]> wrote in message
news:[email protected]...
> 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" <timjwilliams at gmail dot com> wrote in message
> news:[email protected]...
> > 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.
> >
> > --
> > Tim Williams
> > Palo Alto, CA
> >
> >
> > "John" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>