Hi there,
I'm having a couple of issues that I need help with.
The first is an issue of date formatting. In my excel sheet the format for the date is DD/MM/YYY, since I'm from merry old England, but whenever a date is entered in this format on my userform and updated to the spreadsheet, it is flipped back to the American format MM/DD/YYYY. I have used the below to force it back to the format I need:
Private Sub cmdUpdate_Click()
If MsgBox("Are you sure you wish to change this record?", vbYesNo, "Confirm edit") = vbYes Then
With Worksheets("Tasks")
Rw = Worksheets("Tasks").Range("B:B").Find(Me.txtID.Value, LookIn:=xlValues, LookAt:=xlPart).Row
.Range("C" & Rw).Value = txtTask.Value
.Range("D" & Rw).Value = cmbSys.Value
.Range("E" & Rw).Value = CmbName.Value
.Range("F" & Rw).Value = DateValue(txtDateAdd.Value)
.Range("G" & Rw).Value = txtLead.Value
.Range("J" & Rw).Value = DateValue(txtForecast.Value)
.Range("L" & Rw).Value = CmbStatus.Value
.Range("M" & Rw).Value = DateValue(txtCloseDate.Value)
.Range("N" & Rw).Value = txtComment.Value
End With
Else
MsgBox ("Update Aborted")
End If
Me.txtID.Value = ""
Me.txtTask.Value = ""
Me.cmbSys.Value = ""
Me.CmbName.Value = ""
Me.txtDateAdd.Value = ""
Me.txtLead.Value = ""
Me.txtForecast.Value = ""
Me.CmbStatus.Value = ""
Me.txtCloseDate.Value = ""
Me.txtComment.Value = ""
Me.txtID.SetFocus
End Sub
The above format does work, but there is not always a date in the boxes asking for a date, so they are left blank. This leaves me with the error: Run time error '13' Type Mistmatch
Is there anyway to get it to ignore if there are any blank date boxes? This has only cropped up since I added the DateValue( code to the textboxes that are used for a date format.
The other issue I am having is I have been messing around with the use of a Calendar function, so that instead of typing out the date, you can simply click and load the date that way. The first issue I am having with this is that again, it adds it in the American date format rather than the required UK format. How can I force the calendar to input in the UK format?
And finally, I can get the calendar to add the date in perfectly fine when it is only needed in one box. But I need the ability to add it into up to 3, and these dates will all be different.
I found the code below to allow the calendar input to be used in multiple text boxes:
Private Sub Calendar1_Click()
frmAction.ActiveControl.Value = Calendar1.Value
End Sub
But this throws up an error as well: run time error '438' Object doesn't support this property or method. It seems to having an issue with the .Value part.
Sorry for such a long post.
Any help is appreciated
Bookmarks