|
Code Needed
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 |
Code Needed
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 |
Code Needed
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 |
Code Needed
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 |
Code Needed
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 |
Code Needed
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 |
Code Needed
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 |
Code Needed
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 "jamescox" wrote: 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 |
Code Needed
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... -- 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 |
Code Needed
"jamescox" wrote: 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... -- 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 |
| All times are GMT +1. The time now is 10:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com