| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Bombs in Excel 2007
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.List FillRange = "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 -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Bombs in Excel 2007
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 -- Regards, Tom Ogilvy "Chaplain Doug" wrote: 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.List FillRange = "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 -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Bombs in Excel 2007
that is if those sheets are in ThisWorkbook. If not, then preface them with
a reference to the workbook in which the are located. -- Regards, Tom Ogilvy "Chaplain Doug" wrote: 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.List FillRange = "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 -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Bombs in Excel 2007
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 "Chaplain Doug" wrote: 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.List FillRange = "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 -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Bombs in Excel 2007
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? -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Chaplain Doug" wrote: 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.List FillRange = "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 -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Bombs in Excel 2007
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. -- Regards, Tom Ogilvy "Chaplain Doug" wrote: 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? -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Chaplain Doug" wrote: 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.List FillRange = "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 -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Bombs in Excel 2007
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message ... 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 "Chaplain Doug" wrote: 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.List FillRange = "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 -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007 SP1 - How to do F9 in VBA code? | Excel Discussion (Misc queries) | |||
| Excel bombs out in macro...help please!! | Excel Programming | |||
| Code bombs at line indicated, why? | Excel Programming | |||
| sumproduct bombs out | Excel Worksheet Functions | |||
| Macro runs in Excel 2003, bombs in XP - Compatability question | Excel Programming | |||
Linear Mode
