Recurring Problem in Excel 2007

[Guest]

Excel 2007. I have code that worked for years with Excel 2003 and now fails
regularly (but not always). I get the following error:Run time error '1004'

Unable to set the Enabled property of the OLEObject class
The error occurs on the first and any of the subsequent lines below the
asterisks in the following code. Someone had suggested fully qualifying the
references, which I did, but the problem persists. Thanks for ANY help you
may provide. God bless.

Private Sub Workbook_Open()
'GoTo tend
With ThisWorkbook
.Sheets("Main Menu").Activate
.Sheets("Main Menu").ScrollArea = "E7"
'Disable all buttons until initialization is complete.
'Make all sheets except Main invisible until init complete
.Sheets("Income Statements").Visible = False
.Sheets("Detail Reports").Visible = False
.Sheets("Budgets").Visible = False
.Sheets("Payroll Compare").Visible = False
.Sheets("Payroll ACH").Visible = False
.Sheets("Main Menu").IncomeStatements.Enabled = False
.Sheets("Main Menu").DetailReports.Enabled = False
.Sheets("Main Menu").Budgets.Enabled = False
.Sheets("Main Menu").PayrollCompare.Enabled = False
.Sheets("Main Menu").PayrollACH.Enabled = False
.Sheets("Main Menu").Setup.Enabled = False
.Sheets("Main Menu").ExitButton.Enabled = False
'Open status form and initialize completion boxes
StartupStatus.CheckBox1.Value = False
StartupStatus.CheckBox2.Value = False
StartupStatus.CheckBox3.Value = False
StartupStatus.Show 0
'Check to see if Express ClickYes is Running
If Not fIsProcessRunning("C:\Program Files\Express
ClickYes\ClickYes.exe") Then
If Dir("C:\Program Files\Express ClickYes\ClickYes.exe") = "" Then
MsgBox "Express ClickYes is not installed on your system." +
Chr(10) + "This will affect sending email from the switchboard." + Chr(10) +
"Contact your system administrator to have ClickYes installed.",
vbInformation, "Express ClickYes Not Installed"
Else
Shell ("C:\Program Files\Express ClickYes\ClickYes.exe")
End If
End If
StartupStatus.CheckBox1.Value = True
Application.ScreenUpdating = False
.Sheets("Contacts").Activate
Call GetAllAddresses
StartupStatus.CheckBox2.Value = True
.Sheets("Main Menu").Activate
.Sheets("Budgets").OtherRecipient.ListFillRange = "Contacts!A3:A" +
Trim(ThisWorkbook.Worksheets("Contacts").Cells(1, 1))
.Sheets("Income Statements").OtherRecipient.ListFillRange =
"Contacts!A3:A" + Trim(ThisWorkbook.Worksheets("Contacts").Cells(1, 1))
.Sheets("Detail Reports").OtherRecipient.ListFillRange = "Contacts!A3:A"
+ Trim(ThisWorkbook.Worksheets("Contacts").Cells(1, 1))
'Update the Project Definitions sheet
Call Update_Projects_Information
StartupStatus.CheckBox3.Value = True
'Make sure only one sheet is created in new workbooks
Application.SheetsInNewWorkbook = 1
'Delay before removing status form
Call WaitIt(2)
StartupStatus.Hide
'Enable all buttons
'Make all sheets visible
.Sheets("Income Statements").Visible = True
.Sheets("Detail Reports").Visible = True
.Sheets("Budgets").Visible = True
'Only allow privileged users to access payroll functions
If PrivUser(Environ("UserName")) Then
**********************************************************
.Sheets("Main Menu").PayrollCompare.Enabled = True
.Sheets("Main Menu").PayrollACH.Enabled = True
.Sheets("Payroll Compare").Visible = True
.Sheets("Payroll ACH").Visible = True
Else
.Sheets("Main Menu").PayrollCompare.Enabled = False
.Sheets("Main Menu").PayrollACH.Enabled = False
.Sheets("Payroll Compare").Visible = False
.Sheets("Payroll ACH").Visible = False
End If
.Sheets("Main Menu").IncomeStatements.Enabled = True
.Sheets("Main Menu").DetailReports.Enabled = True
.Sheets("Main Menu").Budgets.Enabled = True
.Sheets("Main Menu").Setup.Enabled = True
.Sheets("Main Menu").ExitButton.Enabled = True
Application.ScreenUpdating = True
End With
tend:
End Sub

 

[Guest]

I did not find where the enable property applied to the worksheet per se. It
does apply to Pivot Tables, and sheet related items, but not the sheet. I
think all you need is to make the sheet visible and the error message should
go away.

 

[Guest]

OOops! sorry about that. I see that you did just that. I misread the code.

 

[Guest]

I ran this workbook and code on a machine that has Excel 2003 and there is NO
problem. Why am I getting this only in Excel 2007. These controls DO exist
on the sheet and the properties referred to DO exist for the control. Any
help will be appreciated.

 

[Jon Peltier]

I just ran a little test. It seems that Excel 2007 can only change ActiveX
controls which are on the active sheet. I ran another little test, and
learned that Excel 2000 also worked this way, unable to change an ActiveX
control unless it is on the active sheet. I tried once more, and learned
that in Excel 2003, the controls on a sheet can be changed even if it isn't
the active sheet.

This tells me you need to activate the Main Menu sheet before the second
part of the code runs.

- Jon

 

[Guest]

Dear Jon:

I modified it thus:

ThisWorkbook.Sheets("Main Menu").Activate
'Only allow privileged users to access payroll functions
If PrivUser(Environ("UserName")) Then
ThisWorkbook.Sheets("Main Menu").PayrollCompare.Enabled = True

It worked once without error. Then the next time I opened the workbook, the
same error came back up. I also got another "Run time rrror '1004'" come up
just a minute ago on a code sequence that is absolutely good.

ThisWorkbook.Sheets("Contacts").Unprotect
'Now clear all cells and refill
ThisWorkbook.Worksheets("Contacts").Columns("A:F").Select
Selection.Delete

It bombed on the Selection.Delete

Never got this before. Arghhhh!!!! It is bad enough having errors, but to
have bogies that come and go is nearly impossible! I have run this workbook
from another system with Excel 2007 and had the same on again off again run
time errors.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

I just ran a little test. It seems that Excel 2007 can only change ActiveX
controls which are on the active sheet. I ran another little test, and
learned that Excel 2000 also worked this way, unable to change an ActiveX
control unless it is on the active sheet. I tried once more, and learned
that in Excel 2003, the controls on a sheet can be changed even if it isn't
the active sheet.

This tells me you need to activate the Main Menu sheet before the second
part of the code runs.

- Jon

 

[Guest]

Dear Jon:

Incidently, when I added the:

ThisWorkbook.Sheets("Main Menu").Activate

It made it the third time I did this in a short sequence of code. This
cannot be the solution (and it obviously did not fix it). Any other thoughts?

How could I submit my workbook to someone at Microsoft and have them try it?
Would you be willing? I will gladly send it to you.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

I just ran a little test. It seems that Excel 2007 can only change ActiveX
controls which are on the active sheet. I ran another little test, and
learned that Excel 2000 also worked this way, unable to change an ActiveX
control unless it is on the active sheet. I tried once more, and learned
that in Excel 2003, the controls on a sheet can be changed even if it isn't
the active sheet.

This tells me you need to activate the Main Menu sheet before the second
part of the code runs.

- Jon

 

[Guest]

Also, after I get the run time errors and go into debug, I am unable to close
the workbook. The whole thing hangs and I have to kill it from the Task
Manager.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

I just ran a little test. It seems that Excel 2007 can only change ActiveX
controls which are on the active sheet. I ran another little test, and
learned that Excel 2000 also worked this way, unable to change an ActiveX
control unless it is on the active sheet. I tried once more, and learned
that in Excel 2003, the controls on a sheet can be changed even if it isn't
the active sheet.

This tells me you need to activate the Main Menu sheet before the second
part of the code runs.

- Jon

 

[Peter T]

Hi Jon,

FWIW I don't seem to have any problem changing ActiveX controls that are not
on the activesheet in Excel 2000.

Following worked every time, opening in new or same sessions with Sheet1 not
active.

Sub auto_open()
Dim ole As OLEObject

Set ole = Worksheets("Sheet1").OLEObjects("Commandbutton1")
ole.Enabled = Not ole.Enabled

Worksheets("Sheet1").CommandButton2.Enabled = _
Not Worksheets("Sheet1").CommandButton2.Enabled

End Sub

Regards,
Peter T

I just ran a little test. It seems that Excel 2007 can only change ActiveX
controls which are on the active sheet. I ran another little test, and
learned that Excel 2000 also worked this way, unable to change an ActiveX
control unless it is on the active sheet. I tried once more, and learned
that in Excel 2003, the controls on a sheet can be changed even if it isn't
the active sheet.

This tells me you need to activate the Main Menu sheet before the second
part of the code runs.

- Jon

 

[Jon Peltier]

That's bizarre. I tested several times the other day before posting my
response. This morning everything works fine. A similar problem I had with
Excel 2000 VBA is also cleared up (not being able to manipulate chart
gridlines). Both computers which had the error the other day were rebooted
this morning, so I guess I should follow my own advice more frequently, and
try first to see whether rebooting will clear up any problem.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Hi Jon,

FWIW I don't seem to have any problem changing ActiveX controls that are
not
on the activesheet in Excel 2000.

Following worked every time, opening in new or same sessions with Sheet1
not
active.

Sub auto_open()
Dim ole As OLEObject

Set ole = Worksheets("Sheet1").OLEObjects("Commandbutton1")
ole.Enabled = Not ole.Enabled

Worksheets("Sheet1").CommandButton2.Enabled = _
Not Worksheets("Sheet1").CommandButton2.Enabled

End Sub

Regards,
Peter T

 

[Peter T]

Strangely, earlier this week NickHK got code to work before posting that
should have failed, later tried again and it failed as expected (setting a
Picture ref to a wmf vector drawing that's not a picture).

Clearly, Excel works in different ways according to the level of humidity!

Regards,
Peter T

 

[Jon Peltier]

I have had about three different issues in the past week where code that
should have worked didn't, and without doing much different than closing and
reopening the file, or at the most extreme restarting Excel, the code
suddenly started working. Either I'm going senile, or Excel is.

- Jon

 

[Peter T]

I wonder if those problems relate to files in the temp folder, whether truly
temporary within the session or leftovers. It's speculation but I'm
sometimes surprised as to how files named vb* get in there even when I
haven't used the VBE.

Regards,
Peter T

 

[Jon Peltier]

I think even running code that uses certain VB components leads to seeing
these files (like MSForms.exd and RefEdit.exd).

- Jon

 

[Nick Hebb]

I have had about three different issues in the past week where code that
should have worked didn't, and without doing much different than closing
and reopening the file, or at the most extreme restarting Excel,
the code suddenly started working. Either I'm going senile,
or Excel is.

I've had the same thing happen a number of times. In the worst cases,
the problem suddenly reappears some time down the road.

There was one KB fix for some obscure problem, that I've applied a few
times. The KB article recommending selecting the worksheet object
before performing the action, even though that shouldn't be necessary.
I can't remember what the object and action were, but I've tried that
technique on other problems with success.

The other problem I had a lot was losing my global reference to the
Application object (from VB6). I added a couple of lines that cleared
up the problem:

If gxlApp is Nothing Then
' do nothing
End If

That, too, cleared up the problem. I don't know why. I just burn some
incense, mumble a few incantations to the Excel gods, and move along.

Nicholas Hebb
BreezeTree Software
http://www.breezetree.com