• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Gah! Date switches around for my Userforms

S

sammygirl

New Member
Howdy!


I am a newbie VBA'er


Ok so I have built a database with userforms to add info and then update info based on code.


As I am in Aus I need the date format to be correct. I am almost there! However the date switches every time (eg one time it 09/10/13 and then the next time its 10/09/13)


Here's the code


(A basic run-down. You add a code to populate the userform with information, then you can update it to put it back into the database)

[pre]
Code: 
Private Sub CommandButton1_Click()

Dim eRow As Long
Dim num As String
num = TextBox10.Value

Dim ws As Worksheet
Set ws = Worksheets("Info")

'error check for Match function
On Error GoTo valueiferror

'find specific row
eRow = Application.WorksheetFunction.Match(num, ws.Range("A:A"), 0)

'copy data to the Userform
Me.TextBox1.Value = ws.Cells(eRow, 2).Value
Me.TextBox2.Value = ws.Cells(eRow, 3).Value
Me.TextBox3.Value = ws.Cells(eRow, 4).Value
Me.TextBox4.Value = Format(ws.Cells(eRow, 5).Value, "DD/MM/YYYY")
Me.TextBox5.Value = Format(ws.Cells(eRow, 6).Value, "DD/MM/YYYY")
Me.TextBox6.Value = ws.Cells(eRow, 7).Value
Me.ComboBox1.Value = ws.Cells(eRow, 13).Value

Exit Sub

valueiferror:
MsgBox "Charge Code not recognised" & Chr(13) & "Entries must be Case Sensitive", vbExclamation

End Sub

Private Sub CommandButton2_Click()

Dim eRow As Long
Dim num As String
num = TextBox10.Value

Dim ws As Worksheet
Set ws = Worksheets("Info")

'find specific row
eRow = Application.WorksheetFunction.Match(num, ws.Range("A:A"), 0)

If IsDate(TextBox4.Value) And IsDate(TextBox5.Value) Then

'copy data to the Excel worksheet

ws.Cells(eRow, 2).Value = Me.TextBox1.Value
ws.Cells(eRow, 3).Value = Me.TextBox2.Value
ws.Cells(eRow, 4).Value = Me.TextBox3.Value
ws.Cells(eRow, 5).Value = Format(ws.Cells(eRow, 5).Value, "DD/MM/YYYY")
ws.Cells(eRow, 6).Value = Format(ws.Cells(eRow, 6).Value, "DD/MM/YYYY")
ws.Cells(eRow, 7).Value = Me.TextBox6.Value
ws.Cells(eRow, 13).Value = Me.ComboBox1.Value

Else
MsgBox "Wrong date values entered. Must be dd/mm/yy", vbExclamation
End If

'Clear data for new entry

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox10.Value = ""
Me.ComboBox1.Value = ""

'Set focus on first box for new entry
Me.TextBox10.SetFocus

Exit Sub
End Sub

Private Sub CommandButton3_Click()
Unload Me
End Sub
[/pre]
I like to understand what went wrong so an explanation would be greatly appreciated!! (I learn that way ;)


Thank you!!
 
Hi SammyGirl


I'm no guru and self-taught on VBA but like you I have date frustrations from time to time due to date internals of MS Excel being US format... I'm UK based.


I haven't looked at your problem in detail... but off the bat... some things to check..

(i) Regional settings of PC - sometimes missed

(ii) when formatting a cell - I have discovered under format cell, that the 3rd option down is best used for dd/mmm/yyyy format - not the one with a * at the end.


Hope this helps...

Jabba
 
Hi, sammygirl!


I wrote this 2 days ago at:

http://chandoo.org/forums/topic/function-format#post-106131


"Excel treats dates as integer numbers starting at 01/01/1900 with the value of 1 so today 31/05/2013 is 41425, and times as decimal numbers from 0 corresponding to 00:00:00 to near 1 (0,999988425925926) corresponding to 23:59:59. Now it's 41425,7512395833."


So whatever you do with dates in a worksheet it contents will never vary, maybe the displayed format will do if for example no format is specified, so for December 20th 2010 in the UK, AUS or ARG, you'll see 20/12/2010 and in USA 12/20/2010. If a format is specified for that cell, let us say "dd-mmm-yyyy" then you'll see it everywhere in that format independently of the regional settings configuration.


That's for Excel worksheets. Now if we go to VBA, the same considerations of the first quoted paragraph are still valid but take care that when you assign values to cells from VBA since it always use the USA format "mm/dd/yyyy". Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Gah%21%20Date%20switches%20around%20for%20my%20Userforms%20%28for%20sammygirl%20at%20chandoo.org%29.xlsm


This the sample structure:

[pre]
Code: 
Output ARG			20-dic-2010	Input with no format	20/12/2010	December 20		numeric value			40532
Output USA			dic-20-2010																	40532		

VBA valid date and correct date	11/10/2010	Original date		11/10/2010	October 11						40462
VBA valid but wrong date	10/11/2010	Original date		10/11/2010	November 10						40492
VBA invalid date		20/12/2010	Original date		20/12/2010	December 20						40532		

VBA valid date and correct date	10/11/2010	Original date		11/10/2010	October 11	with format "dd/mm/yyyy"		40492	forced format		wrong
VBA valid but wrong date	11/10/2010	Original date		10/11/2010	November 10						40462				wrong
VBA invalid date		20/12/2010	Original date		20/12/2010	December 20			20/12/2010		invalid

VBA valid date and correct date	11/10/2010	Original date		11/10/2010	October 11	with format "mm/dd/yyyy"		40462	VBA natural format	right
VBA valid but wrong date	10/11/2010	Original date		10/11/2010	November 10						40492				right
VBA invalid date		20/12/2010	Original date		20/12/2010	December 20						40532				right
This is the code used for the test:

-----

Option Explicit

Sub TestDates()
Dim I As Integer
With ActiveSheet
For I = 4 To 6
.Range("B" & I).Value = .Range("D" & I).Value
Next I
For I = 8 To 10
.Range("B" & I).Value = Format(.Range("D" & I).Value, "dd/mm/yyyy")
Next I
For I = 12 To 14
.Range("B" & I).Value = Format(.Range("D" & I).Value, "mm/dd/yyyy")
Next I
End With
End Sub
[/pre]
-----


Hope it helps.


Regards!
 
Hi!


Thank you so much for your help!


I'm sorry to seem daft but I don't understand - I wrote code to overwrite the default format settings...and it has worked in some parts of the spreadsheet (like when adding the entry) but switches when the userform is populated to update existing entries...


What code can I write to prevent this from happening?


I currently have


Me.TextBox4.Value = Format(ws.Cells(eRow, 5).Value, "DD/MM/YYYY")


:/


Sorry - this one has me stumped...


Thank you!! :)
 
Hi ,


There are two things you can check out :


1. What is the default date format on your computer ?


2. What is the date format for the cells you are using to store and retrieve dates from in your workbook ? Can you enter a date such as May 12, 2013 ( there should be a space between May and 12 , as well as a space after the comma ) , and see what is displayed in the formula bar ? The cell where you enter this date will display 12-May-2013 , but the formula bar may display one of the following :


5/12/2013


12/5/2013


depending on the default date format for your system.


3. Can you upload only your workbook with data , but without the macros ?


Narayan
 
Hi, sammygirl!


Since you haven't upload a sample file I had to build my sample file to upload, so you owe me a six-pack of Carlsberg.


Now we've arranged that, give a look to this file:

https://dl.dropboxusercontent.com/u/60558749/Gah%21%20Date%20switches%20around%20for%20my%20Userforms%202%20%28for%20sammygirl%20at%20chandoo.org%29.xlsm


I fixed the code for click event of CommandButton2 command button as this:

-----

[pre]
Code: 
Private Sub CommandButton2_Click()

Dim eRow As Long
Dim num As String
Dim dDate As Date
num = TextBox10.Value

Dim ws As Worksheet
Set ws = Worksheets("Info")

'find specific row
eRow = Application.WorksheetFunction.Match(num, ws.Range("A:A"), 0)

If IsDate(TextBox4.Value) And IsDate(TextBox5.Value) Then

'copy data to the Excel worksheet

ws.Cells(eRow, 2).Value = Me.TextBox1.Value
ws.Cells(eRow, 3).Value = Me.TextBox2.Value
ws.Cells(eRow, 4).Value = Me.TextBox3.Value
dDate = Me.TextBox4.Value
ws.Cells(eRow, 5).Value = dDate
dDate = Me.TextBox5.Value
ws.Cells(eRow, 6).Value = dDate
ws.Cells(eRow, 7).Value = Me.TextBox6.Value
ws.Cells(eRow, 13).Value = Me.ComboBox1.Value

Else
MsgBox "Wrong date values entered. Must be dd/mm/yy", vbExclamation
End If

'Clear data for new entry

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox10.Value = ""
Me.ComboBox1.Value = ""

'Set focus on first box for new entry
Me.TextBox10.SetFocus

Exit Sub
End Sub
[/pre]
-----


Using a date variable you avoid the natural MDY format of VBA.


Hope it helps and were understandable.


Regards!
 
You must log in or register to reply here.
Back
Top