Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Application defined or object defined error

    I have two spreadsheets open at the same time wihch have their own toolbar. Switching between them is fine, the toolbars switch over. But when I close one I get the dreaded "Application defined or object defined error" message. I've tried trapping the error number with no luck, I just get the error message.

    Is there any way of getting Excel VB to point to where the problem is ?

    Thanks
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    calacuccia
    Guest

    Re: Application defined or object defined error

    Hi,

    it would be great if you could post the code causing your error message (which line is highlighted, which are the previous lines and context). Without that it will be difficult to get any help.

    Calacuccia

  3. #3
    Lounger
    Join Date
    Apr 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application defined or object defined error

    Sorry, yes, I've realised it will be difficult. But, there is a lot of code. I've found that the error only occurs when using a macro on the top toolbar to close the active workbook. There are no problems when using the close box or File/Close. The toolbar button is a "Close-NoSave" function. The error occurs after the "Close_nosave" macro executes. The Workbook_Before_close event code runs OK, then the Workbook_Deactivate event runs OK, then I get the error which prevents the Workbook_Activate event on the remaining workbook from running. I've tried creating toolbars with different names for each instance of the workbook open but no luck.

    Anyway, in case someone is keen, I've attached the workbook. It's zipped to save space.

    Just a reminder, the problem is when users open two instances of the workbook. It's a timesheet and so, for example, they may want to copy data from an old sheet to a new one.

    Many thanks.
    Attached Files Attached Files
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  4. #4
    calacuccia
    Guest

    Re: Application defined or object defined error

    Hi Garry,

    The problem lies within your workbook_activate event, more specifically in the error handler.

    Replace it by following module

    Private Sub Workbook_Activate()
    On Error Resume Next
    Application.CommandBars("TimeSheetBar").Visible = True
    Application.CommandBars("Standard").Visible = False
    Application.CommandBars("Formatting").Visible = False

    If Err.Number <> 0 Then
    'MsgBox ("Creating customcommandbar again")
    CreateCustomCommandBar
    End If
    End Sub

    The original module was as follows:

    Private Sub Workbook_Activate()
    On Error Resume Next
    Application.CommandBars("TimeSheetBar").Visible = True
    Application.CommandBars("Standard").Visible = False
    Application.CommandBars("Formatting").Visible = False
    Exit Sub
    errormsg:
    If Err.Number = 5 Then
    'MsgBox ("Creating customcommandbar again")
    CreateCustomCommandBar
    Resume Next
    Else
    MsgBox (Err.Number)
    Resume Next
    End If
    End Sub

    Why was it wrong?

    Well, first of all one line 5, you had 'Exit Sub' so the lines beneath where even never used.

    Second, the error handler had a part Else MsgBox Err.Number which popped up when you activate the worksheet. Not necessary.

    Now, when the command Application.CommandBars("TimeSheetBar").Visible = True generates an error, because you just deleted it with the Workbook_BeforeClose event, the err.number will probably be 5, but any error will do, so I just check If Err.Number <> 0. If therer is an error in other words, the procedure CreateCustomCommandBar will be run now, and you have the menu back.

    Good Luck
    Calacuccia

  5. #5
    Lounger
    Join Date
    Apr 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application defined or object defined error

    calacuccia, many thanks.

    I gave it a try but no luck. I think the problem is that I have a cascade of procedures which run when I execute the Close_nosave macro (close_nosave; workbook_before_close; workbook_deactivate; and workbook_activate after the first workbook has closed).

    I think that the error occurs because the calling macro is in the sheet that has closed.

    I'm trying to find a way of avoiding the deactivate/activate combination altogether !

    Cheers
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  6. #6
    Lounger
    Join Date
    Apr 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application defined or object defined error

    I've given up trying to manipulate attachments to macros from toolbars.

    So, I deleted the macro that caused the problem, then added code to create a new toolbar with a different name on the fly every time there is more than one instance open. Then, when users switch between workbooks it's a matter of making the different toolbars visible/invisible as required.

    The whole thing is just a pain !

    Cheers
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •