Clean Ugly Code

The code in the attached spreadsheet is designed to run on a different version of Excel. I am using Excel 2010 with a 64bit Windows 7.  Assistance in getting it to run correctly on that platform would be greatly appreciated.
ugly-code.xlsm
rtod2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
There doesn't appear to be anything that needs changed in the code for it to work in 2010.

Are you having problems with it?
0
rtod2Author Commented:
Thank you sir for your comment.  Here is a video of what it does for me http://screencast.com/t/YBZNmm5K
0
NorieVBA ExpertCommented:
That isn't really a problem with the code, it's more likely to be  a problem with references, perhaps the reference for the Calendar Control.

Take a look under Tools>References... and look for anything marked as MISSING.

If it's the Calendar Control reference that's marked like that the problem could be because the file for that control is in a different location.

The file name is MSCAL.OCX but I'm not sure where it should be found.

I also use Windows 7 64 bit and though I'm pretty sure the file is located somewhere I downloaded it to a local folder.

That's not ideal obviously but it works for the rare occasions I use the calendar control.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

rtod2Author Commented:
Hmm,
Any way that it can give me a calendar without generating that error or looking for something that might not be on a particular machine?
0
NorieVBA ExpertCommented:
You might find some code to deal with the references if you search.

Alternatively you could use something other than the calendar. eg a small userform with comboboxes for day/date/year or just one combobox which lists the dates in column A.
0
rtod2Author Commented:
Assistance would be great.
0
dlmilleCommented:
Rather than using a Calendar control which doesn't work with 64 bit Excel 2010: re: MSFT:

Calendar control (mscal.ocx) was a Microsoft Access feature that could be used in Access worksheets. Calendar control is removed in Access 2010 and is not usable for Excel 2010. Instead, users can use Date Picker or their own custom calendar controls.

Have you tried using the Date & Time Picker instead?

See attached where I've modified your worksheet to utilize that.

Use the RIGHT CLICK in Column A inside the table to activate the DateTime Picker and the drop down to get the calendar up.

Dave
ugly-code-r1.xlsm
0
rtod2Author Commented:
dlmille,
Thank you. Still compile errors no matter where I click.  Here is the video http://screencast.com/t/Zzn0DtuswDOh
0
dlmilleCommented:
Excellent.

Sorry about that.  Here's what you need to have in your library:  MSFT Windows Common Controls.

So exit debugger, go to VBA, Tools-References and find the references, check them.

See image:
library references
Dave
0
NorieVBA ExpertCommented:
Dave

Does that library definitely have the calendar control?

Like I posted earlier I've been working with a download file sitting in a local folder.

Need to check it out.
0
dlmilleCommented:
This solution uses the Date&Time picker, not the calendar control.

Dave
0
NorieVBA ExpertCommented:
Oops, missed that.
0
dlmilleCommented:
That's ok.  I'm creating a new post from an Excel 64 bit office and 64 bit Windows 7 (which I should have done at the start).  That's what you have, right rtod?

Dave
0
dlmilleCommented:
rtod - I need your confirmation on the control.  Do you have Excel 64 bit installed, or Excel 32 bit (I know you say you have 64 bit Windows 7, but need to confirm Excel version).

PS - check the references and that should make the compile error go away.

Dave
0
rtod2Author Commented:
dlmillle,
Yes, I have 64bit Windows 7 with Excel 2010.
0
dlmilleCommented:
Ok.  Go to Excel 2010, hit HELP under Excel options and tell me what version of Excel 2010 you have - 32 or 64 bit.

Dave
0
rtod2Author Commented:
0
dlmilleCommented:
Looks like we have the same problem with the Date & Time picker.  This really is a better solution for what you're trying to do versus the calendar control, but we either need to find a MSFT version of the control that will work with 64 bit Excel, or a freeware version.  I'm searching now and will be testing on my 64 bit implementation.

imnorie - if you can find the Date & Time picker control on the web, please advise.  The one I downloaded registers, but can't seem to pull it up in Excel.  I'm using this here: http://activex.microsoft.com/controls/vb6/mscomct2.cab based on this link:http://social.msdn.microsoft.com/Forums/en/sbappdev/thread/91cf3127-70fe-4726-8a27-31b8964430c5

For the calendar control, it appears you can use the Excel 2003/2007 version: http://answers.microsoft.com/en-us/office/forum/officeversion_other-customize/missing-calendar-control/03ad5d05-ca3f-4081-9989-e757223ebdde

However, I'd like to see if we can't get Ted setup with the Date & Time picker as its much more elegant in my opinion, as it works like a dropdown popup instead of a big fat calendar on the screen.

Dave
0
rtod2Author Commented:
Dave,
That would be fantastic. I hope we can find something. This is something I have wanted to do for a long time in Excel. I have no clue why they don't build stuff like this into the application.

Certain components like this just make sense to have inside the app.
0
dlmilleCommented:
I agree.  This may take a while, but I'll keep trying till I get it.  I have to break away for a few hours in a bit, but will keep dogging this.

Dave
0
dlmilleCommented:
After some testing to prove it to myself, common controls is not going to work on Office 64 bit.

It is probably easiest (as opposed to finding a freeware software) to just create a userform with three dropdowns - one each for Month/Day/Year.

There are probably folks who've already done this, but I built one on the fly to test out to see if you want to go in this direction.


It just does a popup - advises you MDY (the 3 fields are Month, Day, Year) and the current date that you're sitting on.  This all works if you right click on your table in column A.

So, download the attached, click your right mouse on column A, then you can change the date using the drop downs.  Sorry, its not a MONTH popup for you to select from, but perhaps the next best thing.  I'm afraid if we use some 3rd party picker, you'll have a hard time deploying your workbook for someone else to use - they'd have to go through the same trouble installing the 3rd party software, etc., so it seemed best to just create a userform for this.

Basically, when you right click on a cell in the table in column A, the userform is shown, loaded with the current (if there is one) date.  You can change that date and hit enter, or just click anywhere else and that cell will be updated.  There's VBA code that positions the userform on top of the cell, as well as hides the title bar to minimize the impact of all that "overhead" of the userform (HOWEVER, I've not activated that as yet, still trying to get form positioning to work in 64 bit).  Making it fit more tightly in the cell could be a lot of work, so I went the other way and displayed the date above the 3 comboboxes on the form, so you'd know what date was originally there when you clicked into the cell.

Dave
ugly-code-r21-64.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rtod2Author Commented:
Dave,
Thank you for all your hard work. That's a nice tool but not exactly what I was after.  Here is my video on it. http://screencast.com/t/6wYWK8iKA37.  What I am after may just not be possible.  I'll leave this here for a few days to see if anyone else comes up with anything.
0
dlmilleCommented:
Yea I hit it for a while trying to get the common controls to work (that have the calendar/date-time picker) and unfortunately, MSFT did not provide these for 64 bit Excel, so unless you go with a 3rd party tool that others would have to install as well, unfortunately we're at a loss.  And it is a loss, I agree.

Dave
0
rtod2Author Commented:
Thank you Dave for such a valiant effort.  I'll ask that the thread remain open for a while just in case someone else has a better choice for a calendar control that hasn't already been presented.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.