使用DTPicker或MonthView控件通过代码设置最小和最大日期 [英] Setting min and max dates via code with DTPicker or MonthView control

问题描述

我有一个Excel工作簿，可以从另一个工作簿加载带时间戳的数据，然后为用户提供选择数据子集的选项（按最小/最大日期），并使用它来创建图表和汇总表。

I have an Excel workbook that loads time-stamped data from another workbook and then gives the users options to select a subset of the data (by min/max date) and use it to create charts and summary tables.

加载数据后，我想使用日期范围的最小值和最大值为弹出日期选择器设置MinDate和MaxDate。

Once the data is loaded, I want to use the min and max of the date range to set the MinDate and MaxDate for pop-up date selectors.

我最初使用DTPicker控件在Excel 2010中构建它，这似乎在我的机器上正常工作，但是其他用户在控制时遇到问题，并且在尝试设置这些属性时代码中断了。

I originally built it in Excel 2010 using DTPicker controls, which seemed to work fine on my machine, but other users experienced problems with the control and the code broke when trying to set these properties.

其中一个用户拥有Excel 2007，所以我猜测我必须使用像MonthView控件这样的东西，因为DTPicker在2010年是新的（用户的机器无法在设计模式下访问DTPicker的属性）。  ;但似乎无法通过代码（在任何PC上）设置月视图的最小/最大
日期，并且每次尝试保存文件时都会添加弹出式隐私警告。

One of the users had Excel 2007, so I surmised that I'd have to use something like the MonthView control instead, as DTPicker was new in 2010 (the user's machine could not access DTPicker's properties in design mode).  But it appears that the min/max dates for MonthView can't be set by code (on any PC) and this adds a pop-up privacy warning every time you try to save the file.

有没有办法在便携式工作簿中使用这些控件中的任何一个（不需要安装程序或其他步骤来使用ActiveX控件），或者我坚持不得不在Userform中构建一个看起来像日历控件
的东西？

Is there a way to do this with either of these controls in a portable workbook (without requiring an installer or other steps to use the ActiveX controls), or am I stuck with having to build something that looks and works like a calendar control inside a Userform?

以下是在DTPicker示例的其他机器上生成错误的代码行：

Here are lines of code that generate the error on other machines for the DTPicker example:

   表（"开始"）minDatePicker.MinDate =范围（" MIN_DATE"）：表（"开始"）minDatePicker.MaxDate =范围（" MAX_DATE"）

 群组。 ; 表（"开始"）maxDatePicker.MinDate =范围（" MIN_DATE"）：片材（QUOT;开始"）maxDatePicker.MaxDate =范围（" MAX_DATE"）

    Sheets("Start").minDatePicker.MinDate = Range("min_date"): Sheets("Start").minDatePicker.MaxDate = Range("max_date")
    Sheets("Start").maxDatePicker.MinDate = Range("min_date"): Sheets("Start").maxDatePicker.MaxDate = Range("max_date")

推荐答案

Hi Pete，

Hi Pete,

>>其中一位用户拥有Excel 2007，因此我推测我必须使用类似MonthView控件的东西，因为DTPicker在2010年是新的。<<

>>One of the users had Excel 2007, so I surmised that I'd have to use something like the MonthView control instead, as DTPicker was new in 2010 .<<

MonthView控件和DateTime Picker控件都包含在Microsoft Windows Common Controls-2 6.0。

The MonthView control and DateTime Picker control are both inclued in the Microsoft Windows Common Controls-2 6.0.

>>（用户的计算机无法在设计模式下访问DTPicker的属性）。  <<

>>(the user's machine could not access DTPicker's properties in design mode).  <<

是否在用户的计算机上安装了Microsoft Windows Common Controls-2 6.0控件？据我所知，此控件（MSCOMCT2.OCX）不再包含在Microsoft Office XP Developer中。我建议您检查此控件是否已成功安装。
您可以从以下链接下载：

OFFXPDEV：MSCOMCT2.OCX不包含在Microsoft Office XP Developer 中

Was the Microsoft Windows Common Controls-2 6.0 control installed on the user's machine? As far as I know, this control(MSCOMCT2.OCX) is no longer included with Microsoft Office XP Developer. I suggest that you check whether this control was installed successfull. You can download it from link below:
OFFXPDEV: MSCOMCT2.OCX Is Not Included with Microsoft Office XP Developer

您可以按照以下链接重新调整活动控件：

如何手动注册ActiveX控件（.ocx）

And you can follow link below to regstry the active control:
How to register an ActiveX control (.ocx) manually

下面的链接也是hlelpful for我们使用activeX控件解析soltuion：

在安装中包含一个文件柜文件

Also link below is hlelpful for us destribute the soltuion with activeX control:
Including a Cabinet File in an Installation

>>但似乎无法通过代码设置月视图的最小/最大日期  （在任何PC上） 并且每次您尝试保存文件时都会添加弹出式隐私警告<<

>>But it appears that the min/max dates for MonthView can't be set by code (on any PC) and this adds a pop-up privacy warning every time you try to save the file<<

您是否介意与警告消息共享？据我测试，该属性适合我。

Would you mind sharing with the warning message? As far as I test, the property works well for me.

>>有没有办法在便携式工作簿中使用这些控件中的任何一个（无需安装程序或 ） ;其他步骤 使用ActiveX控件），还是我不得不在Userform内构建一个外观和工作方式的日历
控件？<<

>>Is there a way to do this with either of these controls in a portable workbook (without requiring an installer or other steps to use the ActiveX controls), or am I stuck with having to build something that looks and works like a calendar control inside a Userform?<<

如果我们使用ActiveX控件，如果控件没有安装在用户的机器上，我们需要分发它。

If we are useing ActiveX control, we need to distribut it if the control not installed on the users' machine.

>>以下是在DTPicker示例的其他计算机上生成错误的代码行：

>>Here are lines of code that generate the error on other machines for the DTPicker example:

   表（"开始"）minDatePicker.MinDate =范围（" MIN_DATE"）：表（"开始"）minDatePicker.MaxDate =范围（" MAX_DATE"）

&NBSP;群组。 ;&NBSP;表（"开始"）maxDatePicker.MinDate =范围（" MIN_DATE"）：表（"开始"）maxDatePicker.MaxDate =范围（" MAX_DATE"）<<
$ B $。 b该代码也适用于我，你有什么错误信息？我建议您检查MSCOMCT2.OCX是否已成功安装。

    Sheets("Start").minDatePicker.MinDate = Range("min_date"): Sheets("Start").minDatePicker.MaxDate = Range("max_date")
    Sheets("Start").maxDatePicker.MinDate = Range("min_date"): Sheets("Start").maxDatePicker.MaxDate = Range("max_date")<<
The code also works well for me, what did you got about the error message? I suggest that you check the whether MSCOMCT2.OCX is installed successfully.

祝您好运

Fei

这篇关于使用DTPicker或MonthView控件通过代码设置最小和最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！