Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 3rd, 2006, 01:35 PM
chp chp is offline
Registered User
 
Join Date: Feb 2006
Location: Richmond, VA, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
  #2 (permalink)  
Old April 4th, 2006, 04:34 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: , , Finland.
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #3 (permalink)  
Old April 4th, 2006, 07:57 AM
chp chp is offline
Registered User
 
Join Date: Feb 2006
Location: Richmond, VA, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #4 (permalink)  
Old April 4th, 2006, 08:12 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: , , Finland.
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You've got it

-vemaju


Similar Threads
Thread Thread Starter Forum Replies Last Post
ADODB.Connection user-defined type not defined Wall st Guru Excel VBA 2 March 26th, 2014 03:44 PM
User-defined type not defined (Icecream.mdb) dloren01 BOOK: Beginning Access VBA 0 June 22nd, 2005 10:36 PM
VB.net Database -> user defined object boneill VB How-To 4 May 2nd, 2005 05:23 PM
object defined error piyushdabomb Excel VBA 1 February 1st, 2005 01:26 AM
Application-defined or object-defined error James Excel VBA 1 August 12th, 2003 11:50 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.