|
Date Field Problem in Userform
Dear Sir, I have one userform for data entry. my problem is with the " DateCharged " field in the userform. every thing is working fine but when I enter the Month like this: SEP 08 and press ok to fill out the data to the worksheet, the SEP 08 is showing the Date like this 08-sep-08 instead of Sep 08 I dont want to show the date in the worksheet. I want only the month with Year. this is my problem. Actually I need the month with year like SEP 08, because I have an other function for Advance Filter, for filtering the records by month. when I feed SEP 08 and then press enter key my function filter all the records for the month of September 08. thats why I need my field show the month with year in the worksheet. I am using the following cods in my UserForm. Private Sub cmdClearForm_Click() Call UserForm_Initialize End Sub Private Sub CommandButton3_Click() ' for ok button ActiveWorkbook.Sheets("DailyIssue").Activate Range("A5").Select ' to fill up combo box for the category If ComboCategory.ListIndex = -1 Then MsgBox "You must choose the category SHAHZAD" ComboCategory.SetFocus Exit Sub End If '----------------------------------------------- ' to fill up QTY If txtQuantity = "" Then MsgBox " you must provide Quantity " txtQuantity.SetFocus Exit Sub End If ' to fill up Unit price If txtUnitPrice = "" Then MsgBox " you must provide Unit Price " txtUnitPrice.SetFocus Exit Sub End If ' to fill up DateIssue If DateIssue = "" Then MsgBox " You must enter the date, format: 'dd/mmm/yyyy'" DateIssue.SetFocus Exit Sub End If ' to fill up DateCharged If DateCharged = "" Then MsgBox " You must enter the Month, format: 'dd/mmm/yyyy'" DateCharged.SetFocus Exit Sub End If Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = DateIssue.Value ActiveCell.Offset(0, 1) = TxtDescription.Value ActiveCell.Offset(0, 2) = txtQuantity.Value ActiveCell.Offset(0, 3) = txtUnitPrice.Value ActiveCell.Offset(0, 5) = ComboCategory.Value ActiveCell.Offset(0, 6) = ComboEmployee.Value ActiveCell.Offset(0, 7) = txtTroubleReport.Value ActiveCell.Offset(0, 8) = DateCharged.Value ActiveCell.Offset(0, 9) = txtRemarks.Value ' Range("A5").Select ' Ask and Do Procedure If MsgBox("One record is written, do you have more entries ?", vbYesNo, "Title") = vbYes Then Call UserForm_Initialize Else Unload Me End If End Sub Private Sub DateIssue_exit(ByVal Cancel As MSForms.ReturnBoolean) 'txtFrom being the text box on the user form where the date is entered. ' to restrict the date field If Not IsDate(DateIssue) And Len(DateIssue) 0 Then MsgBox "Input must be a date in the format: 'dd/mmm/yyyy'" Cancel = True Else DateIssue = Format(DateIssue, "dd/mmm/yyyy") End If End Sub Private Sub DateCharted_exit(ByVal Cancel As MSForms.ReturnBoolean) 'txtFrom being the text box on the user form where the date is entered. ' to restrict the date field If Not IsDate(DateCharged) And Len(DateCharged) 0 Then MsgBox "Input must be a date in the format: 'dd/mmm/yyyy'" Cancel = True Else DateIssue = Format(DateCharged, "mmm/yyyy") End If End Sub Private Sub UserForm_Initialize() Application.EnableEvents = False DateIssue.Value = "" TxtDescription.Value = "" txtQuantity.Value = "" txtUnitPrice.Value = "" ComboCategory.Value = "" ComboEmployee.Value = "" txtTroubleReport.Value = "" txtRemarks.Value = "" DateCharged.Value = "" DateIssue.SetFocus Application.EnableEvents = True End Sub Private Sub CommandButton4_Click() ' for cancel buton Unload Me End Sub Private Sub CommandButton5_Click() Call UserForm_Initialize End Sub Private Sub TxtUnitPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' to restrict blank entry If txtUnitPrice.Text = "" Then MsgBox "Sorry, please enter the Unit Price to proceed..." Cancel = True End If End Sub Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' to restrict entry for Numbers only If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then Interaction.Beep KeyAscii = 0 End If End Sub Private Sub TxtQuantity_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'to restrict blank entry If txtQuantity.Text = "" Then MsgBox "Sorry, please enter the Quantity to proceed..." Cancel = True End If End Sub Private Sub txtQuantity_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' to restrict entry for Numbers only If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then Interaction.Beep KeyAscii = 0 End If End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) ' Disable the "X" on the userform so that the user can't If CloseMode = vbFormControlMenu Then Cancel = True MsgBox Prompt:=" Sorry but I can't let you do that. " End If End Sub |
Date Field Problem in Userform
Just format the worksheet cell as mmm yy
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Dear Sir, I have one userform for data entry. my problem is with the " DateCharged " field in the userform. every thing is working fine but when I enter the Month like this: SEP 08 and press ok to fill out the data to the worksheet, the SEP 08 is showing the Date like this 08-sep-08 instead of Sep 08 I dont want to show the date in the worksheet. I want only the month with Year. this is my problem. Actually I need the month with year like SEP 08, because I have an other function for Advance Filter, for filtering the records by month. when I feed SEP 08 and then press enter key my function filter all the records for the month of September 08. thats why I need my field show the month with year in the worksheet. I am using the following cods in my UserForm. Private Sub cmdClearForm_Click() Call UserForm_Initialize End Sub Private Sub CommandButton3_Click() ' for ok button ActiveWorkbook.Sheets("DailyIssue").Activate Range("A5").Select ' to fill up combo box for the category If ComboCategory.ListIndex = -1 Then MsgBox "You must choose the category SHAHZAD" ComboCategory.SetFocus Exit Sub End If '----------------------------------------------- ' to fill up QTY If txtQuantity = "" Then MsgBox " you must provide Quantity " txtQuantity.SetFocus Exit Sub End If ' to fill up Unit price If txtUnitPrice = "" Then MsgBox " you must provide Unit Price " txtUnitPrice.SetFocus Exit Sub End If ' to fill up DateIssue If DateIssue = "" Then MsgBox " You must enter the date, format: 'dd/mmm/yyyy'" DateIssue.SetFocus Exit Sub End If ' to fill up DateCharged If DateCharged = "" Then MsgBox " You must enter the Month, format: 'dd/mmm/yyyy'" DateCharged.SetFocus Exit Sub End If Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = DateIssue.Value ActiveCell.Offset(0, 1) = TxtDescription.Value ActiveCell.Offset(0, 2) = txtQuantity.Value ActiveCell.Offset(0, 3) = txtUnitPrice.Value ActiveCell.Offset(0, 5) = ComboCategory.Value ActiveCell.Offset(0, 6) = ComboEmployee.Value ActiveCell.Offset(0, 7) = txtTroubleReport.Value ActiveCell.Offset(0, 8) = DateCharged.Value ActiveCell.Offset(0, 9) = txtRemarks.Value ' Range("A5").Select ' Ask and Do Procedure If MsgBox("One record is written, do you have more entries ?", vbYesNo, "Title") = vbYes Then Call UserForm_Initialize Else Unload Me End If End Sub Private Sub DateIssue_exit(ByVal Cancel As MSForms.ReturnBoolean) 'txtFrom being the text box on the user form where the date is entered. ' to restrict the date field If Not IsDate(DateIssue) And Len(DateIssue) 0 Then MsgBox "Input must be a date in the format: 'dd/mmm/yyyy'" Cancel = True Else DateIssue = Format(DateIssue, "dd/mmm/yyyy") End If End Sub Private Sub DateCharted_exit(ByVal Cancel As MSForms.ReturnBoolean) 'txtFrom being the text box on the user form where the date is entered. ' to restrict the date field If Not IsDate(DateCharged) And Len(DateCharged) 0 Then MsgBox "Input must be a date in the format: 'dd/mmm/yyyy'" Cancel = True Else DateIssue = Format(DateCharged, "mmm/yyyy") End If End Sub Private Sub UserForm_Initialize() Application.EnableEvents = False DateIssue.Value = "" TxtDescription.Value = "" txtQuantity.Value = "" txtUnitPrice.Value = "" ComboCategory.Value = "" ComboEmployee.Value = "" txtTroubleReport.Value = "" txtRemarks.Value = "" DateCharged.Value = "" DateIssue.SetFocus Application.EnableEvents = True End Sub Private Sub CommandButton4_Click() ' for cancel buton Unload Me End Sub Private Sub CommandButton5_Click() Call UserForm_Initialize End Sub Private Sub TxtUnitPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' to restrict blank entry If txtUnitPrice.Text = "" Then MsgBox "Sorry, please enter the Unit Price to proceed..." Cancel = True End If End Sub Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' to restrict entry for Numbers only If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then Interaction.Beep KeyAscii = 0 End If End Sub Private Sub TxtQuantity_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'to restrict blank entry If txtQuantity.Text = "" Then MsgBox "Sorry, please enter the Quantity to proceed..." Cancel = True End If End Sub Private Sub txtQuantity_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' to restrict entry for Numbers only If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then Interaction.Beep KeyAscii = 0 End If End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) ' Disable the "X" on the userform so that the user can't If CloseMode = vbFormControlMenu Then Cancel = True MsgBox Prompt:=" Sorry but I can't let you do that. " End If End Sub |
Date Field Problem in Userform
hi, is there a mistake in the code?
Private Sub DateCharted_exit(ByVal Cancel As MSForms.ReturnBoolean) should be DateCharged_exit 'txtFrom being the text box on the user form where the *date is entered. ' * to restrict the date field If Not IsDate(DateCharged) And Len(DateCharged) 0 Then * *MsgBox "Input must be a date in the *format: 'dd/mmm/yyyy'" * *Cancel = True * *Else * * * * DateIssue = Format(DateCharged, "mmm/yyyy") DateCharged = Format(DateCharged, "mmm/yyyy") * * End If End Sub bye stefan |
Date Field Problem in Userform
On May 21, 11:46*am, "Bob Phillips" wrote:
Just format the worksheet cell as mmm yy -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Dear Sir, I have one userform for data entry. *my problem is with the " DateCharged " field in the userform. every thing is working fine but when I enter the Month like this: SEP 08 and press ok to fill out the data to the worksheet, the SEP 08 is showing the Date like this 08-sep-08 *instead of *Sep 08 I dont want to show the date in the worksheet. I want only the month with Year. this is my problem. Actually I need the month with year like *SEP 08, *because I have an other function for Advance Filter, *for filtering the records by month. when I *feed SEP 08 and then press enter key my function filter all the records for the month of September 08. thats why I need my field show the month with year in the worksheet. I am using the following cods in my UserForm. Private Sub cmdClearForm_Click() * *Call UserForm_Initialize End Sub Private Sub CommandButton3_Click() ' for ok button * *ActiveWorkbook.Sheets("DailyIssue").Activate * *Range("A5").Select ' to fill up combo box for the category * * * *If ComboCategory.ListIndex = -1 Then * * * *MsgBox "You must choose the category SHAHZAD" * * * *ComboCategory.SetFocus * * * *Exit Sub * * * *End If '----------------------------------------------- ' *to fill up QTY * * * *If txtQuantity = "" Then * * * *MsgBox " you must provide Quantity " * * * *txtQuantity.SetFocus * * * *Exit Sub * * * *End If ' *to fill up Unit price * * * *If txtUnitPrice = "" Then * * * *MsgBox " you must provide Unit Price " * * * *txtUnitPrice.SetFocus * * * *Exit Sub * * * *End If ' *to fill up DateIssue * * * *If DateIssue = "" Then * * * *MsgBox " You must enter the date, format: 'dd/mmm/yyyy'" * * * *DateIssue.SetFocus * * * *Exit Sub * * * *End If ' *to fill up DateCharged * * * *If DateCharged = "" Then * * * *MsgBox " You must enter the Month, format: 'dd/mmm/yyyy'" * * * *DateCharged.SetFocus * * * *Exit Sub * * * *End If * *Do * *If IsEmpty(ActiveCell) = False Then * *ActiveCell.Offset(1, 0).Select * *End If * *Loop Until IsEmpty(ActiveCell) = True * *ActiveCell.Value = DateIssue.Value * *ActiveCell.Offset(0, 1) = TxtDescription.Value * *ActiveCell.Offset(0, 2) = txtQuantity.Value * *ActiveCell.Offset(0, 3) = txtUnitPrice.Value * *ActiveCell.Offset(0, 5) = ComboCategory.Value * *ActiveCell.Offset(0, 6) = ComboEmployee.Value * *ActiveCell.Offset(0, 7) = txtTroubleReport.Value * *ActiveCell.Offset(0, 8) = DateCharged.Value * *ActiveCell.Offset(0, 9) = txtRemarks.Value *' Range("A5").Select ' Ask and Do Procedure If MsgBox("One record is written, do you have more entries ?", vbYesNo, "Title") = vbYes Then * *Call UserForm_Initialize * *Else * *Unload Me * *End If End Sub Private Sub DateIssue_exit(ByVal Cancel As MSForms.ReturnBoolean) 'txtFrom being the text box on the user form where the *date is entered. ' * to restrict the date field If Not IsDate(DateIssue) And Len(DateIssue) 0 Then * MsgBox "Input must be a date in the *format: 'dd/mmm/yyyy'" * Cancel = True * Else * * * *DateIssue = Format(DateIssue, "dd/mmm/yyyy") * *End If End Sub Private Sub DateCharted_exit(ByVal Cancel As MSForms.ReturnBoolean) 'txtFrom being the text box on the user form where the *date is entered. ' * to restrict the date field If Not IsDate(DateCharged) And Len(DateCharged) 0 Then * MsgBox "Input must be a date in the *format: 'dd/mmm/yyyy'" * Cancel = True * Else * * * *DateIssue = Format(DateCharged, "mmm/yyyy") * *End If End Sub Private Sub UserForm_Initialize() Application.EnableEvents = False * *DateIssue.Value = "" * *TxtDescription.Value = "" * *txtQuantity.Value = "" * *txtUnitPrice.Value = "" * *ComboCategory.Value = "" * *ComboEmployee.Value = "" * *txtTroubleReport.Value = "" * *txtRemarks.Value = "" * *DateCharged.Value = "" * *DateIssue.SetFocus Application.EnableEvents = True End Sub Private Sub CommandButton4_Click() ' for cancel buton Unload Me End Sub Private Sub CommandButton5_Click() Call UserForm_Initialize End Sub Private Sub TxtUnitPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' to restrict blank entry * * * *If txtUnitPrice.Text = "" Then * * * *MsgBox "Sorry, please enter the Unit Price to proceed..." * * * *Cancel = True * * * *End If End Sub Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' * to restrict entry for Numbers only * *If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then * * * *Interaction.Beep * * * *KeyAscii = 0 * *End If End Sub Private Sub TxtQuantity_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'to restrict blank entry * * * *If txtQuantity.Text = "" Then * * * *MsgBox "Sorry, please enter the Quantity to proceed..." * * * *Cancel = True * * * *End If End Sub Private Sub txtQuantity_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' * to restrict entry for Numbers only * *If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then * * * *Interaction.Beep * * * *KeyAscii = 0 * *End If End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) ' Disable the "X" on the userform so that the user can't If CloseMode = vbFormControlMenu Then * *Cancel = True * *MsgBox Prompt:=" Sorry but I can't let you do that. " End If End Sub- Hide quoted text - - Show quoted text - Dear Sir, I Formated the worksheet cell to mmm/yyy it is showing JAN 08, but in the formula bar it is showing 08/01/08 this is the reason, I am unable to filter my records by month. if it will show 01/01/08 then it will be ok, Any suggession... Regards. Shahzad |
Date Field Problem in Userform
If you want to filter by month, add a helper column to calculate the month,
and filter by that column. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... On May 21, 11:46 am, "Bob Phillips" wrote: Just format the worksheet cell as mmm yy -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Dear Sir, I have one userform for data entry. my problem is with the " DateCharged " field in the userform. every thing is working fine but when I enter the Month like this: SEP 08 and press ok to fill out the data to the worksheet, the SEP 08 is showing the Date like this 08-sep-08 instead of Sep 08 I dont want to show the date in the worksheet. I want only the month with Year. this is my problem. Actually I need the month with year like SEP 08, because I have an other function for Advance Filter, for filtering the records by month. when I feed SEP 08 and then press enter key my function filter all the records for the month of September 08. thats why I need my field show the month with year in the worksheet. I am using the following cods in my UserForm. Private Sub cmdClearForm_Click() Call UserForm_Initialize End Sub Private Sub CommandButton3_Click() ' for ok button ActiveWorkbook.Sheets("DailyIssue").Activate Range("A5").Select ' to fill up combo box for the category If ComboCategory.ListIndex = -1 Then MsgBox "You must choose the category SHAHZAD" ComboCategory.SetFocus Exit Sub End If '----------------------------------------------- ' to fill up QTY If txtQuantity = "" Then MsgBox " you must provide Quantity " txtQuantity.SetFocus Exit Sub End If ' to fill up Unit price If txtUnitPrice = "" Then MsgBox " you must provide Unit Price " txtUnitPrice.SetFocus Exit Sub End If ' to fill up DateIssue If DateIssue = "" Then MsgBox " You must enter the date, format: 'dd/mmm/yyyy'" DateIssue.SetFocus Exit Sub End If ' to fill up DateCharged If DateCharged = "" Then MsgBox " You must enter the Month, format: 'dd/mmm/yyyy'" DateCharged.SetFocus Exit Sub End If Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = DateIssue.Value ActiveCell.Offset(0, 1) = TxtDescription.Value ActiveCell.Offset(0, 2) = txtQuantity.Value ActiveCell.Offset(0, 3) = txtUnitPrice.Value ActiveCell.Offset(0, 5) = ComboCategory.Value ActiveCell.Offset(0, 6) = ComboEmployee.Value ActiveCell.Offset(0, 7) = txtTroubleReport.Value ActiveCell.Offset(0, 8) = DateCharged.Value ActiveCell.Offset(0, 9) = txtRemarks.Value ' Range("A5").Select ' Ask and Do Procedure If MsgBox("One record is written, do you have more entries ?", vbYesNo, "Title") = vbYes Then Call UserForm_Initialize Else Unload Me End If End Sub Private Sub DateIssue_exit(ByVal Cancel As MSForms.ReturnBoolean) 'txtFrom being the text box on the user form where the date is entered. ' to restrict the date field If Not IsDate(DateIssue) And Len(DateIssue) 0 Then MsgBox "Input must be a date in the format: 'dd/mmm/yyyy'" Cancel = True Else DateIssue = Format(DateIssue, "dd/mmm/yyyy") End If End Sub Private Sub DateCharted_exit(ByVal Cancel As MSForms.ReturnBoolean) 'txtFrom being the text box on the user form where the date is entered. ' to restrict the date field If Not IsDate(DateCharged) And Len(DateCharged) 0 Then MsgBox "Input must be a date in the format: 'dd/mmm/yyyy'" Cancel = True Else DateIssue = Format(DateCharged, "mmm/yyyy") End If End Sub Private Sub UserForm_Initialize() Application.EnableEvents = False DateIssue.Value = "" TxtDescription.Value = "" txtQuantity.Value = "" txtUnitPrice.Value = "" ComboCategory.Value = "" ComboEmployee.Value = "" txtTroubleReport.Value = "" txtRemarks.Value = "" DateCharged.Value = "" DateIssue.SetFocus Application.EnableEvents = True End Sub Private Sub CommandButton4_Click() ' for cancel buton Unload Me End Sub Private Sub CommandButton5_Click() Call UserForm_Initialize End Sub Private Sub TxtUnitPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' to restrict blank entry If txtUnitPrice.Text = "" Then MsgBox "Sorry, please enter the Unit Price to proceed..." Cancel = True End If End Sub Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' to restrict entry for Numbers only If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then Interaction.Beep KeyAscii = 0 End If End Sub Private Sub TxtQuantity_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'to restrict blank entry If txtQuantity.Text = "" Then MsgBox "Sorry, please enter the Quantity to proceed..." Cancel = True End If End Sub Private Sub txtQuantity_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' to restrict entry for Numbers only If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then Interaction.Beep KeyAscii = 0 End If End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) ' Disable the "X" on the userform so that the user can't If CloseMode = vbFormControlMenu Then Cancel = True MsgBox Prompt:=" Sorry but I can't let you do that. " End If End Sub- Hide quoted text - - Show quoted text - Dear Sir, I Formated the worksheet cell to mmm/yyy it is showing JAN 08, but in the formula bar it is showing 08/01/08 this is the reason, I am unable to filter my records by month. if it will show 01/01/08 then it will be ok, Any suggession... Regards. Shahzad |
| All times are GMT +1. The time now is 01:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com