Date Field Problem in Userform

[shahzad4u_ksa]

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

 

[Bob Phillips]

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)

 

[stefan onken]

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

 

[shahzad4u_ksa]

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)


















































- 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

 

[Bob Phillips]

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)

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)
















































- 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