Date Formatting Within A Combo Box On A Userform

[Div]

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

 

[Nick Hodge]

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
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Div via OfficeKB.com]

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

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

 

[Nick Hodge]

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
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

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

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

 

[Div via OfficeKB.com]

Hi Nic,

Thanks for coding. I will over the weekend add the code and let you know how
it goes.

Regards - Div

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]

 

[Div via OfficeKB.com]

Hi again,

I have just entered the code and am getting an error code 70 - "Permission
Denied". Any ideas

Reagrds

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

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

[quoted text clipped - 31 lines]

 

[Div via OfficeKB.com]

The error was down to me. I realised that I hadn't removed the "row source"
data. This done everything works as I had hoped - thank you so much.

Kind regards - Div

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