Code Needed

J

John Calder

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
 
J

jamescox

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 functio
is pretty good about converting anything even remotely resembling
string format for a date into an Excel date.

There is a family of the "C" conversion functions in VBA - search i
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 prett
slow, check into assigning the values from the userform to elements of
Variant array and then setting your workbook range to that array - muc
faster.

Hope this helps
 
J

John Calder

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
 
J

jamescox

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



Private Sub TxtDate_BeforeUpdate(ByVal Cancel A
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 TxtDat
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/m
(where either of those could be a single digit). *BUT* Excel wants th
month first, not the date. The CDate function returns "1/9" as the 9t
of January, but "13/9" is rendered as the 13th of September - becaus
Excel is assuming you really meant 9/13. The code below will allow yo
to enter dd/mm, but if you or your user enters mm/dd instead, it wil
screw up the date. (Actually, I played for a while and didn't find
date format for cells in Excel that would interpret 23/9 as a date a
all - though that's not relevant to the code below.)

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


Private Sub TextBox1_BeforeUpdate(ByVal Cancel A
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 o
d/m." & vbLf & "Please try again..."
Cancel = True

End Sub


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

John Calder

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
 
J

jamescox

That's odd - your syntax for getting values into the worksheet seems t
be working fine for me. Are any of the other values getting writte
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 A
MSForms.ReturnBoolean)

and see if the values start getting written back.

Another test would be to put this line of code insid
TxtDate_BeforeUpdate and see if the value can be written back fro
there:

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

where you choose the values for Cells so that it's writing to
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 t
understand what's going on
 
J

John Calder

James

Thanks a lot for your help, however I,m afraid I just dont have your skill
set and just cannot seem to get this fixed. Short of sending you my file, I
am afraid I have hot an impasse.

As I said, when I add you code the text box displays the date format
dd/mm/yyy I then fill in all the other fields and after the last field is
completed the curser jumps to the "ADD DATA" button that is on the spread
sheet. When I press this buttton the whole thing freezes and I have to use
ctl/alt/del to get out of it.

As soon as I take out your code, the "ADD DATA" button works again.

I have no idea


Cheers

John
 
J

jamescox

If you are willing to create a version of your workbook that you don't
mind the world seeing and can attach it to a reply per the following
guidelines (taken from the FAQ)

- How do I attach a file to a post?

To attach a file to your post, you need to be using the main 'New Post'
or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post'
page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled
'Manage Attachments'. Clicking this button will open a new window for
uploading attachments. You can upload an attachment either from your
computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and
locate the file. To upload a file from another URL, enter the full URL
for the file in the second box on this page. Once you have completed one
of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input
boxes in this window. You can then close the window to return to the new
post screen.

What files types can I use? How large can attachments be?

In the attachment window you will find a list of the allowed file types
and their maximum sizes. Files that are larger than these sizes will be
rejected. There may also be an overall quota limit to the number of
attachments you can post to the board.

How do I add an image to a post?

If you have uploaded an image as an attachment, you can click the arrow
next to the 'Attachment Icon' and select it from the list. This will be
inserted into your post and can be located where you want it displayed.

To include an image that is not uploaded as an attachment and is
located on another website, you can do so by copying the full URL to the
image, (not the page on which the image is located), and either pressing
the 'Insert Image' icon or by typing [image: before the URL and ] after
it, ensuring that you do not have any spaces before or after the URL of
the image. You can insert pictures from your albums (?) in this way too.
-

then I'm willing to take a look and (maybe) find what the problem is...
 
J

John Calder

James

I dont have an option of "Manage Attachments" ?


Is there any other way I can get it to you?

my email is (e-mail address removed)

Cheers


John
 
J

John Calder

jamescox said:
If you are willing to create a version of your workbook that you don't
mind the world seeing and can attach it to a reply per the following
guidelines (taken from the FAQ)

- How do I attach a file to a post?

To attach a file to your post, you need to be using the main 'New Post'
or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post'
page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled
'Manage Attachments'. Clicking this button will open a new window for
uploading attachments. You can upload an attachment either from your
computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and
locate the file. To upload a file from another URL, enter the full URL
for the file in the second box on this page. Once you have completed one
of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input
boxes in this window. You can then close the window to return to the new
post screen.

What files types can I use? How large can attachments be?

In the attachment window you will find a list of the allowed file types
and their maximum sizes. Files that are larger than these sizes will be
rejected. There may also be an overall quota limit to the number of
attachments you can post to the board.

How do I add an image to a post?

If you have uploaded an image as an attachment, you can click the arrow
next to the 'Attachment Icon' and select it from the list. This will be
inserted into your post and can be located where you want it displayed.

To include an image that is not uploaded as an attachment and is
located on another website, you can do so by copying the full URL to the
image, (not the page on which the image is located), and either pressing
the 'Insert Image' icon or by typing [image: before the URL and ] after
it, ensuring that you do not have any spaces before or after the URL of
the image. You can insert pictures from your albums (?) in this way too.
-

then I'm willing to take a look and (maybe) find what the problem is...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Set Textbox on a Form to Deafault 7

Top