Code Bombs in Excel 2007

[Guest]

Excel 2007. I have a workbook that has a lot of VBA modules behind it. This
workbook has opened and all the code has functioned quite nicely under Excel
2003 for years. Now some of the most basic stuff is bombing. In particular,
in the workbooks startup code I am getting the following error on this line
of code:

Sheets("Main Menu").PayrollCompare.Enabled = True
Run-time error '1004'

Unable to set the enabled property of the OLEObject class.
When I go into debug when the error occurs, I find that I get the error on
any of the objects that DO reside on the worksheet named "Main." All of
these objects exist on the sheet. Below is the startup routine that has
worked for years. By the way, I thought it might be just a memory issue (as
I had only 512 MB). I just upgraded to 2 GB and I still get the error. Any
help of suggestion would be greatly appreciated. God bless.

Private Sub Workbook_Open()
'GoTo tend
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
ThisWorkbook.Sheets("Budgets").OtherRecipient.ListFillRange =
"Contacts!A3:A" + Trim(ThisWorkbook.Worksheets("Contacts").Cells(1, 1))
ThisWorkbook.Sheets("Income Statements").OtherRecipient.ListFillRange =
"Contacts!A3:A" + Trim(ThisWorkbook.Worksheets("Contacts").Cells(1, 1))
ThisWorkbook.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
tend:
End Sub

 

[Guest]

I would try prefixing the sheet references with Thisworkbook

rather than Sheets("Main"), use ThisWorkbook.Sheets("Main")

or use a

With Thisworkbook
.Sheets("main"). . . .

.sheets("main"). . . .

.Sheets("Detailed Reports") . . .

End With

 

[Guest]

that is if those sheets are in ThisWorkbook. If not, then preface them with
a reference to the workbook in which the are located.

 

[Guest]

As Tom has pointed out one possibility is that 2007 is not treating your
workbook as the activeworkbook when your code executes. If his suggestion
works, that's great.

If not, try and defer your processing by zero seconds. Put your code in a
new sub in a standard module. Then, in the workbook_open procedure, add a
OnTime method that executes the new code with a zero second delay, i.e.,
Application.OnTime(Now(),"{new_procedure}") This has been useful in the past.

BTW, I could not replicate your problem with 2007. I placed a couple of
ActiveX commandbuttons (in the old days that would be from the commandbar
named Control Toolbox) on the worksheet.
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu

 

[Guest]

By gumby that worked! Thanks Tom. But why are there changes like this in
2007? Was I simply getting away with sloppy programming in 2003?

Are you using Office 2007 yet. What is your take on it. Would it be unwise
for me to move all our folks to it now rather than waiting six months for the
problems to get ironed out?

 

[Guest]

I haven't installed it, so I don't have an opinion. Just haven't gotten to
it yet.

Most advise not using an MS product until the first service release. In any
event, I wouldn't upgrade your users until you had all your "stuff" working
in your test environment. I think this type of problem won't get ironed
out. It is perhaps a design choice they made or a side affect of such a
choice.

It is always best to fully qualify all references - that way there is never
any doubt, but we certainly become complacent when we don't have to. I don't
know if MS intentionally tightened up the rules or not - but there is
certainly a history of differences like/similar to this in past products.

 

[Jon Peltier]

I did this text, without an issue. Then I put a couple ActiveX controls onto
a worksheet in Excel 2003, and opened it in 2007, again without an issue.

"Was I simply getting away with sloppy programming in 2003?" By all means
(although I used the sloppy approach in my testing). When I suggest that
people use full references, they look at me funny, like, this works, so why
bother. But it's easier to extend the code if you've already included the
references, and you will never have a case where, for example, Excel thinks
you want to use the active sheet when you wanted to use a sheet far away.

- Jon