Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:07 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.