Excel VBA 复选框 - 需要将值导出到电子表格

Question

我是一个自学VBA的新手，我有一些我正在使用的基本代码。除了复选框之外，我已经掌握了所有内容的基本知识。我尝试了不同的事情，但我不断遇到问题。我想要的只是让用户选中一个或两个复选框，并将结果显示在新行中。下面是显示界面(用户窗体(的代码和图像。

示例用户窗体(界面(

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

Answer 1

对于返回 false 的复选框 sub 删除 sub 或删除其中的所有内容，在值之间切换是框的固有功能。

Private Sub CheckBox1_Click()
End Sub
Private Sub CheckBox2_Click()
End Sub

要指示它们输出的内容，请使用 if 语句：

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
if Me.CheckBox1.Value = True then
.Cells(lRow, 8).Value = "X"
end if
if Me.CheckBox2.Value = True then
.Cells(lRow, 9).Value = "X"
end if

End With

如果您希望它实际说"NULL"而不是空单元格，那么在end if位置之前：

else
.cells(lRow, 8).value = "NULL"
end if 'Not a second end if same one as above