I am trying to get a checkbox1 to populate the phrases 'Absent' within the next free row when the checkbox is ticked, and within Column O, when a command button submits the userform to the spreadsheet.
The code below is for my 'submit' command button, which works well with no issues. How would I incorporate the checkbox please to add 'absent' into the exact same row as the other information that is currently being added?
Also, I would like the checkbox to be able to revert back to its 'unchecked' state upon the form being submitted.
Any help is much appreciated.
The command button submit code is:
Private Sub CommandButton1_Click()
Set ThisRow = Range("A" & Rows.Count).End(xlUp)
If ThisRow.Row = 1 Then
Me.TextBox1 = 1
Else
Me.TextBox1 = ThisRow.Row + 1
End If
Me.TextBox2.Value = Now
Set ThisRow = ThisRow.Offset(1)
SaveData
End Sub
Saving the data to spreadsheet:
Private Sub SaveData()
Dim C As MSForms.Control
Dim varValue As Variant 'Must be variant to accept different types of data
For Each C In Me.Controls
If C.Tag <> "" Then
varValue = C.Value
Select Case C.Tag
Case "A", "D", "H", "I", "J", "K", "L", "M", "N", "O"
Worksheets("Data").Range(C.Tag & ThisRow.Row) = varValue
Case "B", "C", "E"
If IsDate(varValue) Then
Worksheets("Data").Range(C.Tag & ThisRow.Row) = CDate(varValue)
End If
End Select
The initialise code is:
Private Sub UserForm_Initialize()
Me.listDate.ListIndex = -1
listOwner.ListIndex = 0
Me.listOwner.Tag = "D"
Me.listDate.Tag = "E"
Me.listEmployee.Tag = "F
etc etc etc and so on
Aucun commentaire:
Enregistrer un commentaire