Need Scroll VBA code to use with Microsoft DT Picker control

[johns_st]

Using Excel 2010, I am using the Microsoft DT Picker control on certain sheets within my workbook. The workbook is setup to automatically open on sheet "Start" regardless of which sheet the workbook was last saved on. When reopening file and I select a sheet that utilizes the DT Picker control, I have to scroll down and then back up to enable the DT Picker control box to be active. I need a VBA code that will automatically scroll down and then scroll back up to the top.

Thank you in advance for any assistance provided!

 

[Michael Simpson]

I have to scroll down and then back up to enable the DT Picker control box to be active

Sorry - not sure what you mean there. Is the DT picker imbedded in a userform or what ....?

What should the VBA code actually scroll up and down in

 

[johns_st]

Sorry - not sure what you mean there. Is the DT picker imbedded in a userform or what ....?

What should the VBA code actually scroll up and down in

The DT Picker Control is embedded in multiple cells within the worksheet. Once a workbook is saved and reopened, the DT Picker Control is distorted and not selectable. The only way to enable the control is to scroll the worksheet down and back up. It's a glitch that has been known for quite some time but has never been fixed by Microsoft. So I was hoping to install a VBA code that would scroll the worksheet down and then back up once opened (it only has to scroll 1 row to reactivate the control).

 

[Michael Simpson]

As always, try recording a macro to do what you want. I created a new workbook with about 30 rows, recorded a macro, then paged down and then up. I got the following (which I've "translated" to what I think you want)

Application.ScreenUpdating = False
Range("A2").Select
Range("A1").Select
Application.ScreenUpdating = True

Does this do the trick? (You might have to remove the Application.ScreenUpdating lines. Since I don't use DT pickers on cells, only in user forms, I have no idea whether the code above will solve the problem)

 

[johns_st]

As always, try recording a macro to do what you want. I created a new workbook with about 30 rows, recorded a macro, then paged down and then up. I got the following (which I've "translated" to what I think you want)

Application.ScreenUpdating = False
Range("A2").Select
Range("A1").Select
Application.ScreenUpdating = True

Does this do the trick? (You might have to remove the Application.ScreenUpdating lines. Since I don't use DT pickers on cells, only in user forms, I have no idea whether the code above will solve the problem)

Thank you for your assistance but unfortunately this did not work. As far as recording a macro, this would work if I was the only user, however this workbook is a template that is installed on a department server for multiple employees to use. Some terminals in are restricted by our IT division from having personal macros installed.

 

[Michael Simpson]

I'm a self-taught VBA programmer, but your original topic header was

Need Scroll VBA code to use with Microsoft DT Picker control

Your last reply gives the impression that the IT dept won't allow any VBA macros,or am I missing something ?

 

[johns_st]

I'm a self-taught VBA programmer, but your original topic header was


Your last reply gives the impression that the IT dept won't allow any VBA macros,or am I missing something ?

I am a little new to VBA and macros. If I understand correctly, you can write code in the VBA section by opening via ALT +F11 and you can also record a macro which can be saved in your personal macro folder. Personal macro folders are not allowed on some terminals due to security issues (so they say) however VBA written the editor (ALT + F11) is acceptable on all terminals. I hope this clarifies things.

 

[Michael Simpson]

I always get nervous now (with what I'm going to write), since, as a newbie, I might be saying something that an experienced person would laugh at.
However, here goes .....

If I understand correctly, you can write code in the VBA section by opening via ALT +F11 and you can also record a macro which can be saved in your personal macro folder. Personal macro folders are not allowed on some terminals due to security issues (so they say) however VBA written the editor (ALT + F11) is acceptable on all terminals.

Anyone can write macros by pressing alt+f11 as you say.
If they then save the file as an xlsm file then, de facto, the file contains macros (the m at the end).
They can then distribute this to anyone, but as soon as the recipient saves the files and tries to open it (in 2007) they see a line just above the column headers indicating that the file contains macros, which have been disabled unless they save the file to a trusted location or it comes from a trusted publisher.
Your comment above about a personal folder would be incorrect I believe. Try writing a macro, save it as an xlsm file to your desktop. Close it, and then reopen the desktop xlsm file.
Unless your desktop is trusted, those macros can't be run by you when you reopen the file. (Try it to check that what I say is correct)
Whether they then choose to allow these macros to run is up to them.

I believe also that your comment about specific terminals is also incorrect. It's all about whether the xlsm file is being loaded from a trusted location/publisher. Theoretically, you could go to management and faff around getting/setting up a certificate (read about it, I played with it at one time, but didn't follow t rhough).
This means that your xlsm files are "certified" as being reliable, ie, a trusted publisher. (Think about it, when you download files to your PC and run the exe file to install, you get that message saying that the publisher is Microsoft (maybe) and do you want to continue. Other times you'll get a message that the publisher is unknown and do you want to continue. It's the same principle.

I hope this clears things up (and above all, I hope all my comments are correct)