Date Formatting Within A Combo Box On A Userform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Div - 17 May 2008 20:52 GMT

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

Reply to this Message

Nick Hodge - 17 May 2008 21:49 GMT

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

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk

> Hi All,
>
[quoted text clipped - 10 lines]
>
> Regards - Div

Reply to this Message

Div - 18 May 2008 14:38 GMT

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

Reply to this Message

Nick Hodge - 18 May 2008 18:43 GMT

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

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
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
[quoted text clipped - 31 lines]
>>>
>>> Regards - Div

Reply to this Message

Div - 25 May 2008 10:49 GMT

Hi again,

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

Reagrds

>Div
>
[quoted text clipped - 21 lines]
>>>>
>>>> Regards - Div

Reply to this Message

Div - 23 May 2008 22:07 GMT

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
[quoted text clipped - 17 lines]
>>>
>>> Regards - Div

Reply to this Message

Div - 25 May 2008 10:57 GMT

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
>
[quoted text clipped - 12 lines]
>>
>> Regards - Div

Reply to this Message