Problem with Date/Time Picker in Excel 2000

S

sealions

New Member
Joined
Sep 5, 2002
Messages
19
Hi,

I'm trying to use the Date/Time ActiveX control in Excel 2000 and it is giving me problems. I am attempting to use this control contained directly on a worksheet (not contained on a separate form). My problem is that each time I start the workbook, the date/time control resizes itself to have an extremely large height. I set the control's height at 18 but upon loading it shows the height as about 5x that value. However, the properties of the control still show it listed with a height of 18 ?? No matter what I do, I cannot seem to find a way to have the control maintain its initial height. I've tried this in several different workbooks, and it does the same everytime. Any ideas about what is going on and how I might be able to correct this problem ?

Thanks,
Chris
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Thats odd alright. When you place an ActiveX control on a sheet it takes up some more properties as well. Right click and look under format control and there are options to amend the size under the Size and Properties tab. Perhaps its resizing to cell height/width?

PS: Have you got any workbook open events?
 
Upvote 0
Yes, it is VERY odd. I've spent quite a few hours chasing this now and it is getting old. Yes, I have set the Object Positioning properly under Format Control to 'Don't move or size with cells', but it had no effect.

Are you running 2000 or XP ? Either way, can you just verify that if you place a date/time picker control on a sheet, sized to 18, that it holds its size when you bring the workbook up ? This would at least help to verify that it is not an issue with containing the control directly on a worksheet in Excel. Thanks.

I'm wondering if I may have a corruption in MSCOMCT2.OCX. I'm going to try to reload it and see if that clears anything up.

Any other suggestions would be appreciated.

Thanks,
Chris
 
Upvote 0
Chris,

I believe it is by design. Not all ActiveX are designed to work with worksheets and I don't recall if DT-picker is shipped with Office or if it comes with VB or with the Office Developer version.

Anyway, I made some few tests and can only confirm it resizes everytime I open the workbook.

A workaround is to set the size by VBA in an open-statement:

Option Explicit

Private Sub Workbook_Open()
Dim dtPicker As OLEObject
Dim wsSheet As Worksheet

Set wsSheet = Me.Worksheets(1)
Set dtPicker = wsSheet.OLEObjects(1)

Application.ScreenUpdating = False

With dtPicker
.Height = 18
.Width = 105
End With

Application.ScreenUpdating = True

End Sub


Kind regards,
Dennis
 
Upvote 0
Hi, I didnt seem to have a problem with this in Excel 2002. I did notice that as the workbook opened the control changed into an image of a white background with a red X for a fraction of a second before appearing as normal. Its like the effect when you have placeholders for pictures in a browser.

I would follow Dennis advice and use the workbook open event to resize each time.
 
Upvote 0
parry,

FYI - I didn't have any problem in XL 2002 or 2003 only in 2000.

Kind regards,
Dennis
 
Upvote 0
Hi all,

I know this is a slightly* old thread, but it is the same problem i'm having with date and time pickers. Did anyone ever find a solution to this?
I'm useing Win and Office XP. To my knowledge it affects all versions of excel.

The reason those who tried to replicate it failed would most likely be because you created the control above the minimum size. Shrink the control to the size of the drop down button and it will resize to display the date fully on opening of the workbook.

The only way i currently get around this is by resizeing the sheet on opening the sheet. Does anyone have a more efficent way to get around this?
 
Upvote 0
Although its very old problem which was posted but i just came thru searching and just logged in to solve the problem ........ you can just paste this code and it will do the trick ....

Private Sub Workbook_Open()

Application.ScreenUpdating = False

ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1

Application.ScreenUpdating = True

End Sub
Click to expand...
 
Upvote 0
Hi,

I´m sill having problems in WinXP-spanish/sp3 + Off2k3-spanish/sp3 with DTPicker because it opens about an inch higher than registered height.

I tried mubashiraziz's solution without luck... maybe because of screen updating.

My solution was similar: zooming to 99% and unzooming to 100% in workbook open, but WITH screen updating.

BTW, if you know about a signed date picker that works with Excel, I'll be VERY glad to hear about it.

Thanx
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Code Help - MsgBox Does Not Appear/Date and Time Picker
Replies
0
Views
288
elenakotanchyan
E
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
395
AndyEd
A
M
  • Question
Date & Time Picker Inside a form with Excel 365
Replies
1
Views
1K
mecerrato
M
L
  • Solved
VBA Run-time Errors
Replies
3
Views
513
StephenCrump
S
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A

Forum statistics

Threads
1,214,914
Messages
6,122,211
Members
449,074
Latest member
cancansova

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top