Application or object defined error

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Application or object defined error

Post by bknight »

Got this when opening a workbook used for some time

Code: Select all

Private Sub Workbook_Open()
    Dim cel As Range
    Dim rng As Range
    Dim dteTargetDate As Date

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Sheets("Positions").Activate
    
Fails on the last step. Would this make any difference if I haven't installed the updates I have, but not installed?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Application or object defined error

Post by HansV »

I don't think this has anything to do with updates.
Is the sheet Positions hidden?
If not: has the workbook been protected for structure?
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Application or object defined error

Post by bknight »

The positions are visible along with Sheet2 and Sheet3.
Um I don't think I ever have protected a workbook, so I don't know how to check.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Application or object defined error

Post by HansV »

To check whether the workbook has been protected:
- Activate the Review tab of the ribbon.
- If the Protect Workbook button is highlighted, the workbook has been protected. Click the button to unprotect the workbook.

If that is not the cause of the problem, try this:

In a standard module, create the following macro:

Code: Select all

Sub ActivatePositions()
    Sheets("Positions").Activate
Change your Workbook_Open event procedure to

Code: Select all

Private Sub Workbook_Open()
    Dim cel As Range
    Dim rng As Range
    Dim dteTargetDate As Date

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.OnTime Now + TimeSerial(0, 0, 1), "ActivatePositions"
   ...
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Application or object defined error

Post by bknight »

When hovering over the protect workbook, is indicates "to protect"

This workbook_open sub has been in use for years, why does it need a change?

Another workbook has nearly identical structure only Sheet1 and Sheet2, but with similar Workbook_Open

Code: Select all

Private Sub Workbook_Open()
    Dim cel As Range
    Dim rng As Range
    Dim dteTargetDate As Date
    dteToday = Range("J1").Value
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Sheets("Sheet1").Activate
The sub does not run when the workbook is opened as there is "Cells(2, 1).Select" near the end and the cell in focus/select is NOT 2, 1 but the cell when last saved.
I really hate this swap over to new HD everything is broken from when using the old HD.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Application or object defined error

Post by HansV »

I'm afraid I'd have to see a copy of the workbook.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Application or object defined error

Post by bknight »

Too big, will need to send via email, as I haven't had the time to refresh links to cloud.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Application or object defined error

Post by HansV »

Both workbooks that you sent me open without error...
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Application or object defined error

Post by bknight »

Do they both end up selecting Cell(2,1)?
What could be the issue?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Application or object defined error

Post by HansV »

Yes, the both select cell A2.

No idea why it doesn't work for you.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Application or object defined error

Post by bknight »

Please have a look at all the codes, there may be a conflict.
I can think of two posible solutions in the workbook add an On_Error Resume Next which I don't really like
This is really perplexing, I save The Tradestation and reopened it no error messages.
Dividend still doesn't work.

ETA:
I closed Excell and reopened it then opened Dividend and it works. I noticed before that VBA projects still had the Tradesation ones even after it was shut down.

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Application or object defined error

Post by bknight »

Ok, added the on error resume next then I tried to change an entry on the sheet which should activate the worksheet_change, but what I got was another error
Excel 2007 Developer Reference > Visual Basic for Applications Language Reference > Visual Basic Language Reference > Error Messages

Can't exit design mode because control can't be created

All controls must be instantiated before you can exit design mode. This error has the following causes and solutions:


The control specified in the error message dialog box could not be created.
Code can only run after all controls are instantiated and properly connected. Make sure every file needed for the control is available before trying again.


For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

Please try changing cell O45 to the value of 12/30/2021 and see what happens on your end.

ETA: I forgot to add error message
Can't exit design mode because Control "CommandButton1 can not be created. That was on a chnge of cell o45

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Application or object defined error

Post by HansV »

I checked all the code; except for some variables that hadn't been declared explicitly, I didn't notice anything special.

I changed O45 in the TradestationPositions workbook as suggested, and didn't encounter an error either.

See if the problems disappear if you save the workbooks as Excel Macro-Enabled Workbooks (*.xlsm).
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Application or object defined error

Post by bknight »

You know all this started with the macro to delete files as administer, then I added two command buttons and an "information" button. I wonder if deleting one or more of the buttons might solve the problem. On the other hand you are not seeing any issues with opening/changeing so I think I'll go quite on this subject and install the updates that I have and see if it makes any difference.

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Application or object defined error

Post by bknight »

I attempted to install the 3 updates I have today. I received information message that they had already been installed.
How is this possible when I installed the version from a downloaded file X12-30196 from 2010 and no further updates until today?
Where do you check the version?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Application or object defined error

Post by HansV »

Is this for Office 2007? No updates have been released for that version recently...
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Application or object defined error

Post by bknight »

Yes 2007. The updates are legacy.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Application or object defined error

Post by HansV »

To find out the installed version:
Click the Office button in any Office application.
Select 'Excel Options'.
Select Resources in the navigation pane on the left.
Click the About button.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Application or object defined error

Post by bknight »

Ok, SP3

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Application or object defined error

Post by HansV »

That is the latest and last service pack for Office 2007.
Best wishes,
Hans