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_").Range("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
(E-Mail Removed)



"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_").Range("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