Hello everyone I hope someone can help me with this.
I've created a userform in which I have textboxes containing dates or numbers. When I export these textboxes values to an excel database they are are set to text. It seems to be a frequent problem, but I don't understand how to solve it. I've tried adding "Clng" or "Cdate" in my code, but I only get error 13.
Here is the part of my code where the values are sent to the database (commandbutton1)
and the entire code
I hope you can give me some cues.
thanks
Jp
I've created a userform in which I have textboxes containing dates or numbers. When I export these textboxes values to an excel database they are are set to text. It seems to be a frequent problem, but I don't understand how to solve it. I've tried adding "Clng" or "Cdate" in my code, but I only get error 13.
Here is the part of my code where the values are sent to the database (commandbutton1)
VBA Code:
For i = 1 To 8
Sheets("Data").Range("Data_Start").Offset(TargetRow, 279 + i).Value = Controls("Text_date_pod_" & i & "_ini") ' podo date
Next i
For i = 1 To 8
Sheets("Data").Range("Data_Start").Offset(TargetRow, 287 + i).Value = Controls("Text_result_podo" & i & "_ini") ' podo results numbers
Next i
and the entire code
VBA Code:
Option Explicit
Private oldLength As Integer
'date de podométrie
Private Sub Text_date_pod_1_ini_Change()
If (oldLength > Text_date_pod_1_ini.TextLength) Then
oldLength = Text_date_pod_1_ini.TextLength
Exit Sub
End If
Text_date_pod_1_ini.MaxLength = 10 'nb caractères maxi autorisé dans le textbox mettre 8 si tu veux l'année à 2 chiffres
If Text_date_pod_1_ini.TextLength = 4 Or Text_date_pod_1_ini.TextLength = 7 Then
Text_date_pod_1_ini.Text = Text_date_pod_1_ini.Text + "/"
End If
oldLength = Text_date_pod_1_ini.TextLength
End Sub
Private Sub Text_date_pod_2_ini_Change()
If (oldLength > Text_date_pod_2_ini.TextLength) Then
oldLength = Text_date_pod_2_ini.TextLength
Exit Sub
End If
Text_date_pod_2_ini.MaxLength = 10 'nb caractères maxi autorisé dans le textbox mettre 8 si tu veux l'année à 2 chiffres
If Text_date_pod_2_ini.TextLength = 4 Or Text_date_pod_2_ini.TextLength = 7 Then
Text_date_pod_2_ini.Text = Text_date_pod_2_ini.Text + "/"
End If
oldLength = Text_date_pod_2_ini.TextLength
End Sub
Private Sub Text_date_pod_3_ini_Change()
If (oldLength > Text_date_pod_3_ini.TextLength) Then
oldLength = Text_date_pod_3_ini.TextLength
Exit Sub
End If
Text_date_pod_3_ini.MaxLength = 10 'nb caractères maxi autorisé dans le textbox mettre 8 si tu veux l'année à 2 chiffres
If Text_date_pod_3_ini.TextLength = 4 Or Text_date_pod_3_ini.TextLength = 7 Then
Text_date_pod_3_ini.Text = Text_date_pod_3_ini.Text + "/"
End If
oldLength = Text_date_pod_3_ini.TextLength
End Sub
Private Sub Text_date_pod_4_ini_Change()
If (oldLength > Text_date_pod_4_ini.TextLength) Then
oldLength = Text_date_pod_4_ini.TextLength
Exit Sub
End If
Text_date_pod_4_ini.MaxLength = 10 'nb caractères maxi autorisé dans le textbox mettre 8 si tu veux l'année à 2 chiffres
If Text_date_pod_4_ini.TextLength = 4 Or Text_date_pod_4_ini.TextLength = 7 Then
Text_date_pod_4_ini.Text = Text_date_pod_4_ini.Text + "/"
End If
oldLength = Text_date_pod_4_ini.TextLength
End Sub
Private Sub Text_date_pod_5_ini_Change()
If (oldLength > Text_date_pod_5_ini.TextLength) Then
oldLength = Text_date_pod_5_ini.TextLength
Exit Sub
End If
Text_date_pod_5_ini.MaxLength = 10 'nb caractères maxi autorisé dans le textbox mettre 8 si tu veux l'année à 2 chiffres
If Text_date_pod_5_ini.TextLength = 4 Or Text_date_pod_5_ini.TextLength = 7 Then
Text_date_pod_5_ini.Text = Text_date_pod_5_ini.Text + "/"
End If
oldLength = Text_date_pod_5_ini.TextLength
End Sub
Private Sub Text_date_pod_6_ini_Change()
If (oldLength > Text_date_pod_6_ini.TextLength) Then
oldLength = Text_date_pod_6_ini.TextLength
Exit Sub
End If
Text_date_pod_6_ini.MaxLength = 10 'nb caractères maxi autorisé dans le textbox mettre 8 si tu veux l'année à 2 chiffres
If Text_date_pod_6_ini.TextLength = 4 Or Text_date_pod_6_ini.TextLength = 7 Then
Text_date_pod_6_ini.Text = Text_date_pod_6_ini.Text + "/"
End If
oldLength = Text_date_pod_6_ini.TextLength
End Sub
Private Sub Text_date_pod_7_ini_Change()
If (oldLength > Text_date_pod_7_ini.TextLength) Then
oldLength = Text_date_pod_7_ini.TextLength
Exit Sub
End If
Text_date_pod_7_ini.MaxLength = 10 'nb caractères maxi autorisé dans le textbox mettre 8 si tu veux l'année à 2 chiffres
If Text_date_pod_7_ini.TextLength = 4 Or Text_date_pod_7_ini.TextLength = 7 Then
Text_date_pod_7_ini.Text = Text_date_pod_7_ini.Text + "/"
End If
oldLength = Text_date_pod_7_ini.TextLength
End Sub
Private Sub Text_date_pod_8_ini_Change()
If (oldLength > Text_date_pod_8_ini.TextLength) Then
oldLength = Text_date_pod_8_ini.TextLength
Exit Sub
End If
Text_date_pod_8_ini.MaxLength = 10 'nb caractères maxi autorisé dans le textbox mettre 8 si tu veux l'année à 2 chiffres
If Text_date_pod_8_ini.TextLength = 4 Or Text_date_pod_8_ini.TextLength = 7 Then
Text_date_pod_8_ini.Text = Text_date_pod_8_ini.Text + "/"
End If
oldLength = Text_date_pod_8_ini.TextLength
End Sub
Private Sub CommandButton1_Click()
'when we click the 'continue' button
Dim TargetRow As Integer 'variable for position control
Dim FullName As String 'full name
Dim UserMessage As String 'variable to configure user message at the end
Dim i As Long
FullName = Txt_Surname & " " & Txt_First 'concatenate first and surname for use in code below
'begin check if in 'edit' or 'add new' mode
If Sheets("Engine").Range("B4").Value = "NEW" Then 'in 'new' mode
'begin validation check 'check if name already exists
If Application.WorksheetFunction.CountIf(Sheets("Data").Range("E8:E10008"), FullName) > 0 Then
MsgBox "Name already exists", 0, "Check"
Exit Sub 'notify user and exit the routine
End If
'end validation check
TargetRow = Sheets("Engine").Range("B3").Value + 1 'make variable equal to COUNTA formula on worksheet + 1
UserMessage = " a été ajouté à la base de données" 'configure user message for add new entry
Else 'in 'edit' mode
TargetRow = Sheets("Engine").Range("B5").Value 'make variable equal to the value saved in the engine
UserMessage = " a été modifié" 'configure user message for edit entry
End If
'end check if in 'edit' or 'add new mode'
'''BEGIN INPUT DATA INTO DATABASE'''
Sheets("Data").Range("Data_Start").Offset(TargetRow, 139).Value = Combo_cardio_ini
Sheets("Data").Range("Data_Start").Offset(TargetRow, 140).Value = Combo_inf_ini
Sheets("Data").Range("Data_Start").Offset(TargetRow, 141).Value = Text_com_cardio_ini
Sheets("Data").Range("Data_Start").Offset(TargetRow, 142).Value = Text_evol_fc_ini
Sheets("Data").Range("Data_Start").Offset(TargetRow, 143).Value = Text_recup_ini
Sheets("Data").Range("Data_Start").Offset(TargetRow, 144).Value = Text_dlr_text_ini
Sheets("Data").Range("Data_Start").Offset(TargetRow, 145).Value = Text_obs_effort_ini
Sheets("Data").Range("Data_Start").Offset(TargetRow, 146).Value = Text_leapa_text_perso_ini
Sheets("Data").Range("Data_Start").Offset(TargetRow, 147).Value = Text_descri_ini
For i = 1 To 16
Sheets("Data").Range("Data_Start").Offset(TargetRow, 147 + i).Value = Controls("Text_fc_" & i & "_ini")
Next i
'Sheets("Data").Range("Data_Start").Offset(TargetRow, 164).Value = Text_fc_17_ini
'Sheets("Data").Range("Data_Start").Offset(TargetRow, 165).Value = Text_fc_18_ini
'Sheets("Data").Range("Data_Start").Offset(TargetRow, 166).Value = Text_fc_19_ini
'Sheets("Data").Range("Data_Start").Offset(TargetRow, 167).Value = Text_fc_20_ini
For i = 1 To 8
Sheets("Data").Range("Data_Start").Offset(TargetRow, 167 + i).Value = Controls("Text_TAS" & i & "_ini") ' TAS ini
Next i
For i = 1 To 8
Sheets("Data").Range("Data_Start").Offset(TargetRow, 279 + i).Value = Controls("Text_date_pod_" & i & "_ini") ' podo date
Next i
For i = 1 To 8
Sheets("Data").Range("Data_Start").Offset(TargetRow, 287 + i).Value = Controls("Text_result_podo" & i & "_ini") ' podo results numbers
Next i
'''END INPUT DATA INTO DATABASE'''
Unload Data_UF 'close the userform
MsgBox FullName & UserMessage, 0, "Completé" 'display message box (configured according to mode)
End Sub
I hope you can give me some cues.
thanks
Jp