Solved

Clean Ugly Code

Posted on 2012-04-06
24
244 Views
Last Modified: 2012-04-13
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
0
Comment
Question by:rtod2
  • 10
  • 9
  • 5
24 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 37817211
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
 

Author Comment

by:rtod2
ID: 37817246
Thank you sir for your comment.  Here is a video of what it does for me http://screencast.com/t/YBZNmm5K
0
 
LVL 33

Expert Comment

by:Norie
ID: 37817284
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
 

Author Comment

by:rtod2
ID: 37817336
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
 
LVL 33

Expert Comment

by:Norie
ID: 37817398
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
 

Author Comment

by:rtod2
ID: 37817776
Assistance would be great.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37818502
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
 

Author Comment

by:rtod2
ID: 37819745
dlmille,
Thank you. Still compile errors no matter where I click.  Here is the video http://screencast.com/t/Zzn0DtuswDOh
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37819759
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
 
LVL 33

Expert Comment

by:Norie
ID: 37819774
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
 
LVL 41

Expert Comment

by:dlmille
ID: 37819781
This solution uses the Date&Time picker, not the calendar control.

Dave
0
 
LVL 33

Expert Comment

by:Norie
ID: 37819789
Oops, missed that.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 41

Expert Comment

by:dlmille
ID: 37819792
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
 
LVL 41

Expert Comment

by:dlmille
ID: 37819816
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
 

Author Comment

by:rtod2
ID: 37819846
dlmillle,
Yes, I have 64bit Windows 7 with Excel 2010.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37819848
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
 

Author Comment

by:rtod2
ID: 37819880
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37819937
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
 

Author Comment

by:rtod2
ID: 37819951
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
 
LVL 41

Expert Comment

by:dlmille
ID: 37819955
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
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37822483
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
 

Author Comment

by:rtod2
ID: 37823759
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
 
LVL 41

Expert Comment

by:dlmille
ID: 37825821
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
 

Author Comment

by:rtod2
ID: 37828172
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now