ExcelBanter - Date format problem from form to sheet

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Date format problem from form to sheet (https://www.excelbanter.com/excel-discussion-misc-queries/207348-date-format-problem-form-sheet.html)

Steen

Date format problem from form to sheet

Hi

I have got a problem with writing some date back from a form to excell
sheet, which I hope someone can help med with.

The problem is that the date entered in the form - shifts date and month in
the resulting date that is written into the cell in excell and I can't figure
out why? See comment in the below code ('Result)

I have a form which have a date that is initialized&activated by a
dubbelclick on the row:

Sheet1:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Select Case Target.Column
Case 1
'initialize Tasklist form
frmTaskList.txtDeadline.Value = Format(Target.Offset(0, 13),
"dd-mm-yyyy")
'Call Tasklist form
frmTaskList.Show
End Select
End If
End Sub

Private Sub cmdAccept_Click()
If Me.txtDeadline.Value = "" Then
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtDeadline.Value) Then
MsgBox Me.txtDeadline.Value
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If

RowNo = ActiveCell.Row - 1
With Worksheets("Tasklist").Range("A1")
MsgBox (Format(Me.txtDeadline.Value, "dd-mm-yyyy")) 'Result:
06-04-2009
.Offset(RowNo, 13).Value = Me.txtDeadline.Value
MsgBox (Format(.Offset(RowNo, 13).Value, "dd-mm-yyyy")) 'Result:
04-06-2009
End With
Unload Me
End Sub

Any help would be much appriciated.
/Steen

joel

Date format problem from form to sheet

The textboxes (or other boxes) on you form stores the dates as TEXT. th
efollowing line of code really doesn't make a lot of sense.

MsgBox (Format(Me.txtDeadline.Value, "dd-mm-yyyy"))

Me.txtDeadline.Value is a string variable. The Format function shouldn't
work on a string it requires a serial Date. The code reeally should be this

MsgBox (Format(DateValue(Me.txtDeadline.Value), "dd-mm-yyyy"))

"Steen" wrote:

Hi

I have got a problem with writing some date back from a form to excell
sheet, which I hope someone can help med with.

The problem is that the date entered in the form - shifts date and month in
the resulting date that is written into the cell in excell and I can't figure
out why? See comment in the below code ('Result)

I have a form which have a date that is initialized&activated by a
dubbelclick on the row:

Sheet1:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Select Case Target.Column
Case 1
'initialize Tasklist form
frmTaskList.txtDeadline.Value = Format(Target.Offset(0, 13),
"dd-mm-yyyy")
'Call Tasklist form
frmTaskList.Show
End Select
End If
End Sub

Private Sub cmdAccept_Click()
If Me.txtDeadline.Value = "" Then
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtDeadline.Value) Then
MsgBox Me.txtDeadline.Value
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If

RowNo = ActiveCell.Row - 1
With Worksheets("Tasklist").Range("A1")
MsgBox (Format(Me.txtDeadline.Value, "dd-mm-yyyy")) 'Result:
06-04-2009
.Offset(RowNo, 13).Value = Me.txtDeadline.Value
MsgBox (Format(.Offset(RowNo, 13).Value, "dd-mm-yyyy")) 'Result:
04-06-2009
End With
Unload Me
End Sub

Any help would be much appriciated.
/Steen

Steen

Date format problem from form to sheet

Hi Joel

Thanks for your help - it work great.

Could you help once again - I would like to use the Calendar tool to edit
the date in the form. I have added the following code in the Form:

Private Sub txtDeadline_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
'Call OpenCalendar
Application.Run "Personal.xls!OpenCalendar"
End Sub

This starts up the Calander tool - but how do I get it to ad the date to the
form? PS I also use the calendar tool direct in the excel sheet.

/Steen

"Joel" wrote:

The textboxes (or other boxes) on you form stores the dates as TEXT. th
efollowing line of code really doesn't make a lot of sense.

MsgBox (Format(Me.txtDeadline.Value, "dd-mm-yyyy"))

Me.txtDeadline.Value is a string variable. The Format function shouldn't
work on a string it requires a serial Date. The code reeally should be this

MsgBox (Format(DateValue(Me.txtDeadline.Value), "dd-mm-yyyy"))

"Steen" wrote:

Hi

I have got a problem with writing some date back from a form to excell
sheet, which I hope someone can help med with.

The problem is that the date entered in the form - shifts date and month in
the resulting date that is written into the cell in excell and I can't figure
out why? See comment in the below code ('Result)

I have a form which have a date that is initialized&activated by a
dubbelclick on the row:

Sheet1:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Select Case Target.Column
Case 1
'initialize Tasklist form
frmTaskList.txtDeadline.Value = Format(Target.Offset(0, 13),
"dd-mm-yyyy")
'Call Tasklist form
frmTaskList.Show
End Select
End If
End Sub

Private Sub cmdAccept_Click()
If Me.txtDeadline.Value = "" Then
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtDeadline.Value) Then
MsgBox Me.txtDeadline.Value
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If

RowNo = ActiveCell.Row - 1
With Worksheets("Tasklist").Range("A1")
MsgBox (Format(Me.txtDeadline.Value, "dd-mm-yyyy")) 'Result:
06-04-2009
.Offset(RowNo, 13).Value = Me.txtDeadline.Value
MsgBox (Format(.Offset(RowNo, 13).Value, "dd-mm-yyyy")) 'Result:
04-06-2009
End With
Unload Me
End Sub

Any help would be much appriciated.
/Steen

joel

Date format problem from form to sheet

You are running a calendart control in your personal.xls file. This code
will not work if you send the excel file to another user. I would manually
add the calendar object to the worksheet by using the worksheet menu

Insert - Object - Calendar control Object.

You can referr to the calendar as sheets("Sheet1").calendar1

If you continue to use the personal.xls calendar then do the following

MyDate = workbooks("personal.xls").Sheets("Sheet1").Calenda r1.value

I assume the calendar is on sheet 1 of the personal.xls file

Private Sub txtDeadline_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
'Call OpenCalendar
sheets("Sheet1").calendar1.visible = True
MyDate = Sheets("Sheet1").calendar1.value
End Sub

"Steen" wrote:

Hi Joel

Thanks for your help - it work great.

Could you help once again - I would like to use the Calendar tool to edit
the date in the form. I have added the following code in the Form:

Private Sub txtDeadline_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
'Call OpenCalendar
Application.Run "Personal.xls!OpenCalendar"
End Sub

This starts up the Calander tool - but how do I get it to ad the date to the
form? PS I also use the calendar tool direct in the excel sheet.

/Steen

"Joel" wrote:

The textboxes (or other boxes) on you form stores the dates as TEXT. th
efollowing line of code really doesn't make a lot of sense.

MsgBox (Format(Me.txtDeadline.Value, "dd-mm-yyyy"))

Me.txtDeadline.Value is a string variable. The Format function shouldn't
work on a string it requires a serial Date. The code reeally should be this

MsgBox (Format(DateValue(Me.txtDeadline.Value), "dd-mm-yyyy"))

"Steen" wrote:

Hi

I have got a problem with writing some date back from a form to excell
sheet, which I hope someone can help med with.

The problem is that the date entered in the form - shifts date and month in
the resulting date that is written into the cell in excell and I can't figure
out why? See comment in the below code ('Result)

I have a form which have a date that is initialized&activated by a
dubbelclick on the row:

Sheet1:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Select Case Target.Column
Case 1
'initialize Tasklist form
frmTaskList.txtDeadline.Value = Format(Target.Offset(0, 13),
"dd-mm-yyyy")
'Call Tasklist form
frmTaskList.Show
End Select
End If
End Sub

Private Sub cmdAccept_Click()
If Me.txtDeadline.Value = "" Then
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtDeadline.Value) Then
MsgBox Me.txtDeadline.Value
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If

RowNo = ActiveCell.Row - 1
With Worksheets("Tasklist").Range("A1")
MsgBox (Format(Me.txtDeadline.Value, "dd-mm-yyyy")) 'Result:
06-04-2009
.Offset(RowNo, 13).Value = Me.txtDeadline.Value
MsgBox (Format(.Offset(RowNo, 13).Value, "dd-mm-yyyy")) 'Result:
04-06-2009
End With
Unload Me
End Sub

Any help would be much appriciated.
/Steen

Steen

Date format problem from form to sheet

Hi again

Sorry for interrupting again - but I not that expirenced in VBA.

I would like to use the calendar in personal - but still can't figure out
how to get it to work. Tried the below - but it dosnt work. Most of the
calendar code is place under frmCalendar - that might be the problem...

Private Sub txtDeadline_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
'Call OpenCalendar
Application.Run "Personal.xls!OpenCalendar"
Me.txtDeadline.Value =
Workbooks("personal.xls").Forms.("frmCalendar").Ca lendar1.Value
End Sub

/Steen

"Joel" wrote:

You are running a calendart control in your personal.xls file. This code
will not work if you send the excel file to another user. I would manually
add the calendar object to the worksheet by using the worksheet menu

Insert - Object - Calendar control Object.

You can referr to the calendar as sheets("Sheet1").calendar1

If you continue to use the personal.xls calendar then do the following

MyDate = workbooks("personal.xls").Sheets("Sheet1").Calenda r1.value

I assume the calendar is on sheet 1 of the personal.xls file

Private Sub txtDeadline_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
'Call OpenCalendar
sheets("Sheet1").calendar1.visible = True
MyDate = Sheets("Sheet1").calendar1.value
End Sub

"Steen" wrote:

Hi Joel

Thanks for your help - it work great.

Could you help once again - I would like to use the Calendar tool to edit
the date in the form. I have added the following code in the Form:

Private Sub txtDeadline_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
'Call OpenCalendar
Application.Run "Personal.xls!OpenCalendar"
End Sub

This starts up the Calander tool - but how do I get it to ad the date to the
form? PS I also use the calendar tool direct in the excel sheet.

/Steen

"Joel" wrote:

The textboxes (or other boxes) on you form stores the dates as TEXT. th
efollowing line of code really doesn't make a lot of sense.

MsgBox (Format(Me.txtDeadline.Value, "dd-mm-yyyy"))

Me.txtDeadline.Value is a string variable. The Format function shouldn't
work on a string it requires a serial Date. The code reeally should be this

MsgBox (Format(DateValue(Me.txtDeadline.Value), "dd-mm-yyyy"))

"Steen" wrote:

Hi

I have got a problem with writing some date back from a form to excell
sheet, which I hope someone can help med with.

The problem is that the date entered in the form - shifts date and month in
the resulting date that is written into the cell in excell and I can't figure
out why? See comment in the below code ('Result)

I have a form which have a date that is initialized&activated by a
dubbelclick on the row:

Sheet1:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Select Case Target.Column
Case 1
'initialize Tasklist form
frmTaskList.txtDeadline.Value = Format(Target.Offset(0, 13),
"dd-mm-yyyy")
'Call Tasklist form
frmTaskList.Show
End Select
End If
End Sub

Private Sub cmdAccept_Click()
If Me.txtDeadline.Value = "" Then
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtDeadline.Value) Then
MsgBox Me.txtDeadline.Value
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If

RowNo = ActiveCell.Row - 1
With Worksheets("Tasklist").Range("A1")
MsgBox (Format(Me.txtDeadline.Value, "dd-mm-yyyy")) 'Result:
06-04-2009
.Offset(RowNo, 13).Value = Me.txtDeadline.Value
MsgBox (Format(.Offset(RowNo, 13).Value, "dd-mm-yyyy")) 'Result:
04-06-2009
End With
Unload Me
End Sub

Any help would be much appriciated.
/Steen

joel

Date format problem from form to sheet

Not sure if this will fix the problem

Workbooks("personal.xls").Sheets("frmCalendar").Ca lendar1.Value

There was an extra period and a form is still a sheet

"Steen" wrote:

Hi again

Sorry for interrupting again - but I not that expirenced in VBA.

I would like to use the calendar in personal - but still can't figure out
how to get it to work. Tried the below - but it dosnt work. Most of the
calendar code is place under frmCalendar - that might be the problem...

Private Sub txtDeadline_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
'Call OpenCalendar
Application.Run "Personal.xls!OpenCalendar"
Me.txtDeadline.Value =
Workbooks("personal.xls").Forms.("frmCalendar").Ca lendar1.Value
End Sub

/Steen

"Joel" wrote:

You are running a calendart control in your personal.xls file. This code
will not work if you send the excel file to another user. I would manually
add the calendar object to the worksheet by using the worksheet menu

Insert - Object - Calendar control Object.

You can referr to the calendar as sheets("Sheet1").calendar1

If you continue to use the personal.xls calendar then do the following

MyDate = workbooks("personal.xls").Sheets("Sheet1").Calenda r1.value

I assume the calendar is on sheet 1 of the personal.xls file

Private Sub txtDeadline_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
'Call OpenCalendar
sheets("Sheet1").calendar1.visible = True
MyDate = Sheets("Sheet1").calendar1.value
End Sub

"Steen" wrote:

Hi Joel

Thanks for your help - it work great.

Could you help once again - I would like to use the Calendar tool to edit
the date in the form. I have added the following code in the Form:

Private Sub txtDeadline_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
'Call OpenCalendar
Application.Run "Personal.xls!OpenCalendar"
End Sub

This starts up the Calander tool - but how do I get it to ad the date to the
form? PS I also use the calendar tool direct in the excel sheet.

/Steen

"Joel" wrote:

The textboxes (or other boxes) on you form stores the dates as TEXT. th
efollowing line of code really doesn't make a lot of sense.

MsgBox (Format(Me.txtDeadline.Value, "dd-mm-yyyy"))

Me.txtDeadline.Value is a string variable. The Format function shouldn't
work on a string it requires a serial Date. The code reeally should be this

MsgBox (Format(DateValue(Me.txtDeadline.Value), "dd-mm-yyyy"))

"Steen" wrote:

Hi

I have got a problem with writing some date back from a form to excell
sheet, which I hope someone can help med with.

The problem is that the date entered in the form - shifts date and month in
the resulting date that is written into the cell in excell and I can't figure
out why? See comment in the below code ('Result)

I have a form which have a date that is initialized&activated by a
dubbelclick on the row:

Sheet1:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Select Case Target.Column
Case 1
'initialize Tasklist form
frmTaskList.txtDeadline.Value = Format(Target.Offset(0, 13),
"dd-mm-yyyy")
'Call Tasklist form
frmTaskList.Show
End Select
End If
End Sub

Private Sub cmdAccept_Click()
If Me.txtDeadline.Value = "" Then
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtDeadline.Value) Then
MsgBox Me.txtDeadline.Value
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If

RowNo = ActiveCell.Row - 1
With Worksheets("Tasklist").Range("A1")
MsgBox (Format(Me.txtDeadline.Value, "dd-mm-yyyy")) 'Result:
06-04-2009
.Offset(RowNo, 13).Value = Me.txtDeadline.Value
MsgBox (Format(.Offset(RowNo, 13).Value, "dd-mm-yyyy")) 'Result:
04-06-2009
End With
Unload Me
End Sub

Any help would be much appriciated.
/Steen

Steen

Date format problem from form to sheet

Hi again

Thanks for your extra help, but it didn't work out.

/Steen

"Joel" wrote:

Not sure if this will fix the problem

Workbooks("personal.xls").Sheets("frmCalendar").Ca lendar1.Value

There was an extra period and a form is still a sheet

"Steen" wrote:

Hi again

Sorry for interrupting again - but I not that expirenced in VBA.

I would like to use the calendar in personal - but still can't figure out
how to get it to work. Tried the below - but it dosnt work. Most of the
calendar code is place under frmCalendar - that might be the problem...

Private Sub txtDeadline_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
'Call OpenCalendar
Application.Run "Personal.xls!OpenCalendar"
Me.txtDeadline.Value =
Workbooks("personal.xls").Forms.("frmCalendar").Ca lendar1.Value
End Sub

/Steen

"Joel" wrote:

You are running a calendart control in your personal.xls file. This code
will not work if you send the excel file to another user. I would manually
add the calendar object to the worksheet by using the worksheet menu

Insert - Object - Calendar control Object.

You can referr to the calendar as sheets("Sheet1").calendar1

If you continue to use the personal.xls calendar then do the following

MyDate = workbooks("personal.xls").Sheets("Sheet1").Calenda r1.value

I assume the calendar is on sheet 1 of the personal.xls file

Private Sub txtDeadline_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
'Call OpenCalendar
sheets("Sheet1").calendar1.visible = True
MyDate = Sheets("Sheet1").calendar1.value
End Sub

"Steen" wrote:

Hi Joel

Thanks for your help - it work great.

Could you help once again - I would like to use the Calendar tool to edit
the date in the form. I have added the following code in the Form:

Private Sub txtDeadline_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
'Call OpenCalendar
Application.Run "Personal.xls!OpenCalendar"
End Sub

This starts up the Calander tool - but how do I get it to ad the date to the
form? PS I also use the calendar tool direct in the excel sheet.

/Steen

"Joel" wrote:

The textboxes (or other boxes) on you form stores the dates as TEXT. th
efollowing line of code really doesn't make a lot of sense.

MsgBox (Format(Me.txtDeadline.Value, "dd-mm-yyyy"))

Me.txtDeadline.Value is a string variable. The Format function shouldn't
work on a string it requires a serial Date. The code reeally should be this

MsgBox (Format(DateValue(Me.txtDeadline.Value), "dd-mm-yyyy"))

"Steen" wrote:

Hi

I have got a problem with writing some date back from a form to excell
sheet, which I hope someone can help med with.

The problem is that the date entered in the form - shifts date and month in
the resulting date that is written into the cell in excell and I can't figure
out why? See comment in the below code ('Result)

I have a form which have a date that is initialized&activated by a
dubbelclick on the row:

Sheet1:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Select Case Target.Column
Case 1
'initialize Tasklist form
frmTaskList.txtDeadline.Value = Format(Target.Offset(0, 13),
"dd-mm-yyyy")
'Call Tasklist form
frmTaskList.Show
End Select
End If
End Sub

Private Sub cmdAccept_Click()
If Me.txtDeadline.Value = "" Then
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtDeadline.Value) Then
MsgBox Me.txtDeadline.Value
MsgBox "Please enter Deadline.", vbExclamation, "Task Values"
Me.txtDeadline.SetFocus
Exit Sub
End If

RowNo = ActiveCell.Row - 1
With Worksheets("Tasklist").Range("A1")
MsgBox (Format(Me.txtDeadline.Value, "dd-mm-yyyy")) 'Result:
06-04-2009
.Offset(RowNo, 13).Value = Me.txtDeadline.Value
MsgBox (Format(.Offset(RowNo, 13).Value, "dd-mm-yyyy")) 'Result:
04-06-2009
End With
Unload Me
End Sub

Any help would be much appriciated.
/Steen

All times are GMT +1. The time now is 02:51 PM.