在初始化和更改其他组合框时设置组合框值 [英] Setting combobox values on initialisation and change of other comboboxes

问题描述

（编辑：澄清，我正在运行Excel 2013，因此Microsoft的日期选择器不可用。）

我试图编写一个简单的日期选择器 - 它会变得整齐，当它完成时，它只是大的简单，而我建立它 - 一切填充，因为它应该：

  Me.Combo_Year.List = wsLU.Range（Date_Years）。 Combo_Month.List = wsLU.Range（Date_Months）。Value 
 Me.Combo_Day.List = wsLU.Range（Date_Days31）。
  

但是，有两个实例，我想为年，月和日组合框设置默认值。对于我使用旋转按钮，其中一个简单的 .Value 语句将它们设置为中午12时在 _Initialize 。但是 .Value 或 .Text 都不适用于组合框：

  Me.Combo_Year.Text = Year（Now（））'不起作用
 Me.Combo_Month.Text = Month（Now（））' 
 Me.Combo_Day.Text = Day（Now（））'不工作
 Me.Spin_Hour.Value = 12'工作正常
 Me.Spin_Minute.Value = 0'工作罚款
  

类似地，当我选择一个较少天数的月份（例如避免返回2月31日）时，我尝试将日期设置为较低的值， .Value 和 .Text 再次证明无益：

< a>

有没有办法可靠地设置默认值，然后更改代码中组合框的值？

编辑：为了参考，表格相关部分的完整代码（ UpdatePreview 只需更新确认按钮上方的预览日期）：

  ----------- -------------------------------------------------- ------------------- 
 
选项显式
 
 ------------- -------------------------------------------------- ----------------- 
 
 Private Sub UserForm_Initialize（）
 Dim wsLU作为工作表，wbV as Workbook 
设置wbV = ActiveWorkbook 
设置wsLU = wbV.Worksheets（常规查找）
 
 Me.Combo_Year.List = wsLU.Range（Date_Years）。
 Me.Combo_Month.List = wsLU.Range（Date_Months）。Value 
 Me.Combo_Day.List = wsLU.Range（Date_Days31）。Value 
 Me.Combo_Minute.AddItem 0 
 Me.Combo_Minute.AddItem 30 
 
'尝试通过变量而不是直接将日期数字放在各种数据类型上
 Dim TestYear As String，TestMonth As String，TestDay As String 
 TestYear = Year（Now（ ））
 TestMonth = Month（Now（））
 TestDay = Day（Now（））
 Lab_T_Year.Caption = TestYear 
 Lab_T_Month.Caption = TestMonth 
 Lab_T_Day。 Caption = TestDay 
'Me.Combo_Year.Text = TestYear'如果这些行被注释掉，表单将加载，虽然没有预先填充组合框
'Me.Combo_Month.Text = TestMonth'如果这些行被注释out表单将加载，虽然没有组合框预填充
'Me.Combo_Day.Text = TestDay'如果这些行注释掉表单将加载，虽然没有组合框预填充
 
'原始码;尝试这两个有和没有各种格式类型。 
'Me.Combo_Year.Value = Format（Year（Now（）），0000）
'Me.Combo_Month.Value = Format（Month（Now（）），00）
'Me.Combo_Day.Value = Format（Day（Now（）），00）
 Me.Spin_Hour.Value = 12 
 Me.Combo_Minute.Value = 0'切换分钟微调一个组合框作为客户端想要只是选择半小时（00/30）而不是分钟
 
 UpdatePreview'更新日期和时间预览，工作正常。 
 End Sub 
 
 ------------------------------------ -------------------------------------------- 
 
 Private Sub Combo_Year_Change（）'Combo_Month_Change有一个等价的sub，基本上镜像这个
 Dim wsLU作为工作表，wbV作为工作簿
设置wbV = ActiveWorkbook 
设置wsLU = wbV.Worksheets（一般查找）
 
 Dim iMonthNo As Integer，iYearNo As Long，iMaxDate As Integer 
 
'设置基于月份的天数
 iMonthNo = Me.Combo_Month。 ListIndex + 1 
 iYearNo = Me.Combo_Year.Value 
如果iMonthNo = 1或iMonthNo = 3或iMonthNo = 5或iMonthNo = 7或iMonthNo = 8或iMonthNo = 8或iMonthNo = 10则
 Me.Combo_Day.List = wsLU.Range（Date_Days31）。值
 iMaxDate = 31 
 ElseIf iMonthNo = 4或iMonthNo = 6或iMonthNo = 9或iMonthNo = 11然后
 .Combo_Day.List = wsLU.Range（Date_Days30）。值
 iMaxDate = 30 
 ElseIf iMonthNo = 2然后
 Me.Combo_Day.List = wsLU.Range（Date_Days28）。价值
 iMaxDate = 28 
'闰年div by 4 
如果iYearNo / 4 = Int（iYearNo / 4）和Not（iYearNo / 100 = Int（iYearNo / 100） Combo_Day.List = wsLU.Range（Date_Days29）。如果iYearNo / 4 = Int（iYearNo / 4）和Not（iYearNo / 100 = Int（iYearNo / 100））then iMaxDate = 29 
'闰年除以400 
如果iYearNo / 4 = Int（iYearNo / 4）和iYearNo / 400 = Int（iYearNo / 400）则Me.Combo_Day.List = wsLU.Range（Date_Days29）。 
如果iYearNo / 4 = Int（iYearNo / 4）并且iYearNo / 400 = Int（iYearNo / 400）则iMaxDate = 29 
结束如果
 
尝试更改的代码如果月切换到具有较少天的日期，则日期下降。它不工作。 
如果Me.Combo_Day.Value> iMaxDate和iMonthNo> 0而不是我.Combo_Day.Value =然后Me.Combo_Day.Value = iMaxDate 
 
 UpdatePreview'更新日期和时间预览，工作正常。 
 End Sub 
  

无法使用的问题 / p>

• 添加Microsoft自己的日期选择器（在Excel 2013中不可用）


• 安装补充日期选择器（不能假定其在用户的计算机上可用）


• 尝试直接设置组合框 .Text
  
• 直接尝试使用日期（ = = 通过变量（ = sNowMonth ）或列表索引（ Me.Combo_Month。 Text = Me.Combo_Month.List（Month（Now（）） - 1））。



I自上周以来一直在寻找一个解决方案。我发现的每个可能性是对于较旧版本的Office。

解决方案

要直接回答您的问题，您可以创建一个日期变量，然后将其转换为文本字符串：

  Dim txtNowYear As String 
 Dim txtNowMonth As String 
 Dim txtNowDay As String 
 
 txtNowYear = Year（Now（））
 txtNowMonth = Month（Now（））
 txtNowDay = Day（Now（））
 
 Me.Combo_Year.Text = txtNowYear 
 Me.Combo_Month.Text = txtNowMonth 
 Me.Combo_Day.Text = txtNowDay 
  

但是根据您打算使用它的方式，只需将输入格式更改为.Date

转到工具，附加控制，选择Microsoft Monthview控制6.0（SP6）并在您的表单中插入日期选择器。

PS：类似的方法应该能够处理您的第二个问题。

(EDIT: To clarify, I'm running Excel 2013, so Microsoft's date picker isn't available.)

I'm trying to code a simple date picker - it'll be tidier when it's done, it's just big for simplicity while I build it - and everything populates as it should:

Me.Combo_Year.List = wsLU.Range("Date_Years").Value
Me.Combo_Month.List = wsLU.Range("Date_Months").Value
Me.Combo_Day.List = wsLU.Range("Date_Days31").Value

However, there are two instances where I'd like to set default values for the year, month and day comboboxes. For the times I'm using spin buttons, where a simple .Value statement sets them to 12 noon in the _Initialize. But neither .Value nor .Text works for the comboboxes:

Me.Combo_Year.Text = Year(Now())  ' Doesn't work
Me.Combo_Month.Text = Month(Now())  ' Doesn't work
Me.Combo_Day.Text = Day(Now())  ' Doesn't work
Me.Spin_Hour.Value = 12  ' Works fine
Me.Spin_Minute.Value = 0  ' Works fine

Similarly, when I try to set the date to a lower value when a month with fewer days is selected (to avoid returning the 31st of February, for instance), both .Value and .Text prove unhelpful again:

Is there any way to reliably set a default value and later change the value of a combobox in code? Am I missing something hugely obvious?

EDIT: For reference, the full code for the relevant parts of the form (UpdatePreview just updates the preview date above the OK button) as requested:

--------------------------------------------------------------------------------

Option Explicit

--------------------------------------------------------------------------------

Private Sub UserForm_Initialize()
Dim wsLU As Worksheet, wbV As Workbook
Set wbV = ActiveWorkbook
Set wsLU = wbV.Worksheets("General Lookups")

Me.Combo_Year.List = wsLU.Range("Date_Years").Value
Me.Combo_Month.List = wsLU.Range("Date_Months").Value
Me.Combo_Day.List = wsLU.Range("Date_Days31").Value
Me.Combo_Minute.AddItem 0
Me.Combo_Minute.AddItem 30

' Tried putting the date numbers via variables instead of direct, with various data types
Dim TestYear As String, TestMonth As String, TestDay As String
TestYear = Year(Now())
TestMonth = Month(Now())
TestDay = Day(Now())
Lab_T_Year.Caption = TestYear
Lab_T_Month.Caption = TestMonth
Lab_T_Day.Caption = TestDay
'Me.Combo_Year.Text = TestYear    ' If these lines are commented out the form will load, though without the comboboxes prepopulated
'Me.Combo_Month.Text = TestMonth  ' If these lines are commented out the form will load, though without the comboboxes prepopulated
'Me.Combo_Day.Text = TestDay      ' If these lines are commented out the form will load, though without the comboboxes prepopulated

' Original code; tried this both with and without various Format types.
'Me.Combo_Year.Value = Format(Year(Now()), "0000")
'Me.Combo_Month.Value = Format(Month(Now()), "00")
'Me.Combo_Day.Value = Format(Day(Now()), "00")
Me.Spin_Hour.Value = 12
Me.Combo_Minute.Value = 0  ' Switched the minute spinner to a combobox as the client wanted to just pick half hours (00/30) instead of minutes

UpdatePreview  ' Updates date and time preview, works fine.
End Sub

--------------------------------------------------------------------------------

Private Sub Combo_Year_Change()  ' Combo_Month_Change has an equivalent sub that essentially mirrors this one
Dim wsLU As Worksheet, wbV As Workbook
Set wbV = ActiveWorkbook
Set wsLU = wbV.Worksheets("General Lookups")

Dim iMonthNo As Integer, iYearNo As Long, iMaxDate As Integer

' Set number of days based on month
iMonthNo = Me.Combo_Month.ListIndex + 1
iYearNo = Me.Combo_Year.Value
If iMonthNo = 1 Or iMonthNo = 3 Or iMonthNo = 5 Or iMonthNo = 7 Or iMonthNo = 8 Or iMonthNo = 10 Or iMonthNo = 12 Then
    Me.Combo_Day.List = wsLU.Range("Date_Days31").Value
    iMaxDate = 31
ElseIf iMonthNo = 4 Or iMonthNo = 6 Or iMonthNo = 9 Or iMonthNo = 11 Then
    Me.Combo_Day.List = wsLU.Range("Date_Days30").Value
    iMaxDate = 30
ElseIf iMonthNo = 2 Then
    Me.Combo_Day.List = wsLU.Range("Date_Days28").Value
    iMaxDate = 28
    ' Leap year div by 4
        If iYearNo / 4 = Int(iYearNo / 4) And Not (iYearNo / 100 = Int(iYearNo / 100)) Then Me.Combo_Day.List = wsLU.Range("Date_Days29").Value
        If iYearNo / 4 = Int(iYearNo / 4) And Not (iYearNo / 100 = Int(iYearNo / 100)) Then iMaxDate = 29
    ' Leap year div by 400
        If iYearNo / 4 = Int(iYearNo / 4) And iYearNo / 400 = Int(iYearNo / 400) Then Me.Combo_Day.List = wsLU.Range("Date_Days29").Value
        If iYearNo / 4 = Int(iYearNo / 4) And iYearNo / 400 = Int(iYearNo / 400) Then iMaxDate = 29
End If

' Code to attempt to change the date down if Month is switched to one with fewer days. It doesn't work.
If Me.Combo_Day.Value > iMaxDate And iMonthNo > 0 And Not Me.Combo_Day.Value = "" Then Me.Combo_Day.Value = iMaxDate

UpdatePreview  ' Updates date and time preview, works fine.
End Sub

THINGS WOT HAVEN'T WORKED:

• Adding Microsoft's own date picker (not available in Excel 2013)
• Installing the supplemental date picker as suggested by Microsoft (can't assume it'll be available on users' computers)
• Attempts to directly set the comboboxes' .Text or .Value properties through VBA, regardless of data type used.
• Attempts with dates directly (=Month(Now())), through variables (=sNowMonth), or by list index (Me.Combo_Month.Text=Me.Combo_Month.List(Month(Now())-1)).

I've been hunting around for a solution for this since last week. Every possibility I've found has been for older versions of Office. Can anyone help?

解决方案

To answer your question directly, then you could make a date variable and then convert that to a text string:

Dim txtNowYear As String
Dim txtNowMonth As String
Dim txtNowDay As String

txtNowYear = Year(Now())
txtNowMonth = Month(Now())
txtNowDay = Day(Now())

Me.Combo_Year.Text = txtNowYear
Me.Combo_Month.Text = txtNowMonth
Me.Combo_Day.Text = txtNowDay  

But depending on what you intend to use it for, it might be smarter to just change the input format to .Date

Go to Tools, Additional Control, select Microsoft Monthview Control 6.0 (SP6) And insert a date-picker in your form.

PS: A similar approach should be able to handle your second issue.

这篇关于在初始化和更改其他组合框时设置组合框值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！