lundi 22 juin 2020

Excel VBA Checkboxes - Need to Export Values to Spreadsheet

I'm a newbie teaching myself VBA and I have some basic code that I'm using. I've got the basics of everything except the checkboxes. I've tried different things but I keep running into issues. All I want is for the user to check one or both checkboxes and for the results to appear in a new line. Below is the code and the image that shows the interface (userform).

example Userform (Interface)

Private Sub CheckBox1_Click()
    'To select check box.
   CheckBox1.Value = True
    
    'To select check box.
    CheckBox1.Value = False
End Sub

Private Sub CheckBox2_Click()
    'To select check box.
    CheckBox2.Value = True
    
    'To select check box.
    CheckBox2.Value = False
End Sub

Private Sub cboClass_DropButtonClick()
    'Populate control.
    Me.cboClass.AddItem "Amphibian"
    Me.cboClass.AddItem "Bird"
    Me.cboClass.AddItem "Fish"
    Me.cboClass.AddItem "Mammal"
    Me.cboClass.AddItem "Reptile"
    
End Sub


Private Sub cboConservationStatus_DropButtonClick()
    'Populate control.
    Me.cboConservationStatus.AddItem "Endangered"
    Me.cboConservationStatus.AddItem "Extirpated"
    Me.cboConservationStatus.AddItem "Historic"
    Me.cboConservationStatus.AddItem "Special concern"
    Me.cboConservationStatus.AddItem "Stable"
    Me.cboConservationStatus.AddItem "Threatened"
    Me.cboConservationStatus.AddItem "WAP"

End Sub

Private Sub cboSex_DropButtonClick()
    'Populate control.
    Me.cboSex.AddItem "Female"
    Me.cboSex.AddItem "Male"
End Sub

Private Sub cmdAdd_Click()
    'Copy input values to sheet.
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Animals")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 1).Value = Me.cboClass.Value
        .Cells(lRow, 2).Value = Me.txtName.Value
        .Cells(lRow, 3).Value = Me.txtTagNumber.Value
        .Cells(lRow, 4).Value = Me.txtSpecies.Value
        .Cells(lRow, 5).Value = Me.cboSex.Value
        .Cells(lRow, 6).Value = Me.cboConservationStatus.Value
        .Cells(lRow, 7).Value = Me.txtComment.Value
        .Cells(lRow, 8).Value = Me.CheckBox1.Value
        .Cells(lRow, 9).Value = Me.CheckBox2.Value
        
    End With
    
    'Clear input controls.
    Me.cboClass.Value = ""
    Me.txtName.Value = ""
    Me.txtTagNumber.Value = ""
    Me.txtSpecies.Value = ""
    Me.cboSex.Value = ""
    Me.cboConservationStatus.Value = ""
    Me.txtComment.Value = ""
    Me.CheckBox1.Value = ""
    Me.CheckBox2.Value = ""
        
End Sub

Private Sub cmdClose_Click()
    'Close UserForm.
    Unload Me
    
End Sub

Private Sub UserForm_Click()

End Sub



Aucun commentaire:

Enregistrer un commentaire