How to set textbox value as numerical or date value in database

J

jptaz

New Member
Joined
May 1, 2020
Messages
46
Office Version
  1. 2010
Platform
  1. Windows
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)

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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Hi,
you could create yourself a function to coerce your textbox string to the required data types

Untested but something like following may help

VBA Code: 
Function GetDataType(ByVal Text As String) As Variant
    If Text Like "##/##/####" And IsDate(Text) Then
            GetDataType = DateValue(Text)
     ElseIf IsNumeric(Text) Then
            GetDataType = Val(Text)
      Else
            GetDataType = Text
      End If
End Function

and then call it as follows

Rich (BB code): 
For i = 1 To 8
      Sheets("Data").Range("Data_Start").Offset(TargetRow, 279 + i).Value = _
        GetDataType(Controls("Text_date_pod_" & i & "_ini").Value) 
    Next i

For most users, the built-in IsNumeric function will suffice for their needs but it does have limitations - if not getting results expected to understand issue, have a read here:https://excelfox.com/forum/showthre...sing-VBA-s-IsNumeric-Function-Read-this-first

You will also note that I have included a date format test in the function ("##/##/####") as IsDate can also throw some incorrect results.

just and idea but hope of some help

Dave
 
Upvote 0
dmt32 said:
Hi,
you could create yourself a function to coerce your textbox string to the required data types

Untested but something like following may help

VBA Code: 
Function GetDataType(ByVal Text As String) As Variant
    If Text Like "##/##/####" And IsDate(Text) Then
            GetDataType = DateValue(Text)
     ElseIf IsNumeric(Text) Then
            GetDataType = Val(Text)
      Else
            GetDataType = Text
      End If
End Function

and then call it as follows

Rich (BB code): 
For i = 1 To 8
      Sheets("Data").Range("Data_Start").Offset(TargetRow, 279 + i).Value = _
        GetDataType(Controls("Text_date_pod_" & i & "_ini").Value) 
    Next i

For most users, the built-in IsNumeric function will suffice for their needs but it does have limitations - if not getting results expected to understand issue, have a read here:Thinking About Using VBA's IsNumeric Function? Read this first.

You will also note that I have included a date format test in the function ("##/##/####") as IsDate can also throw some incorrect results.

just and idea but hope of some help

Dave
Click to expand...
Hi Dave,
Thanks for your time. Your solution works perfectly.

Have a nice day!

JP
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel crashing when entering date in userform
Replies
0
Views
940
jptaz
J
J
  • Question Question
Error 13 type mismatch
Replies
7
Views
615
Fluff
Fluff

Forum statistics

Threads
1,224,829
Messages
6,181,222
Members
453,024
Latest member
Wingit77

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top