Hi

I Run WinXP with Excel 2K


I have created a form to enter data into a worksheet. This works fine. The
first entry in the form is a date. Normally in Excel when I type a date I
just enter the day followed by a backslash followed by the month (23/9) and
enter and it returns 23/09/2009. However, when I type into the first text box
of the form 23/9 it just stays like that. I assume that this is because it is
a text box and is not formatted as a date format. What I would like is for
the user to be able to enter 23/9 into the first box and it display
23/9/2009. I dont even know if this is possible but I am hoping someone can
supply me with some code that will do this.

This is my current code for the form:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DATA")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.TxtDate.Value) = "" Then
Me.TxtDate.SetFocus
MsgBox "Please enter a the date"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TxtDate.Value
ws.Cells(iRow, 2).Value = Me.TxtWeek.Value
ws.Cells(iRow, 3).Value = Me.TxtShift.Value
ws.Cells(iRow, 4).Value = Me.TxtCrew.Value
ws.Cells(iRow, 5).Value = Me.TxtNonProdDel.Value
ws.Cells(iRow, 6).Value = Me.TxtCalShift.Value
ws.Cells(iRow, 7).Value = Me.TxtInput.Value
ws.Cells(iRow, 8).Value = Me.TxtOutput.Value
ws.Cells(iRow, 9).Value = Me.TxtDelays.Value
ws.Cells(iRow, 10).Value = Me.TxtCoils.Value
ws.Cells(iRow, 11).Value = Me.TxtThrd.Value
ws.Cells(iRow, 12).Value = Me.TxtEps.Value
ws.Cells(iRow, 13).Value = Me.TxtType.Value
ws.Cells(iRow, 14).Value = Me.TxtNpft.Value
ws.Cells(iRow, 15).Value = Me.TxtScrp.Value
ws.Cells(iRow, 16).Value = Me.TxtDwnGrd.Value
ws.Cells(iRow, 17).Value = Me.TxtRawCoil.Value
ws.Cells(iRow, 18).Value = Me.TxtInj.Value
ws.Cells(iRow, 19).Value = Me.TxtSlowRun.Value
ws.Cells(iRow, 20).Value = Me.TxtPlanOutput.Value
ws.Cells(iRow, 21).Value = Me.TxtBudgOutput.Value


'clear the data

Me.TxtDate.Value = ""
Me.TxtWeek.Value = ""
Me.TxtShift.Value = ""
Me.TxtCrew.Value = ""
Me.TxtNonProdDel.Value = ""
Me.TxtCalShift.Value = ""
Me.TxtInput.Value = ""
Me.TxtOutput.Value = ""
Me.TxtDelays.Value = ""
Me.TxtCoils.Value = ""
Me.TxtThrd.Value = ""
Me.TxtEps.Value = ""
Me.TxtType.Value = ""
Me.TxtNpft.Value = ""
Me.TxtScrp.Value = ""
Me.TxtDwnGrd.Value = ""
Me.TxtRawCoil.Value = ""
Me.TxtInj.Value = ""
Me.TxtSlowRun.Value = ""
Me.TxtPlanOutput.Value = ""
Me.TxtBudgOutput.Value = ""
Me.TxtDate.SetFocus


End Sub

------------------------------------------------------------------------
Private Sub cmdClose_Click()
Unload Me


End Sub

---------------------------------------------------------------------------

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub


Thanks


John

If you want a text representation of the date in your Excel cell, try

format(cdate(Trim(Me.TxtDate.Value)),"dd/mm/yyyy")

or

format(cdate(Trim(Me.TxtDate.Value)),"dd/m/yyyy")

if you don't want a two-digit month.

If you want a date in the cell, use

cdate(Trim(Me.TxtDate.Value))

Actually, the Trim is probably overkill, since the CDate VBA function
is pretty good about converting anything even remotely resembling a
string format for a date into an Excel date.

There is a family of the "C" conversion functions in VBA - search in
the Object Browser for CDate and you'll get a reference to them all...

BTW, if all those writes to the worksheet are making things pretty
slow, check into assigning the values from the userform to elements of a
Variant array and then setting your workbook range to that array - much
faster.

Hope this helps!


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=114956

Hi

Thanks for your quick response James.

The issue I have is not what format is displayed in the spreadsheet cell as
I have preformatted the cell to the date format I require. The issue is that
in the textbox on the form I have ceated I want it to show a date format
there.

At present when I enter into the form for example I type 2/4

then I move to the next text box on the form.

The problem is that the 1st text box where I entered the 2/4 remains with
the display "2/4" when in fact I would like it to display 02-Feb-09

I am not a visual basic programmer so I may have misunderstood your reply.

If you are suggesting that I add:-

format(cdate(Trim(Me.TxtDate.Value)),"dd/mm/yyyy")

to my code then where abount in the code do you suggest I enter it.

Thanks


John




"jamescox" wrote:


If you want a text representation of the date in your Excel cell, try

format(cdate(Trim(Me.TxtDate.Value)),"dd/mm/yyyy")

or

format(cdate(Trim(Me.TxtDate.Value)),"dd/m/yyyy")

if you don't want a two-digit month.

If you want a date in the cell, use

cdate(Trim(Me.TxtDate.Value))

Actually, the Trim is probably overkill, since the CDate VBA function
is pretty good about converting anything even remotely resembling a
string format for a date into an Excel date.

There is a family of the "C" conversion functions in VBA - search in
the Object Browser for CDate and you'll get a reference to them all...

BTW, if all those writes to the worksheet are making things pretty
slow, check into assigning the values from the userform to elements of a
Variant array and then setting your workbook range to that array - much
faster.

Hope this helps!


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=114956

In your userform's code, outside of any other subroutines you currently
have, you would need to add a subroutine like the following



Private Sub TxtDate_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)

Me.TxtDate.Value = Format(CDate(Trim(Me.TxtDate.Value)),
"dd/mm/yyyy")

End Sub



This event code will fire after the user enters any text in the TxtDate
textbox and then either presses Enter or clicks on another text box.

However, while testing the above then re-reading your original post,
there is an additional problem. You want to enter the date as dd/mm
(where either of those could be a single digit). *BUT* Excel wants the
month first, not the date. The CDate function returns "1/9" as the 9th
of January, but "13/9" is rendered as the 13th of September - because
Excel is assuming you really meant 9/13. The code below will allow you
to enter dd/mm, but if you or your user enters mm/dd instead, it will
screw up the date. (Actually, I played for a while and didn't find a
date format for cells in Excel that would interpret 23/9 as a date at
all - though that's not relevant to the code below.)

At any rate, while I would suggest that you use some other format for
date entry (never can tell what those crazy end-users will do...), the
following will support *and require* entry in the dd/mm format - and
will also handle some simple user screwups on data entry.


Private Sub TextBox1_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)

Dim sEntry As String
Dim iLoc As Integer

sEntry = Trim(Me.TextBox1.Value)
iLoc = InStr(sEntry, "/")
If iLoc 0 Then
sEntry = Right$(sEntry, Len(sEntry) - iLoc) & "/" &
Left$(sEntry, iLoc - 1)
On Error Resume Next
Me.TextBox1.Value = Format(CDate(sEntry), "dd/mm/yyyy")
If Err < 0 Then
GoTo Had_Problem
End If
Exit Sub
End If

Had_Problem:
MsgBox "Could not interpret your entry as a date in the format of
d/m." & vbLf & "Please try again..."
Cancel = True

End Sub


You'll have to change the text box name in the code above - my test
userform didn't have the name you used...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=114956

Brilliant !

Works great James....Well Done !

John




"jamescox" wrote:


In your userform's code, outside of any other subroutines you currently
have, you would need to add a subroutine like the following



Private Sub TxtDate_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)

Me.TxtDate.Value = Format(CDate(Trim(Me.TxtDate.Value)),
"dd/mm/yyyy")

End Sub



This event code will fire after the user enters any text in the TxtDate
textbox and then either presses Enter or clicks on another text box.

However, while testing the above then re-reading your original post,
there is an additional problem. You want to enter the date as dd/mm
(where either of those could be a single digit). *BUT* Excel wants the
month first, not the date. The CDate function returns "1/9" as the 9th
of January, but "13/9" is rendered as the 13th of September - because
Excel is assuming you really meant 9/13. The code below will allow you
to enter dd/mm, but if you or your user enters mm/dd instead, it will
screw up the date. (Actually, I played for a while and didn't find a
date format for cells in Excel that would interpret 23/9 as a date at
all - though that's not relevant to the code below.)

At any rate, while I would suggest that you use some other format for
date entry (never can tell what those crazy end-users will do...), the
following will support *and require* entry in the dd/mm format - and
will also handle some simple user screwups on data entry.


Private Sub TextBox1_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)

Dim sEntry As String
Dim iLoc As Integer

sEntry = Trim(Me.TextBox1.Value)
iLoc = InStr(sEntry, "/")
If iLoc 0 Then
sEntry = Right$(sEntry, Len(sEntry) - iLoc) & "/" &
Left$(sEntry, iLoc - 1)
On Error Resume Next
Me.TextBox1.Value = Format(CDate(sEntry), "dd/mm/yyyy")
If Err < 0 Then
GoTo Had_Problem
End If
Exit Sub
End If

Had_Problem:
MsgBox "Could not interpret your entry as a date in the format of
d/m." & vbLf & "Please try again..."
Cancel = True

End Sub


You'll have to change the text box name in the code above - my test
userform didn't have the name you used...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=114956

James

Sorry to bother you again but I thought I had it fixed but I didnn't.



I entered in the the code you suggested into the code for the form I am
using:-

Private Sub TxtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Me.TxtDate.Value = Format(CDate(Trim(Me.TxtDate.Value)), "dd/mm/yyyy")

End Sub

When I typed 1/2 into the text box it showed up as 1/02/2009 which is great.
However, on my form I have a button that copies all the data I have entered
into the form into the worksheet. When I added you code this button no longer
copied the data into the spread sheet.

So, It seems I am almost there, but not quite, any ideas?


=======================================
THIS IS MY ORIGINAL CODE
=======================================

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DATA")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
..End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.TxtDate.Value) = "" Then
Me.TxtDate.SetFocus
MsgBox "Please enter a the date"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TxtDate.Value
ws.Cells(iRow, 2).Value = Me.TxtWeek.Value
ws.Cells(iRow, 3).Value = Me.TxtShift.Value
ws.Cells(iRow, 4).Value = Me.TxtCrew.Value
ws.Cells(iRow, 5).Value = Me.TxtNonProdDel.Value
ws.Cells(iRow, 6).Value = Me.TxtCalShift.Value
ws.Cells(iRow, 7).Value = Me.TxtInput.Value
ws.Cells(iRow, 8).Value = Me.TxtOutput.Value
ws.Cells(iRow, 9).Value = Me.TxtDelays.Value
ws.Cells(iRow, 10).Value = Me.TxtCoils.Value
ws.Cells(iRow, 11).Value = Me.TxtThrd.Value
ws.Cells(iRow, 12).Value = Me.TxtEps.Value
ws.Cells(iRow, 13).Value = Me.TxtType.Value
ws.Cells(iRow, 14).Value = Me.TxtNpft.Value
ws.Cells(iRow, 15).Value = Me.TxtScrp.Value
ws.Cells(iRow, 16).Value = Me.TxtDwnGrd.Value
ws.Cells(iRow, 17).Value = Me.TxtRawCoil.Value
ws.Cells(iRow, 18).Value = Me.TxtInj.Value
ws.Cells(iRow, 19).Value = Me.TxtSlowRun.Value
ws.Cells(iRow, 20).Value = Me.TxtPlanOutput.Value
ws.Cells(iRow, 21).Value = Me.TxtBudgOutput.Value


'clear the data

Me.TxtDate.Value = ""
Me.TxtWeek.Value = ""
Me.TxtShift.Value = ""
Me.TxtCrew.Value = ""
Me.TxtNonProdDel.Value = ""
Me.TxtCalShift.Value = ""
Me.TxtInput.Value = ""
Me.TxtOutput.Value = ""
Me.TxtDelays.Value = ""
Me.TxtCoils.Value = ""
Me.TxtThrd.Value = ""
Me.TxtEps.Value = ""
Me.TxtType.Value = ""
Me.TxtNpft.Value = ""
Me.TxtScrp.Value = ""
Me.TxtDwnGrd.Value = ""
Me.TxtRawCoil.Value = ""
Me.TxtInj.Value = ""
Me.TxtSlowRun.Value = ""
Me.TxtPlanOutput.Value = ""
Me.TxtBudgOutput.Value = ""
Me.TxtDate.SetFocus


End Sub

------------------------------------------------------------------------
Private Sub cmdClose_Click()
Unload Me


End Sub

---------------------------------------------------------------------------

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub

===========================================


Thanks


John



"jamescox" wrote:


In your userform's code, outside of any other subroutines you currently
have, you would need to add a subroutine like the following



Private Sub TxtDate_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)

Me.TxtDate.Value = Format(CDate(Trim(Me.TxtDate.Value)),
"dd/mm/yyyy")

End Sub



This event code will fire after the user enters any text in the TxtDate
textbox and then either presses Enter or clicks on another text box.

However, while testing the above then re-reading your original post,
there is an additional problem. You want to enter the date as dd/mm
(where either of those could be a single digit). *BUT* Excel wants the
month first, not the date. The CDate function returns "1/9" as the 9th
of January, but "13/9" is rendered as the 13th of September - because
Excel is assuming you really meant 9/13. The code below will allow you
to enter dd/mm, but if you or your user enters mm/dd instead, it will
screw up the date. (Actually, I played for a while and didn't find a
date format for cells in Excel that would interpret 23/9 as a date at
all - though that's not relevant to the code below.)

At any rate, while I would suggest that you use some other format for
date entry (never can tell what those crazy end-users will do...), the
following will support *and require* entry in the dd/mm format - and
will also handle some simple user screwups on data entry.


Private Sub TextBox1_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)

Dim sEntry As String
Dim iLoc As Integer

sEntry = Trim(Me.TextBox1.Value)
iLoc = InStr(sEntry, "/")
If iLoc 0 Then
sEntry = Right$(sEntry, Len(sEntry) - iLoc) & "/" &
Left$(sEntry, iLoc - 1)
On Error Resume Next
Me.TextBox1.Value = Format(CDate(sEntry), "dd/mm/yyyy")
If Err < 0 Then
GoTo Had_Problem
End If
Exit Sub
End If

Had_Problem:
MsgBox "Could not interpret your entry as a date in the format of
d/m." & vbLf & "Please try again..."
Cancel = True

End Sub


You'll have to change the text box name in the code above - my test
userform didn't have the name you used...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=114956

That's odd - your syntax for getting values into the worksheet seems to
be working fine for me. Are any of the other values getting written
back, or is it just TxtDate's value that isn't showing up?

One quick test is to just comment out all lines of the

Private Sub TxtDate_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)

and see if the values start getting written back.

Another test would be to put this line of code inside
TxtDate_BeforeUpdate and see if the value can be written back from
the

Activesheet.Cells(1, 1).Value = Me.TextBox1.Value

where you choose the values for Cells so that it's writing to a
location that won't mess up anything - (1,1) = A1 of course. Obviously,
this wouldn't be a permanent solution, but might help in trying to
understand what's going on.


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=114956