Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formatting Within A Combo Box On A Userform
Hi All,
Using Excel 2007 I have a combo box with a row source of dates linked to a column of worksheet cells. The cells on the worksheet are UK formatted as dd/mm/yy and appear as such. The form opens and when a user clicks on the combo box the dates can be viewed correctly - so far so good. The problem I need help with is when the user (me) selects a date from the list it appears as a date serial number and not in the anticipated format. Any help with this would be greatly appreciated. Regards - Div |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formatting Within A Combo Box On A Userform
Div
It appears as a serial date where? If it is on a worksheet just format it, if it arrives into a variable in your code you may need to specifically parse it, e.g Private Sub ComboBox1_Change() Dim myUKDate As String myUKDate = Format(Me.ComboBox1.Value, "dd/mm/yyyy") MsgBox myUKDate End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.excelusergroup.org web: www.nickhodge.co.uk "Div" <u43652@uwe wrote in message news:844d1c0356ed4@uwe... Hi All, Using Excel 2007 I have a combo box with a row source of dates linked to a column of worksheet cells. The cells on the worksheet are UK formatted as dd/mm/yy and appear as such. The form opens and when a user clicks on the combo box the dates can be viewed correctly - so far so good. The problem I need help with is when the user (me) selects a date from the list it appears as a date serial number and not in the anticipated format. Any help with this would be greatly appreciated. Regards - Div |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formatting Within A Combo Box On A Userform
Many thanks Nick. Being new to any form of programming I had pondered over
the solution for many weeks. About 2 hours after posting my original message I got to the conclusion with regards the use of formatting. However your solution is far more elegant and efficient so again thanks. If it's not too much of a cheek what I really had desired was the following (again within Excel 2007): On a userform I would like to populate a combobox with dates for the previous 28 days starting from the current date (i.e. todays date) but absent from that list is any day which is a Sunday - all via VBA. Any ideas? Regards - Div Nick Hodge wrote: Div It appears as a serial date where? If it is on a worksheet just format it, if it arrives into a variable in your code you may need to specifically parse it, e.g Private Sub ComboBox1_Change() Dim myUKDate As String myUKDate = Format(Me.ComboBox1.Value, "dd/mm/yyyy") MsgBox myUKDate End Sub Hi All, [quoted text clipped - 10 lines] Regards - Div -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200805/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formatting Within A Combo Box On A Userform
Div
You can use the Initialize() event of the userform like so (Change the name of the combobox to the name you have) Private Sub UserForm_Initialize() Dim cb As ComboBox Dim x As Integer, iDayNo As Integer Set cb = Me.ComboBox1 For x = 0 To 27 iDayNo = Application.WorksheetFunction.Weekday(Date - x, 2) If iDayNo < 7 Then cb.AddItem Format(Date - x, "dd/mm/yyyy") End If Next x cb.ListIndex = 0 End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.excelusergroup.org web: www.nickhodge.co.uk "Div via OfficeKB.com" <u43652@uwe wrote in message news:845669fb928b0@uwe... Many thanks Nick. Being new to any form of programming I had pondered over the solution for many weeks. About 2 hours after posting my original message I got to the conclusion with regards the use of formatting. However your solution is far more elegant and efficient so again thanks. If it's not too much of a cheek what I really had desired was the following (again within Excel 2007): On a userform I would like to populate a combobox with dates for the previous 28 days starting from the current date (i.e. todays date) but absent from that list is any day which is a Sunday - all via VBA. Any ideas? Regards - Div Nick Hodge wrote: Div It appears as a serial date where? If it is on a worksheet just format it, if it arrives into a variable in your code you may need to specifically parse it, e.g Private Sub ComboBox1_Change() Dim myUKDate As String myUKDate = Format(Me.ComboBox1.Value, "dd/mm/yyyy") MsgBox myUKDate End Sub Hi All, [quoted text clipped - 10 lines] Regards - Div -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200805/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formatting Within A Combo Box On A Userform
Hi Nic,
Thanks for coding. I will over the weekend add the code and let you know how it goes. Regards - Div Div wrote: Many thanks Nick. Being new to any form of programming I had pondered over the solution for many weeks. About 2 hours after posting my original message I got to the conclusion with regards the use of formatting. However your solution is far more elegant and efficient so again thanks. If it's not too much of a cheek what I really had desired was the following (again within Excel 2007): On a userform I would like to populate a combobox with dates for the previous 28 days starting from the current date (i.e. todays date) but absent from that list is any day which is a Sunday - all via VBA. Any ideas? Regards - Div Div [quoted text clipped - 12 lines] Regards - Div -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200805/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Userform | Excel Programming | |||
~~ Help with Userform and Combo Box ~~ | Excel Programming | |||
Userform Date Formatting MM/DD/YYYY | Excel Programming | |||
UserForm text box Date formatting | Excel Programming | |||
Combo Box in userform | Excel Programming |