Add TextBoxs to UserForm runtime and check them with each other

Rogerant79

New Member
Joined
Oct 14, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Hello,
first of all I congratulate you on the forum. It is one of the best in the world right now :)

Let's get to the point:
I would like to create a Userform as shown:
image1.png


Essentially we have 3 objects:
  • CheckBox
  • TextBox (DATE)
  • TextBox (TIME)

I create these objects through this code inside the UserForm:
VBA Code:
Option Explicit

Private Sub UserForm_Initialize()

Dim ID As msforms.CheckBox
Dim DAY As msforms.TextBox
Dim TIME As msforms.TextBox

Dim i As Long
For i = 1 To 10
        
' CheckBox creation
    Set ID = UserForm1.Controls.Add("Forms.CheckBox.1")
    With ID
        .Name = "ID_index" & i
        .Left = 20
        .top = 25 + 22 * i
        .Height = 18
        .Width = 20
    End With

' TextBox DAY creation
    Set DAY = UserForm1.Controls.Add("Forms.TextBox.1")
    With DAY
        .Name = "DAY_index" & i
        .Left = 50
        .top = 25 + 22 * i
        .Height = 18
        .Width = 100
    End With
    
    
' TextBox TIME creation
    Set TIME = UserForm1.Controls.Add("Forms.TextBox.1")
    With TIME
        .Name = "TIME_index" & i
        .Left = 160
        .top = 25 + 22 * i
        .Height = 18
        .Width = 100
    End With

Next i


End Sub



Here's my problem:
I want that if, for example, I insert the date in a textbox and the time in the adjacent texbox, I would like that if these 2 fields are complete, the checkbox of the same row will automatically become selected.
image2.png


Can someone help me?
Thank you
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
L

Legacy 456155

Guest
Here is a bare example that will get you started. The logic should probably be in your userform by way of an event or callback, but for the sake of simplicity, I encapsulated it in the class.

Add Class1 to your project and enter the following:
VBA Code:
Option Explicit

Private WithEvents txtDate As MSForms.TextBox
Private WithEvents txtTime As MSForms.TextBox
Private chkDateTime As MSForms.CheckBox

Friend Sub Init(DateTextBox As MSForms.TextBox, TimeTextBox As MSForms.TextBox, DateTimeCheckBox As MSForms.CheckBox)
    Set txtDate = DateTextBox
    Set txtTime = TimeTextBox
    Set chkDateTime = DateTimeCheckBox
End Sub

Private Sub txtDate_Change()
    chkDateTime = IsDate(txtDate) And IsDate(txtTime)
End Sub

Private Sub txtTime_Change()
    chkDateTime = IsDate(txtDate) And IsDate(txtTime)
End Sub

Adapt your current userform code:
VBA Code:
Option Explicit

Private c As New Collection

Private Sub UserForm_Initialize()

Dim ID As MSForms.CheckBox
Dim DAY As MSForms.TextBox
Dim TIME As MSForms.TextBox

Dim i As Long, c1 As Class1
For i = 1 To 10
        
' CheckBox creation
    Set ID = UserForm1.Controls.Add("Forms.CheckBox.1")
    With ID
        .Name = "ID_index" & i
        .Left = 20
        .Top = 25 + 22 * i
        .Height = 18
        .Width = 20
    End With

' TextBox DAY creation
    Set DAY = UserForm1.Controls.Add("Forms.TextBox.1")
    With DAY
        .Name = "DAY_index" & i
        .Left = 50
        .Top = 25 + 22 * i
        .Height = 18
        .Width = 100
    End With
    
    
' TextBox TIME creation
    Set TIME = UserForm1.Controls.Add("Forms.TextBox.1")
    With TIME
        .Name = "TIME_index" & i
        .Left = 160
        .Top = 25 + 22 * i
        .Height = 18
        .Width = 100
    End With
    
    Set c1 = New Class1
    c1.Init DAY, TIME, ID
    c.Add c1
Next i


End Sub
 

Rogerant79

New Member
Joined
Oct 14, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Is it possible, after inserting the values in the respective TextBoxes, to write the value of the TextBox in a cell of a sheet?
Let's take the TextBox for the date, I would like that if I write a date inside the TextBox, this data will automatically be written inside a cell of a sheet.
It's possible to do it?
 
L

Legacy 456155

Guest
Sure. For two way data binding, see the ControlSource property in help. For one way, you will perform the assignment yourself. I don't know what your end is, so I'll just edit the existing code to write to a range in the activesheet whenever a valid date and time are entered.

VBA Code:
    With DAY
        '...
        .Tag = Cells(i, 1).Address
    End With
      
    With TIME
        '...
        .Tag = Cells(i, 2).Address
    End With

SQL:
Private Sub txtDate_Change()
    CheckDateTime
End Sub

Private Sub txtTime_Change()
    CheckDateTime
End Sub

Private Sub CheckDateTime()
    If IsDate(txtDate) And IsDate(txtTime) Then
        chkDateTime = True
        Range(txtDate.Tag) = txtDate
        Range(txtTime.Tag) = txtTime
    Else
        chkDateTime = False
        Range(txtDate.Tag) = Empty
        Range(txtTime.Tag) = Empty
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,112,799
Messages
5,542,579
Members
410,561
Latest member
Sasha Lawrence
Top