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

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

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

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

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...
 

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


Top