Solved

EXCEL FORM DATE PICKER ERRORS

Posted on 2007-11-22
5
963 Views
Last Modified: 2013-12-25
Experts,

I have produced some vb script that involves some date pickers. Before i send the code to colleagues, i have been trying the code out with other users and have found that i am having sever problems with the 'DATE PICKER' control with other users. For example, the following code returns an error when other users have used it - it is part of the userform intiliastion:

save_date_button.Enabled = False
Date_Picker.CustomFormat = Chr(32)<========= error
Date_Picker.Enabled = False

When i commented this out on the other users machine - and then attempted to use the date picker - another error was thrown out on the date picker mousedown event:

Private Sub Date_Picker_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As stdole.OLE_XPOS_PIXELS, ByVal Y As stdole.OLE_YPOS_PIXELS)
Dim a As Boolean
a = X > (Date_Picker.Width - 285) 'a = true if mouse is over the grey DTPicker button
If a = False Then Exit Sub
If Button <> 1 Then Exit Sub 'allow only left mouse button
If Date_Picker.Format <> dtpLongDate Then Call MakeDtpLongDate<============= ERROR
End Sub

it is like other users are not formatted to use the date pickers - yet the versions of excel they are using are exactly the same! So confused, could anyone please tell me why this is happening?

Thanks

Simon
0
Comment
Question by:simondopickup
  • 3
  • 2
5 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20334240
The Date and Time Picker control is not a standard Excel control. If you have VB6 (or Visual Studio) installed on your machine that may be where you got it from, in which case you need to package it up with the workbook in an installation routine, assuming you have a redistributable version and licence to do so.
Regards,
Rory
PS I assume you are not talking about the Calendar control? (which comes with MSAccess)
0
 

Author Comment

by:simondopickup
ID: 20334259
So this is not something i can set or have installed as the workbook opens?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20334320
You need to distribute the actual control as it physically does not exist on most computers - it's not just a question of setting a reference. You need to copy the control and register it on each machine. (one reason I never use Date Pickers or Calendar controls)
0
 

Author Comment

by:simondopickup
ID: 20334974
OKay well i have a worksheet that contains 4 date pickers at present. I am going to send this to to lots of colleagues in the states and i dont want the thing to be throwing out errors as soon as they compile it. What would you suggest?
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 20335234
Do you have a copy of VB which entitles you to distribute the control? If so, you will need to create an installation package that includes the .ocx file for the Date Time Picker and distribute that.
Alternatively, remove the controls and let them enter dates manually.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Make a Cell act like a Date 7 39
Excel IF formula 3 20
Excel. How to get via VBA the last used row in a named dynamic range 10 36
Msgbox tickler 10 23
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 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