Appl-defined or Object-defined error 1004
I've written the following code (borrowing the worksheet search section from a bulletin board) to prompt the user for their ID and then display only the worksheet that contains their ID. All worksheets are "very hidden" except the Cover (which is never hidden since we always have to have one open).
I've gotten almost everything to work except part of the code I got off the bulletin board. When I find a match in the Next statement, I get the "Application-Defined or Object-Defined Error" (1004) on the goto line.
If Not R Is Nothing Then
Application.Goto R, Scroll:=True
ActiveSheet.Visible = xlSheetVisible
Exit Sub
End If
If this borrowed code works, and it seems to based on the bulletin boards, is there something further up in the code that's just showing its ugly head at this point? The full code is provided below.
Any guidance you can provide would be greatly appreciated. I've definitely spent too much time already trying to figure this out on my own.
Thanks!
--Cinda
Private Sub Workbook_Open()
'Asks user for employee number then displays worksheet for the unit
'where they enter their time. Workbook starts with all sheets hidden.
'When it finds a match, it displays that worksheet and scrolls to the
'row containing the Employee Number.
'NOTE: all cells on the worksheet for Employee Number must be formatted
'as text. Otherwise, a number with a leading zero will not be found.
Dim EmpNum, Msg As String
Dim WS As Worksheet
Dim R As Range
EmpNum = Application.InputBox("Please enter your Employee Number.", _
"Employee Verification")
'If they click Cancel --> EmpNum = False
'If they don't enter a value and then click OK --> EmpNum = ""
'That means we don't want to open the workbook at all
If EmpNum = "" Or EmpNum = "False" Then ThisWorkbook.Close savechanges:=False
'If they enter "stop", then exit the macro. This is just for testing but
'we will want to add an admin account that will be able to view all worksheets.
If EmpNum = "stop" Then
'need to display all worksheets if Admin Account
Exit Sub
End If
'If they enter a <value> and then click OK --> EmpNum = <value>
'Search each worksheet to find the one that contains that value.
'When found, display the worksheet and scroll to the location of
'the Employee Number.
For Each WS In ActiveWorkbook.Worksheets
Set R = WS.Cells.Find(What:=EmpNum, After:=WS.Range("A1"), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not R Is Nothing Then
Application.Goto R, Scroll:=True
ActiveSheet.Visible = xlSheetVisible
Exit Sub
End If
Next
'Notify user there was no match found and close workbook
Msg = "This Employee Number could not be found." & vbCrLf & _
"Please verify the number then open the spreadsheet again." & vbCrLf & _
"If you need assistance, please contact your manager."
MsgBox (Msg)
ThisWorkbook.Close savechanges:=False
End Sub
|