Hello everyone,
Here's my problem. I have a userform with textboxes titled Reg 1 to Reg 597.
My full code
I have this class module to avoid infinite loop
And this piece of code to format the text in Reg206 and Reg351 to this ###/###.
When I put Reg351 in there alone
everything works flawlessly. But when I insert Reg206
I get Error 13.
It seems the name "reg206" causes the error because if I name my textbox "textbox1" or to any name between "Reg300 to Reg450", the problem doesn't occur (Reg299 and less the problem happens). I don't get why. I prefer not to change the Name "Reg" because it's easier to import in my database.
Any tips or ideas?
thanx in advance
Here's my problem. I have a userform with textboxes titled Reg 1 to Reg 597.
My full code
VBA Code:
Option Explicit
Dim bFlgExit As Boolean
Dim LocOldLength As Integer
Dim TbxDate() As New Classe1
Dim Tbx7() As New Classe1
Function GetDataType(ByVal Text As String) As Variant
If IsNumeric(Text) Then
GetDataType = Val(Text)
Else
GetDataType = Text
End If
End Function
' When we click the 'continue' button
Private Sub CommandButton1_Click()
Dim Sht As Worksheet, cStart As Range
Dim LasteRow As Long
Dim TargetRow As Long 'variable for position control
Dim FullName As String 'full name
Dim UserMessage As String 'variable to configure user message at the end
Dim Ind As Long
Dim Dossier As String
' Désactiver les évènements et le calcul auto
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'
Dossier = Reg5
FullName = Reg4 & " " & Reg3 'concatenate first and surname for use in code below
' Define the work sheet
Set Sht = ThisWorkbook.Sheets("Data")
Set cStart = Sht.Range("Data_Start")
' The last row
LasteRow = Sht.ListObjects("Tableau1").ListRows.Count
'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(Sht.Range("H8:d" & LasteRow), Dossier) > 0 Then
MsgBox "Le numéro de dossier existe déjà", 0, "Check"
Exit Sub 'notify user and exit the routine
End If
'end validation check
TargetRow = Sht.ListObjects("Tableau1").Range(LasteRow, 1).End(xlUp).Row - 7 + 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
'''BEGIN INPUT DATA INTO DATABASE'''
cStart.Offset(TargetRow, 0).Value = TargetRow 'ref
cStart.Offset(TargetRow, 1).Value = UCase(Reg4) & " " & Reg3 'Txt_First 'full name 'concatenate
For Ind = 1 To 597
cStart.Offset(TargetRow, 2 + Ind - 1).Value = GetDataType(Controls("Reg" & Ind).Value)
Next Ind
For Ind = 1 To 23
cStart.Offset(TargetRow, 599 + Ind - 1).Value = Controls("Text_date" & Ind)
Next Ind
' Réactiver les évènements et le calcul auto
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
'''END INPUT DATA INTO DATABASE'''
Unload Data_UF 'close the userform
MsgBox FullName & UserMessage, 0, "Complété" 'display message box (configured according to mode)
End Sub
Private Sub CommandButton2_Click()
Unload Data_UF
End Sub
' Utilisation d'un module de classe pour tous les controls contenant le terme "date"
Private Sub UserForm_Initialize()
Dim Ctl As Control, Ind As Long, Ind7 As Integer
Ind = 0: Ind7 = 0
' Pour chaque control de l'UserForm
For Each Ctl In Me.Controls
' Si son nom contient "date"
If InStr(1, Ctl.Name, "date", vbTextCompare) > 0 Or _
InStr(1, "Text_sign_ini,Text_sign_fin", Ctl.Name) > 0 Then
' Définir une classe pour ce control
' Saisie d'une date de 10 caractères
Ind = Ind + 1
ReDim Preserve TbxDate(1 To Ind)
Set TbxDate(Ind).TbxDate = Ctl
End If
' 2 control avec saisi d'une date de 7 caractères
If InStr(1, "Reg206,Reg351", Ctl.Name, vbTextCompare) > 0 Then
Ind7 = Ind7 + 1
ReDim Preserve Tbx7(1 To Ind7)
Set Tbx7(Ind7).Tbx7 = Ctl
End If
Next Ctl
End SubI have this class module to avoid infinite loop
VBA Code:
Option Explicit
Private oldLength As Integer
Private FlgExit As Boolean
Public WithEvents TbxDate As MSForms.TextBox
Public WithEvents Tbx7 As MSForms.TextBox
Private Sub TbxDate_Change()
' Empêcher la boucle infinie
If FlgExit Then FlgExit = False: Exit Sub
' Tout est ok
If oldLength > TbxDate.TextLength Then
oldLength = TbxDate.TextLength
Exit Sub
End If
' nb caractères maxi autorisé dans le textbox mettre 8 si tu veux l'année à 2 chiffres
TbxDate.MaxLength = 10
'
If TbxDate.TextLength = 4 Or TbxDate.TextLength = 7 Then
FlgExit = True
TbxDate.Text = TbxDate.Text & "/"
End If
oldLength = TbxDate.TextLength
End Sub
Private Sub Tbx7_Change()
' Empêcher la boucle infinie
If FlgExit Then FlgExit = False: Exit Sub
' Tout est ok
If oldLength > Tbx7.TextLength Then
oldLength = Tbx7.TextLength
Exit Sub
End If
' nb caractères maxi autorisé dans le textbox mettre 8 si tu veux l'année à 2 chiffres
Tbx7.MaxLength = 7
'
If Tbx7.TextLength = 3 Then
FlgExit = True
Tbx7.Text = Tbx7.Text & "/"
End If
oldLength = Tbx7.TextLength
End SubAnd this piece of code to format the text in Reg206 and Reg351 to this ###/###.
VBA Code:
Private Sub UserForm_Initialize()
Dim Ctl As Control, Ind As Long, Ind7 As Integer
Ind = 0: Ind7 = 0
' Pour chaque control de l'UserForm
For Each Ctl In Me.Controls
If InStr(1, Ctl.Name, "date", vbTextCompare) > 0 Or _
InStr(1, "Text_sign_ini,Text_sign_fin", Ctl.Name) > 0 Then
' Définir une classe pour ce control
Ind = Ind + 1
ReDim Preserve TbxDate(1 To Ind)
Set TbxDate(Ind).TbxDate = Ctl
End If
If InStr(1, "Reg206,Reg351,Text", Ctl.Name, vbTextCompare) > 0 Then
Ind7 = Ind7 + 1
ReDim Preserve Tbx7(1 To Ind7)
Set Tbx7(Ind7).Tbx7 = Ctl
End If
Next Ctl
End SubWhen I put Reg351 in there alone
VBA Code:
If InStr(1, "Reg351", Ctl.Name, vbTextCompare) > 0 Then
VBA Code:
If InStr(1, "Reg206, Reg351", Ctl.Name, vbTextCompare) > 0 ThenIt seems the name "reg206" causes the error because if I name my textbox "textbox1" or to any name between "Reg300 to Reg450", the problem doesn't occur (Reg299 and less the problem happens). I don't get why. I prefer not to change the Name "Reg" because it's easier to import in my database.
Any tips or ideas?
thanx in advance
