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
 
Sort by date Sort by votes
this also works, when pasted to the ThisWorkbook area (no idea why though):

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Add
ActiveWindow.Close
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Thanks mubashiraziz: Thanks, your solution (workaround) worked for me too. :)

I'm using Excel 2010 (Microsoft Office 2010 Home & Student Trial).
Seems strange that this issue has been around since 2004 & yet it's still a bug in Excel 2010!!!
 
Upvote 0
Here's the code I'm using... per mubashiraziz. I found it still had problems after opening the workbook, so I also added it to the Worksheet_Activate too. So far so good... may also need to use it on Worksheet_Change if still a problem.

Module1:
Code: 
Public Sub FixDTPickers()

On Error GoTo FixDTPickers_Err

' A workaround for the DTPicker's not displaying correctly per mubashiraziz in
' http://www.mrexcel.com/forum/showthread.php?t=70813&highlight=DTPicker
Application.ScreenUpdating = False
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Application.ScreenUpdating = True

'Exit Handler
FixDTPickers_Exit:
    On Error Resume Next
    Application.ScreenUpdating = True
    Exit Sub

'Error Handler
FixDTPickers_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume FixDTPickers_Exit

End Sub

ThisWorkbook:
Code: 
Private Sub Workbook_Open()

Call FixDTPickers

End Sub

Sheet1 (or whever your dtpickers are):
Code: 
Private Sub Worksheet_Activate()

Call FixDTPickers

End Sub
 
Upvote 0
None of those solutions worked for me. I ended up going to Format Control, Properties and setting the Object Positioning to "Move and size with cells". I then added the following code in my Workbook_Active sub.

Worksheets("Daily Data").Rows(3).RowHeight = .Rows(3).RowHeight + 2
Worksheets("Daily Data").Rows(3).RowHeight = Master.Rows(3).RowHeight - 2

This just increases and decreases the height of the row that my control is on.

Good luck.
 
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
399
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,983
Messages
6,122,588
Members
449,089
Latest member
Motoracer88

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