Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 5 - Invalid Procedure Call or Argument Q
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 5 - Invalid Procedure Call or Argument Q
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 ... 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 5 - Invalid Procedure Call or Argument Q
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" wrote in message ... 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 ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '5': Invalid procdre call or argumnt - End/Debug/He | Excel Discussion (Misc queries) | |||
Run-time Error '5' - Invalid procedure call or argument | Excel Discussion (Misc queries) | |||
Run-time error '5': Invalid Procedure Call or Argument | Excel Discussion (Misc queries) | |||
bizarre "invalid procedure call" error | Excel Programming | |||
Run-time error '5':Invalid Procedure call or argument | Excel Programming |