Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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
0
rtod2
Asked:
rtod2
  • 10
  • 9
  • 5
1 Solution
 
NorieCommented:
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
 
NorieCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
NorieCommented:
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
 
NorieCommented:
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
 
NorieCommented:
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now