Excel DatePicker (6.0/SP6) Moves On File Open

[Joel Marion]

I have an excel file that I have been working on bit by bit over a few years, that I use as a basic, personal task tracker. I've built in task/milestone differentiation and a Gantt chart that moves relative to the selected date. I created a few basic controls to set the Gantt chart view (back one week, back one day, today, forward one day, forward one week), and I set up a date picker to set the view to any date of the user's choosing.

I recently moved to Office 2010, and now I'm having a problem with the Date Picker. It keeps re-positioning itself to the top-left corner of the sheet when I open the file. I then have to enter "Design Mode," manually re-position it, then exit design mode, and everything works as it should. Specifically, the date picker is designed to hover roughly over H4:J5, but on load it positions itself over the row/column headings at the top-left, varying in size some times (some times it covers A1:C2, others it's A1:H7). The fix is the same every time.

I've tried re-sizing, re-positioning, re-building and changing properties of the date picker, but nothing seems to make it load in the right position or size.

Further complicating things, I have VBA code that lets me hide the first three rows (a "header" of sorts) for when I want to get down to business, so the date picker needs to be able to move with rows 4-5 so that it stays relative to the date control buttons (fwd, today, back).

The file is normally in .xlsm, but bytes.com won't let me upload that, so I've converted it to .xls. I have confirmed that all of the VBA functionality and the error persist in the .xls version of this file (I mostly just want the extra layers of conditional formatting).

Is there VBA code that I could use to force it to a specific location, yet still have it move with the "hide header" control?

Thanks in advance for any consideration you can give to this problem.

[zmbd]

Joel Marion:
Looks like you were buried under a busy day, this sometimes happens - an unfortunate aspect of the web. :)

With Office 2010, they have dropped support for that version of the date picker.Replacing the Calendar Control in Access 2010 Applications What you've experienced has been the more benign events... I've read where the applications have crashed, the PC BSOD, and other more frustrating events; however, it seems that normally people are simply receiving a broken library error.

There are various workarounds listed around the net that will allow the use of the old control and third party add-ins; however, IMHO, if you need this functionality, then in the long run it will be less heartburn to adopt one of the conventions as given in the link.

As for anyone d/l your attached file. You'll find that very few of the Experts/Mods will do so for an unrequested attachment. This has to do more with Good/Safe computing practice and should never be taken as a personal reflection. Plain and Simple, good people have Trojans on their PC quite often and never know it! Therefor, it is always best (preferred) to post the pertinent portion of the script/code formatted with the [Code/] button when the code is suspect.

best regards
z

[WallyZ21]

I had the same issue so in "ThisWorkBook" module I added the following code:

Private Sub Workbook_Open()
Worksheets("Whatever sheet").DTPicker2_Name.Visible = False
Worksheets("Whatever sheet").DTPicker2_Name.Top = 0
Worksheets("Whatever sheet").DTPicker2_Name.Left = 0
Worksheets("Whatever sheet").DTPicker2_Name.Width = SomeWidth
Worksheets("Whatever sheet").DTPicker2_Name.Height = SomeHeight
Worksheets("Whatever sheet").DTPicker2_Name.Visible = True
Worksheets("Whatever sheet").DTPicker2_Name.Top = WhereYouWantItTop
Worksheets("Whatever sheet").DTPicker2_Name.Left = WhereYouWantItLeft
Worksheets("Whatever sheet").DTPicker2_Name.Width = SomeWidth
Worksheets("Whatever sheet").DTPicker2_Name.Height = SomeHeight
End Sub

The respective values I grabbed from the actual DatePicker control properties window while in Developer mode.