adapting macro coded in excel 2003 to excel 2010

[bqheng]

Hi, I have this excel file with codes written in excel 2003, but now the IT department is upgrading all computers to microsoft 2010. Is there anyway to convert this file so that it can work in 2010? What I know is that the calendar function is no longer supported in 2010. I tried changing the calendar to a date picker but I couldn't get the codes right. Can someone please help me?

[jaslake]

Hi bqheng

Your Code is password protected.

[bqheng]

Hi jaslake, sorry for my mistake. the password is YourPassword

[bqheng]

Have uploaded a non password protected file, can somebody please help me now?

[jaslake]

Hi bqheng

This can be done in one of two ways.

First Approach
It's my understanding mscal.ocx does not exist in Excel 2010, however, see this link http://www.gmayor.com/downloads.htm

Look for MSCAL.ZIP. The instructions for registering, etc. are included. You may need to recreate the calendar in your workbook. This will need to be done on each Computer that's using the File.

Second Approach
The attached workbook has Code for a Non Active X Calendar Control. MSCAL.OCX is not required. The Calendar will appear as the User Tabs into TextBox1 and Textbox2.

It's my preference...play with it...you may prefer it also.

[bqheng]

Hi Jaslake, thanks for taking time to help me. I tried your second approach and it works fine on Office lower than 2010. When i tried it on Office 2010, there are some errors. I attached the screen shot coz I don't know how to explain what's wrong

[jaslake]

Hi bqheng

Please post the offending File. I've tested what I sent you in Excel 2010 and it worked as expected. We'll see what we can figure out.

[bqheng]

Hi Jaslake, as requested

[jaslake]

Hi bqheng

On my platform the Code performs as expected in Excel 2010.The Screen Shot you posted would indicate a missing Reference.

Go to VBA (Alt + F11). Click on Tools ---> References (you'll need a Password for some reason)
Are any "MISSING"? If so, uncheck the "MISSING" Reference(s).
Click OK. Save and Close the File.
Reopen...any issue?

One further point, the Error Message seems to highlight this line of Code in the Workbook Open Event. Don't know why. If the above fails comment out this line of Code, Save, Close, Reopen.

Please Login or Register  to view this content.

Beyond that I'm stumped.

[bqheng]

Hi Jaslake, i unchecked the "MISSING" in vba and its working fine now. Thank you so much

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.

If this resolves your issue please mark your Thread as SOLVED.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[bqheng]

Hi Jaslake, I found a problem and hope you can help me with it. Users who open the file in 2007 and 2010 have different dates in the months. Please have a look at the attached screen shot for details.

I think it has something to do with this code

Please Login or Register  to view this content.

[jaslake]

Hi bqheng

Attached are Screen Shots of what appears on my Display, Excel 2010 vs Excel 2007. They appear to be the same.

[bqheng]

Hi Jaslake, I have no idea why it can work on your side but not on mine When i changed the above code to vbMonday, the correct date and day is shown on 2007 but incorrect on 2010. is there a way to work around this code? like replace it with something generic?

[jaslake]

Why are you changing this

When i changed the above code to vbMonday

[bqheng]

Hi, someone helped me with the creation of this file some time back. It was initially set to vbMonday and on my side the dates were wrong. So he suggested to changed to vbSunday and it worked. So here I was trying to see if changing it will have any effect on 2007 and 2010

[bqheng]

am thinking whether the problem lies with the settings in excel or the computer. since on your side it looks ok, it shouldn't be a problem with the codes. Do you know of any date settings in excel / computer that might affect this? let me know your thoughts on this

[jaslake]

Hi bqheng

Sorry, have no ideas...

[bqheng]

Hi Jaslake, I have solved the problem. It was due to a different region and language setting (under control panel). some had it set to English (UK) while some had English (Singapore). I have asked everyone to set to the same setting and its working fine now. Thank you so much for your help. If i ever encounter any more excel problems in future, can I ask for your help?

[jaslake]

Hi bqheng

Glad you got it sorted...good work...and yes, be glad to be of help if I can...

If i ever encounter any more excel problems in future, can I ask for your help?

[bqheng]

Hi jaslake, the excel file is in a mess now haha. Some people just do not know how to follow instructions to change their setting. Do you know of any macro code that can detect a computer's regional setting in control panel and set it to the one I want? I saw this Application.International(xlCountrySetting) code, but do not know if it helps or how to apply it here. Can you help me please?

[jaslake]

Hi bqheng

No, I'm not familiar with this

macro code that can detect a computer's regional setting in control panel and set it to the one I want

You may wish to start a new Thread for this issue.