vba 在组合框下拉列表中格式化日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21443409/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Formatting dates in a combobox dropdown list
提问by satch-23
I have created a simple userform with a combobox populated with a range of dates (rngWeekList) but I am having serious headaches trying to get the list in the dropdown box to appear in "dd-mmm-yy" format. Here is my code:
我创建了一个简单的用户表单，其中包含一个填充了一系列日期 ( rngWeekList)的组合框，但我在尝试让下拉框中的列表以“dd-mmm-yy”格式显示时遇到了严重的麻烦。这是我的代码：
Private Sub UserForm_Initialize()
' Populate the list with the date range
ComboBox1.List = Worksheets("Cover").Range("rngWeekList").Value
' Set the defulat selection (based off rngWeekIndex)
ComboBox1.ListIndex = Worksheets("Cover").Range("rngWeekIndex").Value - 1
' Format
ComboBox1 = Format(ComboBox1, "dd-mmm-yy")
End Sub
Private Sub ComboBox1_Change()
' Format
ComboBox1 = Format(ComboBox1, "dd-mmm-yy")
End Sub
This manages to format the selected item in the combobox correctly (e.g. "02-Jul-14") but when I open the dropdown list, all the list entries shown are formatted in the default "m/d/yyyy". Is there a way to change the formatting for the list entries? It is confusing for users who are used to seeing the day before the month.
这设法正确格式化组合框中的所选项目（例如“02-Jul-14”），但是当我打开下拉列表时，显示的所有列表条目都以默认的“m/d/yyyy”格式设置。有没有办法更改列表条目的格式？对于习惯于看到前一天的用户来说，这是令人困惑的。
Thanks in advance for your help, it is much appreciated.
在此先感谢您的帮助，非常感谢。
Ed
埃德
采纳答案by satch-23
I managed to fix it by looping through each item in the comboboax and formatting it (feel free to correct me if there is a more elegant way to do it!)
我设法通过循环组合框中的每个项目并对其进行格式化来修复它（如果有更优雅的方法，请随时纠正我！）
Private Sub UserForm_Initialize()
Dim i As Integer
' Populate the list with the date range
ComboBox1.List = Worksheets("Cover").Range("rngWeekList").Value
'Format all items
For i = 0 To ComboBox1.ListCount - 1
ComboBox1.List(i) = Format(DateValue(ComboBox1.List(i)), "dd-mmm-yy")
Next i
' Set the default selection (based off rngWeekIndex)
ComboBox1.ListIndex = Worksheets("Cover").Range("rngWeekIndex").Value - 1
End Sub
Private Sub ComboBox1_Change()
' Format the selection
ComboBox1 = Format(ComboBox1, "dd-mmm-yy")
End Sub
Sorry for posting, but I really thought I was stuck.
很抱歉发帖，但我真的以为我被卡住了。
Thanks again,
再次感谢，
Ed
埃德
