Userform displays in (MM/DD/YYY) format, when spreadsheet stores as (DD/MM/YYYY) format

**david1987** · 08-27-2012, 09:14 AM

Hi,

I have a UserForm which displays the dates in my spreadsheet in the (MM/DD/YYYY) format. The dates are stored in my spreadsheet as (DD/MM/YYYY).

I want my UserForm to display the dates in the (DD/MM/YYY) format.

Below is my code, everything else works correctly. Can anyone suggest a fix?

Text Box 7, 16, 17, 30, 32, 34, 36, 38, 40, 41, 42 are those with dates in them.

Private Sub ComboBox1_Change()
On Error GoTo 1
TextBox43 = MyRange.Find(ComboBox1).Offset(0, 1)
TextBox1 = MyRange.Find(ComboBox1).Offset(0, 2)
TextBox2 = MyRange.Find(ComboBox1).Offset(0, 3)
TextBox3 = MyRange.Find(ComboBox1).Offset(0, 4)
TextBox4 = MyRange.Find(ComboBox1).Offset(0, 5)
TextBox5 = MyRange.Find(ComboBox1).Offset(0, 6)
TextBox6 = MyRange.Find(ComboBox1).Offset(0, 7)
TextBox7 = MyRange.Find(ComboBox1).Offset(0, 8)
TextBox8 = MyRange.Find(ComboBox1).Offset(0, 9)
TextBox9 = MyRange.Find(ComboBox1).Offset(0, 10)
TextBox10 = MyRange.Find(ComboBox1).Offset(0, 11)
TextBox11 = MyRange.Find(ComboBox1).Offset(0, 12)
TextBox12 = MyRange.Find(ComboBox1).Offset(0, 13)
TextBox13 = MyRange.Find(ComboBox1).Offset(0, 14)
TextBox14 = MyRange.Find(ComboBox1).Offset(0, 15)
TextBox15 = MyRange.Find(ComboBox1).Offset(0, 16)
TextBox16 = MyRange.Find(ComboBox1).Offset(0, 17)
TextBox17 = MyRange.Find(ComboBox1).Offset(0, 18)
TextBox18 = MyRange.Find(ComboBox1).Offset(0, 19)
TextBox19 = MyRange.Find(ComboBox1).Offset(0, 20)
TextBox20 = MyRange.Find(ComboBox1).Offset(0, 21)
TextBox21 = MyRange.Find(ComboBox1).Offset(0, 22)
TextBox22 = MyRange.Find(ComboBox1).Offset(0, 23)
TextBox23 = MyRange.Find(ComboBox1).Offset(0, 24)
TextBox24 = MyRange.Find(ComboBox1).Offset(0, 25)
TextBox25 = MyRange.Find(ComboBox1).Offset(0, 26)
TextBox26 = MyRange.Find(ComboBox1).Offset(0, 27)
TextBox27 = MyRange.Find(ComboBox1).Offset(0, 28)
TextBox28 = MyRange.Find(ComboBox1).Offset(0, 29)
TextBox29 = MyRange.Find(ComboBox1).Offset(0, 30)
TextBox30 = MyRange.Find(ComboBox1).Offset(0, 31)
TextBox31 = MyRange.Find(ComboBox1).Offset(0, 32)
TextBox32 = MyRange.Find(ComboBox1).Offset(0, 33)
TextBox33 = MyRange.Find(ComboBox1).Offset(0, 34)
TextBox34 = MyRange.Find(ComboBox1).Offset(0, 35)
TextBox35 = MyRange.Find(ComboBox1).Offset(0, 36)
TextBox36 = MyRange.Find(ComboBox1).Offset(0, 37)
TextBox37 = MyRange.Find(ComboBox1).Offset(0, 38)
TextBox38 = MyRange.Find(ComboBox1).Offset(0, 39)
TextBox39 = MyRange.Find(ComboBox1).Offset(0, 40)
TextBox40 = MyRange.Find(ComboBox1).Offset(0, 41)
TextBox41 = MyRange.Find(ComboBox1).Offset(0, 42)
TextBox42 = MyRange.Find(ComboBox1).Offset(0, 43)


Label1 = ComboBox1
1 End Sub

Private Sub CommandButton1_Click()
With MyRange.Find(Label1)
    

    .Offset(0, 0) = ComboBox1
    .Offset(0, 1) = TextBox43
    .Offset(0, 2) = TextBox1
    .Offset(0, 3) = TextBox2
    .Offset(0, 4) = TextBox3
    .Offset(0, 5) = TextBox4
    .Offset(0, 6) = TextBox5
    .Offset(0, 7) = TextBox6
    .Offset(0, 8) = TextBox7
    .Offset(0, 9) = TextBox8
    .Offset(0, 10) = TextBox9
    .Offset(0, 11) = TextBox10
    .Offset(0, 12) = TextBox11
    .Offset(0, 13) = TextBox12
    .Offset(0, 14) = TextBox13
    .Offset(0, 15) = TextBox14
    .Offset(0, 16) = TextBox15
    .Offset(0, 17) = TextBox16
    .Offset(0, 18) = TextBox17
    .Offset(0, 19) = TextBox18
    .Offset(0, 20) = TextBox19
    .Offset(0, 21) = TextBox20
    .Offset(0, 22) = TextBox21
    .Offset(0, 23) = TextBox22
    .Offset(0, 24) = TextBox23
    .Offset(0, 25) = TextBox24
    .Offset(0, 26) = TextBox25
    .Offset(0, 27) = TextBox26
    .Offset(0, 28) = TextBox27
    .Offset(0, 29) = TextBox28
    .Offset(0, 30) = TextBox29
    .Offset(0, 31) = TextBox30
    .Offset(0, 32) = TextBox31
    .Offset(0, 33) = TextBox32
    .Offset(0, 34) = TextBox33
    .Offset(0, 35) = TextBox34
    .Offset(0, 36) = TextBox35
    .Offset(0, 37) = TextBox36
    .Offset(0, 38) = TextBox37
    .Offset(0, 39) = TextBox38
    .Offset(0, 40) = TextBox39
    .Offset(0, 41) = TextBox40
    .Offset(0, 42) = TextBox41
    .Offset(0, 43) = TextBox42
 
    
    
End With
Unload Me
End Sub

Private Sub CommandButton2_Click()
End
End Sub

Private Sub Label24_Click()

End Sub

Private Sub Label32_Click()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
For Each cell In MyRange
    ComboBox1.AddItem cell
Next

    With TextBox41
        .Text = "dd/mm/yyyy"
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
 End With

    With TextBox42
        .Text = "dd/mm/yyyy"
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
 End With
 
End Sub

Function MyRange()

Set MyRange = Sheet1.[A2:A300]
End Function

**Richard Buttrey** · 08-27-2012, 09:20 AM

Hi,

Please upload the workbook so that we can see the request in context.
In addition have you considered simplifying that code with a loop that processes from 1-43?

**david1987** · 08-27-2012, 09:43 AM

Originally Posted by Richard Buttrey

Hi,

Please upload the workbook so that we can see the request in context.
In addition have you considered simplifying that code with a loop that processes from 1-43?

Hi, here is a condensed version of my Userform/spreadsheet.

As you can see, the date is displayed as 08/01/1987, but in the UserForm it is displayed as 01/08/1987, how can i display it as 08/01/1987 in the UserForm?

Thanks

**Richard Buttrey** · 08-27-2012, 10:03 AM

Hi,
In the ComboBox1_Change event change TextBox7 to

TextBox7 = Format(MyRange.Find(ComboBox1).Offset(0, 8), "dd/mm/yyyy")

**david1987** · 08-27-2012, 10:15 AM

Thanks very much, that worked perfectly, greatly appreciated.

**Richard Buttrey** · 08-27-2012, 10:39 AM

Hello David

You might like to consider simplifying this. As I intimated a loop would be better used to populate your text boxes
I alos see that every time you populate them you are calling the Function MyRange. Which means that you are calling it 43 times. Which is rather a waste of time and resources.

Just create an Excel dynamic range name called 'MyRange'
=OFFSET(Plan!$A$2,0,0,COUNTA(Plan!$A:$A),1)

Create a Public Variable in the VBA

Public myrange As Range

In your Userform initialise event

Private Sub UserForm_Initialize()
    Set myrange = Sheet1.Range("MyRange")
    For Each cell In myrange
        ComboBox1.AddItem cell
    Next
End Sub

and now your ComboBox change event is

Private Sub ComboBox1_Change()
    Dim x As Long
    On Error GoTo 1
    
    For x = 1 To 7
        If x <> 7 Then
           UserForm2.Controls("TextBox" & x) = myrange.Find(ComboBox1).Offset(0, x + 1)
           Else
           UserForm2.Controls("TextBox" & x) = Format(myrange.Find(ComboBox1).Offset(0, x + 1),"dd/mm/yyyy")
        End If
    Next x

    TextBox43 = myrange.Find(ComboBox1).Offset(0, 1)
    Label1 = ComboBox1
1 End Sub

Userform displays in (MM/DD/YYY) format, when spreadsheet stores as (DD/MM/YYYY) format

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Userform displays in (MM/DD/YYY) format, when spreadsheet stores as (DD/MM/YYYY) format

Re: Userform displays in (MM/DD/YYY) format, when spreadsheet stores as (DD/MM/YYYY) forma

Re: Userform displays in (MM/DD/YYY) format, when spreadsheet stores as (DD/MM/YYYY) forma

Re: Userform displays in (MM/DD/YYY) format, when spreadsheet stores as (DD/MM/YYYY) forma

Re: Userform displays in (MM/DD/YYY) format, when spreadsheet stores as (DD/MM/YYYY) forma

Re: Userform displays in (MM/DD/YYY) format, when spreadsheet stores as (DD/MM/YYYY) forma

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions