Disable "Could not load object because it is not available on this machine" Pop-Up.

[kermit_d_frog]

Hello,

I have a workbook that uses the Microsoft Date and Time Picker control. However some users do not have this control installed on their systems. (I cannot install it because I do not have admin access to do so.) When those users open the workbook or click on the button to load the user form containing the Date/Time Picker control they get this error:

"Could not load an object because it is not available on this machine."
excel alert.jpg

The workbook can still function fine without this control, it's just an easier way for the users to select the dates. (They can manually enter them instead of using the Date/Time picker if they have to.) Is there any way I can just prevent this alert from appearing? I've tried using application.displayalerts=false in the workbook open event and in the code that shows the userform and it doesn't work. Any help is appreciated!

[BadlySpelledBuoy]

Hi kermit and welcome.

What about using a custom date picker? Basically a userform designed to look similar to and work like the date picker control but works on any machine without the need for any specific install.

There are plenty around you can pinch for free. Ron de Bruin has one (I think) on his website. I may even have one laying about I could dig out for you.

BSB

[kermit_d_frog]

I looked into some other custom calendars and was a little concerned about the security of using these on the company share drives, especially the ones that have links to websites and donate buttons etc. We have a pretty strict privacy policy. If you can recommend a safe custom calendar without any ads or links to outside sites, I would consider using it. I also had some trouble customizing some of the code on the custom calendars that I did come across(I'm pretty new to excel VBA), but would be willing to try again if I could find a good one.

[rorya]

Alternatively, if you add the control at runtime, you can error handle that.

[kermit_d_frog]

Well the error comes up as soon as I open the workbook. I'm assuming because Excel tries to load all the add-ins attached to the file on file open. So you're saying if I put some error handling in a workbook open event I could bypass that message? Do you have a quick example or some reference to what that might look like? As I said my VBA knowledge is fairly basic. I haven't used much error handling in any of my code unfortunately except for basic "on error resume next" or "on error goto 0" codes. I appreciate all of the help thus far. Thanks!

[rorya]

I'm assuming you added the control to your form at design time. If so, you will have to remove it, and then have code in the form to add it when the form loads, handling the error in the case that loading it fails (eg a simple On Error Resume Next, then test if Err.Number is not 0). Depending on what other code you have hooked up to the DT picker, you may have to rewrite some other things too but I can't comment on that without a workbook.

[BadlySpelledBuoy]

We'd need far more info on what is being loaded and when to actually help. Feel free to attach a sample workbook.

I agree with Rory's approach given the info provided so far.

Look through my past posts and you'll find a custom calendar control that's written with the VBA beginner in mind. i.e. nothing baffling!

If you cannot find it, I'll happily dig a copy out and attach it.

BSB

[kermit_d_frog]

Sorry for the late response I've been a bit busy. Here is a bare bones example of what I need the control to do. I like the suggestion of code at startup to load the control, and if it can't be loaded then error handle it to open workbook without it and avoid the pop-up messages. It would also have to disable the macros attached to the purple rectangles as well, though. If you have a basic example of what that code could look like that would be very helpful.

[nigelog]

Attached is a datepicker that I have used for many years now without incident. I gave up on the default datepicker. See if its easier to incorporate this

[kermit_d_frog]

All,

I've managed to solve this issue by using the Calendar Control instead of the Microsoft Date/Time Picker control. I came across one while browsing that acted like a pop-up calendar and altered it for my needs. (I don't remember exactly where I found it but attached the .zip file in case anyone has a use for it. I also attached an example workbook after I altered the calendar to my needs.) So far all of the users have the calendar control installed on their systems and it is working properly. Thank you all for your help and thank you nigelog for the date picker. I have saved it for possible future use.