mercredi 10 mai 2017

Userform checkbox send text to spreadsheet

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