Hi All,

I'm sorry this bloody hard to explain. I have a userform with two text fields,
txtstartdate & txtenddate. I would like to use a command button that when
clicked on opens up MS Calendar for selecting a date not clicking on text
field. The first problem is when I select the date it is not displayed in
text field on form and then is not being added to worksheet with other
information in one cell. The displayed date in text field could be change to
a label.caption if easier. I wish to use the calendar as data validation was
becoming a very big problem.

Code to date:

Private Sub Calendar1_Click()

txtStartDate.Value = Format(txtStartDate.Calendar1.Value, "dd-mm-yyyy")

'UserForm2.ActiveControl.Value = Format(Me.Calendar1.Value, "dd-mm-yyyy")
UserForm2.ActiveControl.SetFocus
Unload Me
End Sub

Private Sub txtStartDate_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, _
ByVal X As Single, ByVal Y As Single)
UserForm2.Show
End Sub

This part of the code for adding the data entered into sheet. Cells (irow, 4)
is where the date is placed. Please don't be woried about the extra spaces in
the code as tere are specific requirements for producing a CSV file to load
into Oracle Financial App.

Application.ScreenUpdating = False
Worksheets("DataEntry").Visible = True

'write userform entries to database
Cells(irow, 1) = Format(Now, "dd/mmm/yy")
Cells(irow, 3) = Me.txtInvClaim.Value
Cells(irow, 4) = Me.txtFirstName.Value & ", " & Me.txtSurName & " "
& Me.txtStartDate & " TO " _
& Me.txtEndDate & " Weekly Rate $ " & Me.
txtInvWeekly & " Hrs " & Me.txtInvQty
Cells(irow, 5) = Me.txtInvAmt.Value
Cells(irow, 6) = Worksheets("XXAR_INVOICES_102_DCA_WORKCOMP_").Rang e("A4")
..Value
Cells(irow, 7) = Me.txtInvAmt.Value
Cells(irow, 8) = Me.txtInvEntity.Value
Cells(irow, 9) = Me.txtInvCostCentre.Value
Cells(irow, 10) = Me.txtInvAccount.Value
Cells(irow, 11) = Me.txtInvFund.Value
Cells(irow, 12) = Me.txtInvProject.Value
Cells(irow, 13) = Me.txtInvADS.Value

'clear the data from input form
Me.txtInvClaim.Value = ""
Me.txtSurName.Value = ""
Me.txtFirstName.Value = ""
Me.txtStartDate.Value = ""
Me.txtEndDate.Value = ""
Me.txtInvQty.Value = ""
Me.txtInvWeekly.Value = ""
Me.txtInvAmt.Value = ""

'setting focus on Employee Name
Me.txtInvClaim.SetFocus
Application.ScreenUpdating = True
Worksheets("DataEntry").Visible = False

Any help will be greatly appreciated

ViViC

--
Message posted via http://www.officekb.com

I am not sure your construct is correct.

txtStartDate.Value = Format(txtStartDate.Calendar1.Value, "dd-mm-yyyy")

should this assign the Calendar1 value to txStartDate value ? In which case
try

txtStartDate.Value = Format(Calendar1.Value, "dd-mm-yyyy")

If you intend to use a label then

txtStartDate.Caption = Format(txtStartDate.Calendar1.Value,
"dd-mm-yyyy")

If the date value is not being entered into the textbox then nothing will be
transferred to the sheet,
if you usde a label do not forget to read the caption not the value from the
control.

--

Regards,
Nigel




"ViViC via OfficeKB.com" <u39049@uwe wrote in message
news:7ba10f04fe0be@uwe...
Hi All,

I'm sorry this bloody hard to explain. I have a userform with two text
fields,
txtstartdate & txtenddate. I would like to use a command button that when
clicked on opens up MS Calendar for selecting a date not clicking on text
field. The first problem is when I select the date it is not displayed in
text field on form and then is not being added to worksheet with other
information in one cell. The displayed date in text field could be change
to
a label.caption if easier. I wish to use the calendar as data validation
was
becoming a very big problem.

Code to date:

Private Sub Calendar1_Click()

txtStartDate.Value = Format(txtStartDate.Calendar1.Value, "dd-mm-yyyy")

'UserForm2.ActiveControl.Value = Format(Me.Calendar1.Value,
"dd-mm-yyyy")
UserForm2.ActiveControl.SetFocus
Unload Me
End Sub

Private Sub txtStartDate_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, _
ByVal X As Single, ByVal Y As Single)
UserForm2.Show
End Sub

This part of the code for adding the data entered into sheet. Cells (irow,
4)
is where the date is placed. Please don't be woried about the extra spaces
in
the code as tere are specific requirements for producing a CSV file to
load
into Oracle Financial App.

Application.ScreenUpdating = False
Worksheets("DataEntry").Visible = True

'write userform entries to database
Cells(irow, 1) = Format(Now, "dd/mmm/yy")
Cells(irow, 3) = Me.txtInvClaim.Value
Cells(irow, 4) = Me.txtFirstName.Value & ", " & Me.txtSurName & "
"
& Me.txtStartDate & " TO " _
& Me.txtEndDate & " Weekly Rate $ " & Me.
txtInvWeekly & " Hrs " & Me.txtInvQty
Cells(irow, 5) = Me.txtInvAmt.Value
Cells(irow, 6) =
Worksheets("XXAR_INVOICES_102_DCA_WORKCOMP_").Rang e("A4")
Value
Cells(irow, 7) = Me.txtInvAmt.Value
Cells(irow, 8) = Me.txtInvEntity.Value
Cells(irow, 9) = Me.txtInvCostCentre.Value
Cells(irow, 10) = Me.txtInvAccount.Value
Cells(irow, 11) = Me.txtInvFund.Value
Cells(irow, 12) = Me.txtInvProject.Value
Cells(irow, 13) = Me.txtInvADS.Value

'clear the data from input form
Me.txtInvClaim.Value = ""
Me.txtSurName.Value = ""
Me.txtFirstName.Value = ""
Me.txtStartDate.Value = ""
Me.txtEndDate.Value = ""
Me.txtInvQty.Value = ""
Me.txtInvWeekly.Value = ""
Me.txtInvAmt.Value = ""

'setting focus on Employee Name
Me.txtInvClaim.SetFocus
Application.ScreenUpdating = True
Worksheets("DataEntry").Visible = False

Any help will be greatly appreciated

ViViC

--
Message posted via http://www.officekb.com

Thanks Nigel,

Our systems have been down for 3 days due some storage problem, now up. Your
answer help sort out a problem and this lead to an answer to my problem. Now
works perfectly, only one small problem left. Will fix today

ViViC

Nigel wrote:
I am not sure your construct is correct.

txtStartDate.Value = Format(txtStartDate.Calendar1.Value, "dd-mm-yyyy")

should this assign the Calendar1 value to txStartDate value ? In which case
try

txtStartDate.Value = Format(Calendar1.Value, "dd-mm-yyyy")

If you intend to use a label then

txtStartDate.Caption = Format(txtStartDate.Calendar1.Value,
"dd-mm-yyyy")

If the date value is not being entered into the textbox then nothing will be
transferred to the sheet,
if you usde a label do not forget to read the caption not the value from the
control.

Hi All,

[quoted text clipped - 77 lines]

ViViC

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200711/1