PDA

View Full Version : VBA Form inserting dates in the wrong format



JCosta
02-10-2016, 07:34 AM
Hi,
I am one Excel user but very limited in what concerns VBA Forms and coding.
Nevertheless I have copied some VBA code from one tutorial that works almost perfectly, but fails when dates are registered in the spreadsheet.
What the VBA code does is, it collects some information on a form and when I hit a button it copies it it into some cells in a spreadsheet. As I have mentioned earlier it performs as expected. The only issue is related with dates, as I use the format dd/mm/yyyy, that is the way I input dates and how I expected to read them in the spreadsheet cells. What happens is that VBA will input in the cell a date with a strange format, looks like mm/dd/yyyy and not a date but apparently text.
This causes a problem when indexing dates as use this spreadsheet to book all my bank account movements.
I am looking forward for someone to please will me sorting out this minor issue.

Here follows the code:
------------------------------------------------------------------

Private Sub Cmd_Click()
Dim Irow As Long
Dim IPart As Long
Dim ws As Worksheet
Set ws = Worksheets("Reconciliação Receitas")

Irow = ws.Cells.Find(what:="*", searchorder:=xlRows, searchdirection:=xlPrevious, LookIn:=xlValues).Row + 1

'IPart = Me.cboTipodemovimento.ListIndex

'check for a movement type
If Trim(Me.txtData.Value) = "" Then
Me.txtData.SetFocus
MsgBox "Insira uma data"
Exit Sub
End If

If Trim(Me.txtReceitasparque.Value) = "" Then
Me.txtReceitasparque.SetFocus
MsgBox "Insira a receita Parque"
Exit Sub
End If

If Trim(Me.txtCafetaria.Value) = "" Then
Me.txtCafetaria.SetFocus
MsgBox "Insira a receita para a Cafetaria"
Exit Sub
End If

If Trim(Me.txtMinimercado.Value) = "" Then
Me.txtMinimercado.SetFocus
MsgBox "Insira a receita para o Minimercado"
Exit Sub
End If

'Copy data to the database Banco Costa do Vizir CA"
With ws
.Cells(Irow, 2).Value = Me.txtData.Value
.Cells(Irow, 3).Value = Me.txtReceitaparque.Value
.Cells(Irow, 4).Value = Me.txtCafetaria.Value
.Cells(Irow, 5).Value = Me.txtMinimercado.Value
.Cells(Irow, 6).Value = Me.txtJogos.Value
.Cells(Irow, 7).Value = Me.txtX.Value
.Cells(Irow, 8).Value = Me.txtY.Value
End With

'Clear data
'Me.txtData.Value = Format(Date, "Medium Date")
Me.txtData.Value = ""
Me.txtReceitas.Value = ""
Me.txtCafetaria.Value = ""
Me.txtMinimercado.Value = ""
Me.txtJogos.Value = ""
Me.txtX.Value = ""
Me.txtY.Value = ""

End Sub



Private Sub cmdSair_click()
Unload Me
End Sub

--------------------------------------------------------
What do I need to change to correct this issue?

Best regards,
JAC

Aflatoon
02-10-2016, 07:45 AM
Use CDate:


.Cells(Irow, 2).Value = CDate(Me.txtData.Value)
for example.

SamT
02-10-2016, 10:27 AM
Private Function AllTextBoxesHaveData() As Boolean
AllTextBoxesHaveData _
= Len(Trim(Me.txtData)) _
* Len(Trim(Me.txtReceitasparque)) _
* Len(Trim(Me.txtCafetaria)) _
* Len(Trim(Me.txtMinimercado)) _
> 0
End Function

Usage

If Not AllTextBoxesHaveData Then _
: MsgBox "Fill in all TextBoxes" _
: Exit Sub