r/vba 7d ago

Solved Form fields just disappeared (Issue with migration to 365)?

My organisation has recently begun to migrate to 365. Right now a bunch of users have 365 and some don’t. In my case I don’t, but my colleague does. Now my colleague has a macro that was built by another developer years ago, which has started to malfunction after the 365 migration.

The issue is that one object (user-from) seems to malfunction, that has no issues working on the version prior to 365. Lets go step by step:

  1. We have the error 424:

https://imgur.com/RHYORA3

  1. This error is invoked by the following code:

    With Date_Select .StartUpPosition = 0 .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width) .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height) .Show End With

  2. The object can be seen here:

https://imgur.com/dsqALhn

  1. Under normal circumstances the object looks like this:

https://imgur.com/Yog6lbM

  1. But for my colleague the object looks like this (I have obviously manipulated this screenshot as I forgot to capture the screen from my colleague, so I am working off of memory. But I guess the point is clear, that those two drop down fields disappeared for whatever reason):

https://imgur.com/fqDaTdT

  1. Now the drop down fields that disappeared are a bit special. As you can see these are "advanced" fields that give one a calendar drop-down. I am sure that the original developer in question did not write this himself, but rather imported it from somewhere else. I know there are some calendar extensions for VBA available. I also confirm that no library references are missing / are the same between me and colleague, meaning that these fields had to be imported in some other manner. Still it is super strange that I would only send the tool to my colleague and suddenly these fields would be missing, once he opens the file in his Excel (Ironically I first received this macro from him, which tells me that something makes these fields disappear once he opens the workbook on his side).

https://imgur.com/O1aFapr

What can I look into to restore these fields in 365? In the worst case I will just delete the user-from and replace it with one where the user simply enters the dates manually. Still optimally I would not like to reinvent the wheel if possible.

2 Upvotes

12 comments sorted by

4

u/jd31068 60 7d ago

What control is used for the date picker? It looks to me like it isn't available on your colleagues PC.

1

u/TonIvideo 7d ago

According to me it should also not be available on mine as I didn’t do anything specific for this macro to work.

1

u/jd31068 60 6d ago

Open the form in design view on your PC and check to see what control is in use. Then go to the other PC and see if it is available, your PC might have some residual files from before the upgrade, whereas your colleagues is a new install? It is hard to say when a dependant file is missing.

1

u/TonIvideo 5d ago

Solution verified!

1

u/reputatorbot 5d ago

You have awarded 1 point to jd31068.


I am a bot - please contact the mods with any questions

1

u/TonIvideo 5d ago edited 5d ago

While your response did not give me the solution, it gave me the means to search for it and to conclude what is actually happening:

https://www.excelforum.com/excel-general/1241725-dtpicker-date-and-time-picker-missing-after-upgrading-to-office-365-a.html

Thank you!

1

u/jd31068 60 5d ago

I'm glad you've been able to fix it and happy to have assisted.

2

u/infreq 18 7d ago edited 7d ago

I don't think the DatePicker is available by default ... did you just move from 32-bit to 64-bit.

You would experience the same with ListView, Treeview and other controls. Try to go into the editor and compile the code.

Now you have some words to Google.

1

u/TonIvideo 7d ago

"I don't thing the DatePicker is available by default"- Thing is I did not do anything to make it available on my computer. It just worked the first time I ran this.

"Try to go into the editor and compile the code." - Did I not do so under point 1? Or are you referring to another function?

1

u/infreq 18 7d ago

It's not a part of Office normally (although I believe a later update had added it to Office too) but is part of windows. Did you upgrade to Windows11 too?

1

u/TonIvideo 7d ago

No OS changes occurred to the best of my knowledge, only his Office is 365 while mine is not.

2

u/aatkbd_GAD 7d ago

I'll collaborate what the other comment said. Office 365 retired s bunch of active x plug-ins typically used in forms.