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

Hi Cinda,

Change the code like this

If Not R Is Nothing Then
    WS.Visible = xlSheetVisible
    Application.Goto R, Scroll:=True
    Exit Sub
End If

-vemaju

vemaju--

You're a lifesaver--that works like a charm!!! Guess it makes since that the sheet should be viewable before I start scrolling around in it and that it isn't necessarily active.

Thanks again for taking the time to help me out!

--Cinda

You've got it

-vemaju