VBA code works in 2003 but NOT 2010

[dsrt16]

Original Post: http://www.ozgrid.com/forum/showthread.php?t=157681&p=573896&posted=1#post573896

I hope I cross post correctly with the link.

I wrote a line of code to rename worksheets, and it works in 2003, but not 2010. My work has excel 2010, and I am suppose to show my program to my boss today, but I can't get the code to run in 2010.

Every time I open the workbook, I get the following error message: -2147417843 An outgoing call can not be made since the application is dispatching an input synchronous call.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Sheets("Set-up Page").Range("B2").Value
End Sub

Each worksheet has that code referencing a different cell. It works perfectly at home, but not at work. I have two hours before I am suppose to present the program I created....HELP!

 

[xenou]

If this error occurs when you open the workbook it may have nothing to do with selection_change events on the worksheets. There's a lot of unknowns here. Though it seems like overkill to me to be changing sheet names every time the cursor moves - I'm not entirely sure this was a good idea to start with.

I would comment out the code and take it from there. It's probably too late as far as your deadline goes. Just try to cut your losses. There's not a lot of Excel 2003 code that actually crashes Excel 2010 (I can't think of anything offhand). So it may be something else at play besides just your code as such. If you haven't tried it, you could always try the magic cure-all of rebooting the system.

 

[dsrt16]

Agreed about the being prepared--my bad! I am just a school teacher with a very basic limited knowledge of VBA, so I am coding a program to make teachers' paperwork lives a little easier. I didn't realize code would run differently on 2010 and 2003 versions. I did comment out the code, and I just explained to her what it would do once that code worked.

I just need the sheet names to change when student names are entered into the cell. It happens once at each quarter when the students names change, so it isn't everytime the cursor moves just when the data in that reference cell changes.

However, I am still wondering why it doesn't work in 2010, but I think I will just try different coding all together that a more advanced programmer gave me. But because I am really try to learn this stuff, I am still curious if anyone knows why it wouldn't work in 2010.

 

[xenou]

I personally don't think the code you have posted would not work in Excel 2010. My guess is there's something else that's the problem - is this the only code in this workbook?

Though I'd probably create a button to apply the changes rather than using selection change event code.

 

[dsrt16]

I already did create a button apply the changes instead of the selection change event.

That code was the only code that came up in the debugger, and once I commented it out, everything ran fine in 2010.

I will test my new code applied to the button today at work. Hopefully, this new one will work in 2010.

 

[dsrt16]

I have changed the code, and I have several other codes in the program, and none of the macros will run. The error message comes up anytime I open the workbook at work. I can not attach a sample since for whatever reason I can not attach files here.

I don't know what to do, but the program runs perfectly at home, and now I have to transfer it to our data network sever at work, but nothing works.

You are right; it wasn't that code in particular. The error message comes up the moment I open the workbook, and the debugger will go off for any code I attempt to run.

Does anyone know what that error message means? If I knew what it meant maybe I could fix the problem.

 

[dsrt16]

I have spent over 14 hours on this program, and now nothing works! I have spent several hours researching the problem, and on all the threads everyone seemed to know why they were getting the error, so the solution was specific to their workbook.

I have no clue why I am getting this error. I can open any workbook created in 2003 on my work computer that does not have macros, but any program with any macro at all will get that error when I open up the workbook. I have gone into the settings and allowed macros--nothing works. I can not save the workbook as a macro enabled workbook either; it will just crash.

Any ideas?

 

[RoryA]

Can you record macros in a new workbook?

 

[dsrt16]

Yep! I just copied and pasted one page from my program over to a new notebook, and I copied two of the macros and pasted into the module, and they ran just fine.

Macros are running. So either I figure out what is causing this error, or I rebuild my entire program on my school computer via copy and pasting, which will be very time consuming.

I tried commented out code by code to see which code was causing the problem, but it won't even let me save the workbook, so I couldn't save and reopen and see if I got the error. It will just reopen it exactly the way it was before with the error message and zero code commented out.

 

[RoryA]

Do you have any addins loaded? (other than PowerPivot, I don't know of any that can stop you from saving workbooks)